| A subroutine is a collection of VBA statements that, taken together, accomplish something. The term is a little unfortunate, implying that it is subordinate to something. The meaning is broader than that, though: A subroutine can be the main, top-level code in your project, or it can be a set of code that accomplishes some smaller task on behalf of the main code. Providing the Subroutine's Basic Elements In VBA, subroutines are stored in modules, and establishing a module is where it all starts. To establish a module you need to start the VBE. From the main Excel window, one way to get to the VBE is by choosing Macro from the Tools menu, and then selecting Visual Basic Editor from the shortcut menu. As you become more experienced with the VBE, you'll find yourself using the keyboard shortcut (Alt+F11) so consistently, that you're liable to forget there's another way to get there. When you start the VBE, the window shown in Figure 7.3 appears. Figure 7.3. The VBE looks much the same in other Office applications such as Word and Access. 
The two windows on the left in Figure 7.3 aren't necessary if you're establishing a subroutine, but they can be useful. The VBE refers to the whole collection of sheets, modules, and user forms in an Excel file as a project. When you have several modules in a project, the Project Explorer is the most convenient way to switch among them. The Properties Window is not as routinely useful as the Project Explorer, and you might decide to close it if only to get it out of your way. I like having it there because I frequently have hidden worksheets in my workbooks. The Properties window is a convenient way to hide and unhide worksheets, by changing its Visible property. TIP You can set a worksheet's Visible property to xlSheetVisible: This is its normal state. Or you can set it to xlSheetHidden: This is the same as choosing Format, Sheet, Hide. There are at least two problems with a hidden worksheet. Unless the workbook is protected, the user can tell that the sheet exists because the Unhide menu item is enabled, and the user can unhide the sheet. By using the Properties Window (or VBA code) to set a worksheet's Visible property to xlSheetVeryHidden, you solve both problems. Even if the workbook itself is not protected, the Unhide menu item remains disabled and, therefore, the user can neither unhide from the Format menu nor infer that a hidden sheet exists.
After you have the VBE open, there are several ways to establish modules, depending on what you mean to accomplish. The most basic, straightforward method is to choose Module from the Insert menu. Figure 7.4 shows the result. Figure 7.4. The active object is the one whose properties appear in the Properties window. Standard modules have only one property. 
TIP Notice the Option Explicit statement at the start of the module in Figure 7.4. It's good programming practice to use that statement because it forces you to explicitly declare all variables in your code (the importance of explicit declaration is discussed in the next section). In case you forget to use it, you can choose Options from the VBE's Tools menu, click the Editor tab, and make sure that the Require Variable Declaration check box is filled. Then all new modules automatically include the Option Explicit statement.
Now, all that's necessary is to type a statement such as the following somewhere below the Option Explicit statement: Sub MoveListToDatabase
The string MoveListToDatabase becomes the name of the subroutine, by virtue of being the first string of characters following the keyword Sub. When you have finished typing the name of the subroutine, press Enter. When you do, two things immediately happen: An empty pair of parentheses is automatically provided following the name of the subroutine (unless you've typed the parentheses yourself). An End Sub statement is provided, with an empty line between the Sub and the End Sub where you can begin entering your code.
Both of these have already happened in Figure 7.5. Figure 7.5. The subroutine's basic elements are now complete. 
At this point, you've established a subroutine: You've provided a beginning statement (Sub) and an ending statement (End Sub), and there is a pair of parentheses at the end of the Sub statement (see Figure 7.5). You could now switch back to the workbook window, choose Tools, Macro, Macros, select the MoveListToDatabase macro, and click the Run button. The subroutine would run, although it wouldn't do anything because you have not yet supplied the subroutine with any instructions statements that indicate where to find data and what to do with it. Declaring Variables The previous section urged you to use Option Explicit, and to use the Require Variable Declaration option to provide that statement automatically. The following paragraph provides the reason. In the bad old days roughly, the 1950s through the 1980s coding followed a go-as-you-please approach (coding is just another term for programming). When you needed a new variable, all you had to do was use it. For example, this would have been not only syntactically legal, but part of established practice even if the program had never mentioned the existence of a NumberOfRows variable prior to that statement: NumberOfRows = Rows(MyMatrix)
This sort of thing led to code that was very difficult to understand and document, much less troubleshoot. Especially in a lengthy program, it was all too possible for the coder to forget that he had already used a NumberOfRows variable to store the number of rows in the range named MyMatrix. Then he might create yet another variable to carry the same information, just by using a new one on the fly. Imagine the difficulty faced by the programmer months or even years later, trying to remember what he had in mind with two variables for a single purpose. Worse yet, the unfortunate coder might remember that he was already using a variable named NumberOfRows, but err typographically when using it later. Suppose that he wanted to start creating a square range by putting the value of NumberOfRows into a variable named NumberOfColumns: NumberOfColumns = NumberOfRow
But look what he's done: He has miskeyed NumberOfRows by leaving off the final s. In so doing, he's created a new variable remember, they can be created just by using them. And a new variable would have the special value of Empty, indicating that the variable is as yet uninitialized. So, the variable NumberOfColumns, instead of taking as its value the number of rows in MyMatrix, takes the value 0 from NumberOfRow. NOTE A variable declared in this way (thus, in the absence of Option Explicit) using VBA would be of a special type, Variant. Variant variables start life with a special value: Empty. Numeric variables begin as zero, and string variables begin as zero-length strings ("").
Things are not going to work as the programmer had anticipated. It's going to be tough for him to figure out what's gone wrong because he'll be looking for problems with NumberOfColumns when the source of the problem is NumberOfRow. One good way to protect yourself against this sort of nonsense is to require variable declaration. That means that before you can use a variable, you have to declare it. In VBA, you do that with a Dim statement such as this one, which declares two variables: Dim NumberOfRows As Integer, NumberOfColumns As Integer
Dim, like Sub, is a little misleading. It is short for dimension, which traditionally meant to specify the structure of a memory array. In VBA syntax, when you dim a variable, you declare to the VBE that the variable exists, and you usually also declare what sorts of values the variable can take on. In this example, the variables NumberOfRows and NumberOfColumns can take on integer values only: Neither one of them can equal 5.3 or 5/4/2009 or "Fred." In this example, then, the effect of Option Explicit is to prevent you from using a variable named NumberOfRow (again, note the missing final s) when you haven't already declared it. So, if you miskey a variable's name, you won't have automatically created a new variable. The VBE will note that you're trying to use an undeclared variable and will warn you with an error message. The effect of declaring your variables in one or more Dim statements is that you can look back to see what variables you have declared. It's a lot easier to find them in Dim statements than it is to scan an entire program looking for first instances of a variable. This helps you avoid declaring multiple variables to accomplish the same purpose. NOTE It's also considered good programming practice to declare your variables at the beginning of a subroutine or the beginning of a module, and not defer any declarations until farther down in the code. It is, however, legal to do so.
If you don't use Option Explicit, the VBE will not complain when you suddenly refer to a new variable whether or not you mean to. It will let you use that new variable and, by the way, it will give that variable the most flexible (and resource-intensive) type: Variant. A variable whose type is Variant can take on nearly any sort of value: integers, decimal numbers, strings, dates, and so on. The Variant type is the default in a Dim statement such as this: Dim NumberOfRows, NumberOfColumns
If you don't specify the type of a variable, it defaults to Variant. This is better than not declaring variables at all in the absence of Option Explicit, but it's much better to declare a variable's type. Among other reasons, you protect yourself against assigning, say, "Fred" to a variable declared as Integer. NOTE It's in the Dim statement that you'll declare variables that represent database objects. You must first establish a reference to the object library, as discussed earlier in this chapter. But after that reference has been established, you can use statements such as these: Dim dbMyDatabase As Database Dim tdfDoors As TableDef Dim qdfDoors As QueryDef
TIP To start the name of a variable with a clue to what it represents is a matter of personal style, but it can't hurt and can sometimes help. In the example from the previous note, the variable qdfDoors is declared to be a query definition (QueryDef). The 3-letter string qdf can help you or someone else remember, later in the code, that qdfDoors represents a query definition.
| 