We began by using the Macro recorder to create a Macro, and then Edit/fix that Macro. This is fine as far as it goes. But using the Macro recorder also has some limitations. Some of them, like unnecessarily long code because every keystroke is recorded, can be fixed afterwards. But the one thing you cannot get away from is that recording macros cannot cover everything you might want to do. You cannot, for instance, display a dialog box or user form when recording a macro; you have to write this code in manually. So we have to start looking at writing code in the VBA Editor.
Setting up to Code
You want to set up your workspace for writing code, so open the Visual Basic Editor and make sure the Project Explorer is displayed. Make sure the Properties Window is displayed. If you need to review this part, go back to The Visual Basic Editor on the parts of the Visual Basic Editor.
Next, as we discussed in the previous tutorial, you want to be sure that the editor will require you to declare your variables explicitly. Choose Tools–>Options, go to the Editor tab and place a check mark in Require Variable Declaration. This will cause the Editor to check during Runtime and give you a warning if you have any undeclared variables.
Public vs. Private
A useful concept when you start writing Macros is Public vs. Private. In this case it is not a setting for you vs. your co-workers, but rather a setting by modules. Private means you can only access something within the module where it was created, and Public means it can be called by other modules as needed. These settings can apply either to complete subroutines or to individual variables. Equivalent terms are DIM and GLOBAL, where DIM = Private, and GLOBAL = Public. If a variable is defined as Private or DIM within a sub, it is only available within that sub. Any other subs within the module cannot call it. If you define the variable as Private or DIM at the beginning of the module, before you create any Subs, every sub within the module can call it. If you want to make the variable visible to other modules you need to make it Public.
The bottom line is that you want to consider the variables you need, define them explicitly at the beginning, and set their scope (i.e. Public vs. Private) before you start coding. You can always fix things later, but at the very least whenever you create a variable, you should do these things at that time and not leave them for later, because later you may forget.
The Object Model
VBA has an Object model that you need to understand as you start to code. The basics of Objects is that they have a hierarchy, they properties, and they have inheritance. Pretty much all modern office programs use an Object model in the same way, and you want to have a good grasp of this as you get started. The Hierarchy in Excel is like this:
- Application
- Workbook
- Worksheet
- Range
- Background (for example)
- Range
- Worksheet
- Workbook
In addition to these, there are objects like Charts, Pivot tables, and so on. All of them can be used in VBA coding. They all have a set of properties, and a set of uses, which are called methods. A Worksheet Cell, for instance, has properties like its background color, font, cell height, and so on. A Worksheet has methods that included being opened or closed. Properties are like adjectives for your objects. So for example, instead of just saying you have a car, you might say you have a red Ford Mustang car. In this case, red, Ford, and Mustang are all properties of the object Car. Methods are like verbs. You can start your car, backup your car, turn your car, and so on.
Excel has a lot of Objects. If you Press F2 you can open the Object Browser, which has hundreds of them. And many of the objects listed on the left have other objects that can be contained in them. When you use them in coding, you always start with the highest object and work your way down, with a period separating them:
Application.Workbook.Worksheet.Range
Note that Application is not needed since you are writing code within the application already, so that is assumed. Workbook and Worksheet have default values of being the active workbook and the active worksheet, but sometimes you may want to incorporate objects from a different workbook or worksheet, so it helps to understand the syntax.
tarting to Code
With your visual Basic Editor open and ready for action, go to the Insert menu, and select Module to begin a new module. If all you see is a blank window in the editor, stop what you are doing, go to Tools–>Options, and place a checkmark in the Require Variable Declaration box, and click OK. Now go back to the Insert menu and insert a module. You should now see at the top of the editor window “Option Explicit”, which means you will declare all of your variables explicitly, and of course being a good coder you will also decide if they are public or private when you do so. And you might as well give it a name now. Go to the Properties window in the lower left, and right now it will Module with a number (e.g. Module 4). Click the space next to Name, and give it a meaningful name so you will know what it is later.
Modules contain subs, so now the first thing you do is type Sub, followed by a Space, and then the name of the Sub. Press Enter and you will see Excel do a couple of things:
- Place empty parentheses after the name
- Place an End Sub statement at the bottom
The parentheses are there in case we want to pass an argument into the macro from somewhere else. since that is topic for another day, skip over that for now. But the End Sub statement is important because once you have started an object you need to say when it is complete. Many times a module will only contain one Sub, and so you will hear people call a Sub a Macro, but that is not really a correct understanding of the Object model. It is the Module that is the Macro, and it can contain more than one Sub, and can even call subs from other Modules as long as those are Public. So properly opening and closing Subs is important, and Excel is helping you here.
Next, you want to put in a little comment to explain what you are about to do. This will help you in the future if you need to figure out what you were doing, and even more so if someone else has to pick up your code and figure it out. To do this, hit the Tab key once for an indent, type an Apostrophe, and then your comment. This can be as little as a single sentence, so long is it clearly explains the purpose of the Sub. When you are done, press the Enter key, and you will see the whole line turn Green, which is an indication that Excel knows not to try and execute that line.
Now, we need to do something, and the Object model comes in to play here. Let’s say we want to make cell C3 of the active sheet use the font Arial. We’ll start by typing Application, followed by a period. As soon as we type the period, a pop-up window opens with all of the objects we could use after Application. In this case, the next object will be Workbooks, and so on. The proper syntax is to to follow the object type (e.g. Workbooks) by parentheses, and inside the parentheses the specific example inside of quotations marks. Here is a line of code that accomplishes our example:
Application.Workbooks("Book2").Worksheets("Sheet1").Range("c3").Font.Name = "Arial"
Now, if we are going to talk about best practices it might be a good idea to give actual names to the Workbook and the Worksheet, which will make things easier as you go forward.
Note that as we said it starts at the highest level, Application. But since we are in Excel it probably assumes that anyway. So if we delete that part and make the line:
Workbooks("Book2").Worksheets("Sheet1").Range("c3").Font.Name = "Arial"
It turns out it will work just as well. And because the active workbook and the active worksheet are defaults, you could even shorten it to:
Range("c3").Font.Name = "Arial"
and that would work as well so long as you are in the right sheet of the right workbook before you run the macro. In the long run, however, I think you are better off defining everything carefully. It is up to you of course, but coding is hard enough without undefined objects all over the place. So when I write code, I name my workbook and worksheets, and use those names in my code. It might mean that I have a little more work if I try to reuse the code in a different context, but forcing me to rethink how my code applies is usually a good thing.
Save as PDF
