Where Do I Begin...
I'm sure there are many ways to teach people how to code but I am going to use my own methods that I have developed while explaining VBA to my co-workers over the years. There is no way I can explain everything about coding here in this post but I will try to cover the basics and hopefully provide you with a good foundation that you can build on going forward.
How VBA is Structured
When explaining VBA code to someone who has no experience with computer languages, I like to use the analogy of a computer's folder hierarchy. A typical folder structure on a PC might look like this:
Hopefully everyone is comfortable seeing a structure like above and I am here to tell you that VBA coding is kind of setup like your computer's folder structure.
Walking Through The Hierarchy
I am a visual learner myself and I always find it easiest to explain how to write a line of code with an example. So let's write a VBA line that tells Excel to clear the contents of range A1:C50.
Instead of Drives on a computer, VBA starts out with a reference to the computer program or in its terms "Application" that you are wanting to target. This can be Word, Excel, NotePad, Internet Explorer, or whatever program you want to access that also speaks the Visual Basic language. VBA assumes that what ever program you are storing your code in is the application you writing for. Because of this, most people do not add the program name to their line of code but for purpose of this example we will. With this in mind, the first part of our line of code will be as follows since we are wanting to work with the Excel Application:
After we specify the Application we want to use, we then need to tell Excel which Workbook we want to modify. You can do this in a couple of different ways:
This references the workbook that your VBA code is written in. Typically you use this when you want the macro to only effect one workbook. The benefit to this is that if you are viewing another workbook and you accidentally run your code it will not affect that workbook. REMEMBER YOU CANNOT UNDO A MACRO'S COMMANDS ONCE IT HAS RUN!
This refers to the workbook that you are currently viewing on your screen. You will mainly use this type of reference when you create code that is aimed at affecting any workbook (for example if you write a macro to format text a specific way)
Writing your workbook level reference this way allows you to specify the name of the workbook you want to take action with. I rarely refer to workbooks in this way because if I or someone else changes the name of the workbook, the macro will not be able to find it because it is referencing the old name.
I don't believe I have ever used this form of referencing a workbook but I will throw it out there just as an FYI. When you use a number to reference a workbook you are referencing the order in which the workbooks were opened by Excel. This can get tricky because if you have add-in files active, those are going to be the first workbooks opened when your Excel Application starts-up. You can determine which position your workbook is in by referencing its order in the VBA Project Window (location is shown in Lesson Three).
For this example, I am going to decide that I only want the scope of the macro to be within our workbook, so we are going to use a ThisWorkbook reference in our code;
'My Docs' Folder
After we specify the workbook we want our code to target we can then move onto the next level and specify the tab or worksheet that we want to get our code to point to. Like the Workbook level, the Worksheet level has a few different referencing options:
This is very similar in functionality to ActiveWorkbook. ActiveSheet points you to the tab that you are currently viewing. This is great to use when you want to give your code the flexibility to run on a worksheet of your choosing.
In this form, you can spell out the name of the worksheet you want to target. Because your sheet name is considered text and is not part of the VBA language, you will need to make sure your tab name is surrounded by quotes. This lets Worksheet know that you are spelling out a name of a tab. If your name does not have quotes then VBA will think that Sheet1 is a variable that you created instead of text.
You can also target the order of your worksheets. The above code is bringing you to the second worksheet from the left inside your workbook.
Once we've specified the worksheet we want to modify, we then need to specify which cells we want to do something to (in this case, clear their contents). There are a bunch of nifty ways to tell VBA which range we want but here I am going to cover the two main ways for referencing a single cell and a range of cells.
Reference A Single Cell
Range("A4") - You can reference a cell by inserting its string name (range name surrounded with quotes).
Cells( 4, 1) - You can also reference a cell by using Cells( ) and inputting the row number and then the column number. Note that Cells( ) will not accept a letter as an input for the column number.
Reference A Range of Cells
Range("A1:E5") - Similar to referencing a single cell by name, you can also reference multiple cells by inserting the top left cell of your range, a colon, and then the bottom right cell of the range.
Range(Cells(1,1),Cells(5,5)) - By embedding two Cells( ) references inside a Range( ) function, you can refer to multiples cells. To do this, use a numerical reference (row number, then column number) for the top left cell in your range and then do the same for the bottom right cell of the range.
'Budget' Excel File
Congratulations! We have made it to the lowest level of what we wanted to modify or if you want to keep with the analogy we finally clicked on the folder that is storing our file. Now in this folder there are hundreds of files we can select so we need to specify which unique file we want to open.
There are lots of different properties you can change and actions you can take at the Range level. You can change a color, you can perform a calculation.....you can do pretty much anything you can do manually on a spreadsheet. A benefit to Microsoft being the creator of both Office and Visual Basic is that most of the computer language is used in the program functionality. So in our example, if we wanted to delete the values in our selected cells while in Excel, we would simply go to the Home tab, click on the Clear drop-down in the Editing section, and click Clear Contents. In VBA language to simulate doing the same thing all we have to add to our line of code "ClearContents". Pretty easy, right?!
What's Up With All the Periods?
Now that we have written a fully actionable line of code, you might be asking yourself what is the significance of putting a period in-between each level in VBA? I like to compare the use of periods in VBA to the use of backward slashes in a folder's address. What these two symbols do in their respective areas is connect the previous level to the next level. Another way of picturing this is that they are the stairs from one floor to another in a home. You can't go from the basement to the first floor without using the stairs to get there.
The VBA Defaults
The VBA Language creators at Microsoft realized that we as computer coders want to type as little as possible. They found it in their hearts to program in some defaults or assumptions of which levels we may want to be targeting. Below are a list of some of the major defaults that VBA will assume in Excel
So if we leave out any of these levels in our code, the VBA language is going to assume the respective level reference above. Lets look at a few examples of how we can shorten our code and understand what that means.
- Starting at the Excel Application level
ThisWorkbook.Worksheets("Sheet2").Range("B3").Value = 10
- Starting at the ActiveWorkbook level
Worksheets("Sheet2").Range("B3").ColumnWidth = 4.3
- Starting at the ActiveSheet level
Range("B3").Interior.Color = RGB(75, 172, 198)
The Visual Basic Editor Wants To Help You!
Last but not least I want to leave you with a tip and you may have noticed this while typing in the Visual Basic Editor. Every time you type a period after one of your 'levels' you may have noticed that a little window pops up with a bunch of words. What this floating box displays is every possible word that can come after your previous code phrase. This is a feature that I often ignored when first learning how to write VBA code because I really didn't understand what it was. Eventually I took the time to figure out what it was showing me and the below little box has helped prevent errors in my code ever since.
Share Your Code Writing Tips!
If you have any tips that you would like to share with all the VBA beginners out there please leave your tips below in the comments section. I will try to compile them into a tips page so everyone can read them in one place.