ADVANCED EXCEL VBA LESSON 03
Earl Lamont Montgomery
December 14, 2016
3 Adv Excel VBA Lesson 03 (FTP)
3 My goal in this lesson is to show you the fun side of programming by showing interesting relatable examples at the same time building a project.
3.101 Homage
3.101.1 I wanted to use the R package “Shiny” for this lesson but because of the limitations and restrictions connected to Shiny, I will continue to use the RMarkdown package and the same languages I used to create lesson 02:
- Microsoft Excel
- Microsoft Word
- R, RStudio
- Packages
- devtools
- knitr
- rmarkdown
- YAML
- MiKTeK
- LaTeK
- CSS
- Paint.Net
- Sublime Text 2
- Regular Expressions
- XML
- HTML
- Google Chrome
- XPath
- DOM
- Bootstrap
- PyCharm Community Edition 4.5.4
- Python
- re (regular expressions)
- sys, os
3.102 First things first
Okay I don’t want to be this guy 
but I have to say it: open your file that you created in Lesson 01 –
3.103 Let’s go to the Visual Basic Editor (VBE)
3.103.1 Here are two ways that you can get to the VBE
One way: Select the Developer ribbon and Click the Visual Basic button located all the way to the left on the ribbon…
OR
the other way: On the keyboard press Alt 11
If you have a grey background in your workspace when you open your Visual Basic Editor (VBE) follow these steps:
3.103.1.1 Your VB Editor (VBE) might open with a grey background as in the description below. If this happens, it just means that you don’t have anything in your workspace at the moment.
3.103.1.2 If you have a grey background in your workspace area then you can click the plus sign located to left of Modules as indicated within the red circle below.
3.103.1.3 After you click on the plus sign, the list of modules that you created will display under module as depicted in the picture below.
3.103.1.4 Select MyMOduLE module.
From the VBE there are four (4) different ways to view the MYMOduLE code. For now we will press the F7 key on your keyboard.
3.104 A word about successful programming
3.104.1 I don’t consider myself a master programmer. After 20+ years of VBA programming I still use Google like you wouldn’t believe. I still record macros, analayze and change them on most every project.
3.104.2 So what gives me the advantage over the gurus? Let’s just say if I were to recommend reading material for this class it would be
Napoleon Hill’s “Think and Grow Rich”. 
It expresses the concepts of determination, will power and belief. When I sit down in front of a computer to program, the advantage that I have is that I totally believe that I will succeed.
3.104.3 There was a project that I gave up on. I searched all over online for solutions and all the gurus said it could not be done. I tried several different things and nothing seemed to work. I contacted the person and let them know that it wasn’t possible to do in Excel 2010 and advised them to request Excel 2013 where it could be done. Five minutes after that I contacted them again and told them to forget the last message, I will figure it out – and I did.
3.105 Style
3.105.1 As you get more familar with programming and examine other VBA programmer’s work, you will notice different styles and techniques. I see the VBA programmer as a NYC graffiti artist. You develop your style and pick up bits and pieces from others to incorporate into the making of your own style. The beauty of VBA is its flexibility to be able to do the same process in several different ways. So that in mind as you venture forth in future projects.
3.106 Let’s begin
3.106.1 Save the work that you have done and close your Excel workbook.
3.106.2 By now if I say: open up a new Excel Macro enabled workbook and save it as:
your initials, an underscore and lesson_03, at this point I believe you don’t need step by step instructions to do that part. (for me the file would look like this: ELM_lesson03.xlsm)
3.107 Picking our project
I have decided to go over, step by step, a piece of one of the projects that I created and a piece of one that I am currently working on. The first one that I will go over is a section of a project called
No Show/Action Required (NSSP)
A brief description of this Add/Remove Auxillary Email Dialog Box (UserForm).
The Gold colored EXIT button, closes the UserForm. Below that is a comboBox that lists all the Clinics in the workbook. The listBox below and to the left displays a list of emails for the selected clinic. The commandButton below that listbox removes the selected email from the list and adds it to the listBox on the right as emails that have been removed. Under the big, bold OR is a comboBox where the user can type in an email to the auxillary list. After you are satisfied with all of your updates, the user can then click the bottom commandButton to update all the sheets.
3.108 Preparation stage
Whenever you’re getting ready to start a project, it’s a good idea to write out what it is that you want to do and how you are going to accomplish it. If you have a form, design the form on paper first. This is good practice.
3.108.1 So let’s set up your newly opened (Macro enabled) workbook. Below is the setup for your sheets.
Your sheet names:
* START_PAGE
* FIRST_SHEET
* SECOND_SHEET
* THIRD_SHEET
* LOGIC
3.108.2 Set the START_PAGE tab color to green on the Standard Color row as it is depicted in the picture below.
3.108.3 Click the sheet named FIRST_SHEET then hold down the Ctrl key while selecting the sheet named THIRD_SHEET. This will select both sheets. Right Click the 
over either of the selected sheets and from the top row of the Theme Colors select Blue, Accent 1 as shown in the description below.
3.108.5 For the sheet named SECOND_SHEET make the tab color Orange, Accent 2, which you will select from the top row of the theme colors.
3.109 Adding Test Data
If you look in the Homage section, you will see that I have added PyCharm Community Edition. I used Python code to create the email lists that we will use with this project.
In cell F2 on the sheet named FIRST_SHEET copy and paste the following list to that cell.
Harry.Heryum@ittagin.com; Craspe.A@medusa.com; Electric.B@teries.com; Dot.dot@line.com; Ra.Er@sayitagain.org; En.Horax@bugsme.com; Eppy.Ick@granite.org; Little.Cree@Ure.edu; Hydrous.Silicl@Ess.mil; Tu.The@mosphere.mil; Cindy.Smorgeshborg@org.com; The.Fan@Icks.com; Leppy.Ed@slanging.com;Pe.Rofi@feedthekids.com; Macroleppy.Um@magicmushroom.com;In cell F2 on the sheet named SECOND_SHEET copy and paste the following list to that cell.
Cindy.Smorgesh@borg.org; The.Spect@Or.org; Al.Iswell@thehomestead.edu; Haleci.A@tessellaris.edu; Thermanti.Es@Rotatingfans.org; Psue.Suga@douglasfir.com; To.The@Mosphere.mil; Ri.To@myprivatevenice.org; Tail.Fe@Hers.org; A.Mul@Toe.com; To.Zar@Hustra.com; Po.Heca@scalyfoot.com; Macacus.Pile@Us.gov; Sacer.Al@wheresmypriest.com; Extra.Al@itshers.com; En.Helium@scottiestissue.com; Heru.Us@historians.edu; In cell F2 on the sheet named THIRD_SHEET copy and paste the following list to that cell.
The.Adri@Ic.com; The.Gree@Est.com; Johnny.Walker@Red.com; Pleasantly.Stimul@Ing.org; Sumver.Ovadarenbo@aplaceandtime.com; The.Ultim@E.org; To.Radi@Ion.org; Aceituna.Zap@Era.com; Gona.ropin@fertmeds.com; The.Arom@Ic.com; Dorothy.Tee@definatelydot.com; A.Gradu@Ed.edu; Theo.L@In.com; A.Fan@Ical.com; Paleozoic.Form@Ions.org; Closely.Rel@Ed.edu; 3.110 Now Let’s design the form
Note: If you have multiple instances of Excel open make sure that you are creating the form within the same workbook.
In the Visual Basic Editor on the Standard Toolbar Click that eeny weeny, tiny, little, down-pointed arrow on the right side of the insert … button. That tiny almost non existent button exposes a dropdown menu to insert a UserForm, Module, Class Module, or Procedure.
Select UserForm. In the Properties window across from the row (name) rename the form AuxEmailForm.
Go back to the Standard Toolbar and back to that eeny weeny down-pointed arrow and this time from the dropdown menu select Module. From the Properties Window rename Module Tools.
In the Properties window select the Categorized tab as shown within the red circle in the picture diagram. This categorizes all the items in the left column.
Scroll to find the section named Position. From there set the Height to 378.75, the Left to 0, the StartUpPosition to 1 - CenterOwner, the Top to 0 and the Width to 341.25 as indicated within the red square in the picture description.
3.111 Adding items to the Form
Using the visual description below, see how close you can match the design.
hint: The items consist of:
* 4 Labels,
* 4 commandButtons,
* 2 listBoxes, and
* 2 comboBoxes
Click image for names of items
3.112 I promise we’re programming now
Double click on any open space inside your form as indicated in the red squared off areas in the diagram below
This will take you behind the scenes to the code area of the userform.
Select and delete those lines from Private Sub UserForm_Click() to End Sub and from the right dropdown window select initialize.
On the empty line between the Private Sub UserForm_Initialize() and the End Sub type the tab key and type dim sh as worksheet. When you type the a in as, a list will dropdown for you to select from. As you type worksheet another dropdown list will appear to help you select your words. Your code should look like the following after you hit enter:
Private Sub UserForm_Initialize()
Dim sh As Worksheet
End SubNotice that you typed all lowercase words and when you hit enter certain letters capitalized. This is because they are keywords. You’ll learn more about keywords later. For now let’s examine the Dim. Dim is used to give variables existence. Dim stands for dimension. In this particular case we are dimensioning sh as a worksheet. Normally we would still have to give the variable a value but in this case both the value and the type are set all in one. We will explore more variables and types later.