In This Chapter
Knowing when, why, and how to use comments in your code
Using variables and constants
Telling VBA what type of data you’re using
Knowing why you may need to use labels in your procedures
Because VBA is a real, live programming language, it uses many elements common to all programming languages. In this chapter, I introduce you to several of these elements: comments, variables, constants, data types, arrays, and a few other goodies. If you’ve programmed using other languages, some of this material will be familiar. If you’re a programming newbie, it’s time to roll up your sleeves and get busy.
Using Comments in Your VBA Code
A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote. Use comments liberally to describe what the code does (which isn’t always obvious by reading the code itself). Often, code that makes perfect sense today mystifies you tomorrow. Been there. Done that.
You begin a comment with an apostrophe (‘). VBA ignores any text that follows an apostrophe in a line of code. You can use a complete line for your comment or insert your comment at the end of a line of code. The following example shows a VBA procedure with three comments, though they’re not necessarily good comments:
The “apostrophe indicates a comment” rule has one exception: VBA doesn’t interpret an apostrophe inside a set of quotation marks as a comment indicator. For example, the following statement doesn’t contain a comment, even though it has an apostrophe:
When you’re writing code, you may want to test a procedure without a particular statement or group of statements. Rather than delete the statements, simply turn them into comments by inserting apostrophes. VBA ignores statements enclosed in apostrophes when executing a routine. Simply remove the apostrophes to convert the comments back to statements.
When testing a procedure you may want to remove some statements temporarily. Rather than delete the statements, you can convert them to comments. Then, when testing is completed, convert the comments back to statements. In the VBE, choose View Toolbars Edit to display the Edit toolbar that you see in Figure 7-1. To convert a block of statements to comments, select the statements and click the Comment Block button. To remove the apostrophes, select the statements and click the Uncomment Block button.
Figure 7-1:
The VBE Edit toolbar contains several useful buttons.
Although comments can be helpful, not all comments are created equal. For example, the following procedure uses lots of comments, but they add nothing of value. The code is clear enough without the comments.
Everyone develops his or her own style of commenting. To be useful, however, comments should convey information that’s not immediately obvious from reading the code. Otherwise, comments just chew up bytes and make files larger than necessary.
The following tips can help you make effective use of comments:
Briefly describe the purpose of each Sub or Function procedure you write.
Use comments to keep track of changes you make to a procedure.
Use a comment to indicate that you’re using a function or a construct in an unusual or nonstandard manner.
Use comments to describe the variables you use, especially if you don’t use meaningful variable names.
Use a comment to describe any workarounds you develop to overcome bugs in Excel.
Write comments as you develop code, rather than saving the task for a final step.
Using Variables, Constants, and Data Types
VBA’s main purpose is to manipulate data. VBA stores the data in your computer’s memory; it may or may not end up on disk. Some data, such as worksheet ranges, resides in objects. Other data is stored in variables that you create.
Understanding Variables
A variable is simply a named storage location in your computer’s memory. You have lots of flexibility in naming your variables, so make the variable names as descriptive as possible. You assign a value to a variable using the equal sign operator. (More about this later in the “Using Assignment Statements” section.)
The variable names in these examples are on the left side of the equal signs. Note that the last example uses two variables.
VBA enforces a few rules regarding variable names:
You can use letters, numbers, and some punctuation characters, but the first character must be a letter.
You cannot use any spaces or periods in a variable name.
VBA does not distinguish between uppercase and lowercase letters.
‘ You cannot use the following characters in a variable name: #, $, %, &, or! .
‘ Variable names can be no longer than 254 characters. Of course, you’re only asking for trouble if you use variable names 254 characters long.
To make variable names more readable, programmers often use mixed case (for example, Interest Rate) or the underscore character (interest_rate).
VBA has many reserved words that you can’t use for variable names or procedure names:
Built-in VBA function names such as Ucase and Sqr VBA language words such as Sub, With, and For
If you attempt to use one of these names as a variable, you may get a compile error (your code won’t run). So, if an assignment statement produces an error message, double-check and make sure that the variable name isn’t a reserved word.
What are VBA’s data types?
When I talk about data type, I’m referring to the manner in which a program stores data in memory — for example, as integers, real numbers, or strings. Although VBA can take care of these details automatically, it does so at a cost. (There’s no free lunch.) Letting VBA handle your data typing results in slower execution and inefficient memory use. For small applications, this usually doesn’t present much of a problem. But for large or complex applications,which may be slow or need to conserve every last byte of memory, you need to be on familiar terms with data types.
VBA’s automatically handling all the data details makes life easy for programmers. Not all programming languages provide this luxury. For example, some languages are strictly typed, which means the programmer must explicitly define the data type for every variable used.
VBA has a variety of built-in data types. Table 7-1 lists the most common types of data that VBA can handle.
| Table 7-1 | VBA’s Built-in Data Types | |
| Data Type | Bytes Used | Range of Values |
| Boolean | 2 | True or False |
| Integer | 2 | -32,768 to 32,767 |
| Long | 4 | -2,147,483,648 to 2,147,483,647 |
| Single | 4 | -3.402823E38 to 1.401298E45 |
| Double (negative) | -1.79769313486232E308 to -4.94065645841247E-324 | |
| Double (positive) | 4.94065645841247E-324 to 1.79769313486232E308 |
|
| Currency | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
|
| Date | 1/1/100 to 12/31/9999 | |
| String | 1 per char | Varies |
| Object | 4 | Any defined object |
| Variant | Varies | Any data type |
| User defined | Varies | Varies |
In general, choose the data type that uses the smallest number of bytes but can still handle all the data the program assigns to it.
Declaring and scoping variables
If you read the preceding sections, you now know a bit about variables and data types. In this section, you discover how to declare a variable as a certain data type.
If you don’t declare the data type for a variable you use in a VBA routine, VBA uses the default data type: variant. Data stored as a variant acts like a chameleon; it changes type depending on what you do with it. For example, if a variable is a variant data type and contains a text string that looks like a number (such as “143″), you can use this variable for string manipulations as well as numeric calculations. VBA automatically handles the conversion, which may seem like an easy way out — but remember that you sacrifice speed and memory.
Before you use variables in a procedure, it’s an excellent practice to declare your variables — that is, tell VBA each variable’s data type. Declaring your variables makes your program run faster and use memory more efficiently. The default data type, variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows a variable’s data type, it doesn’t have to investigate and can reserve just enough memory to store the data.
To force yourself to declare all the variables you use, include the following as the first statement in your VBA module:
When this statement is present, you won’t be able to run your code if it contains any undeclared variable.
You need to use Option Explicit only once: at the beginning of your module. Keep in mind that the Option Explicit statement applies only to the module in which it resides. If you have more than one VBA module in a project, you need an Option Explicit statement for each module.
Suppose that you use an undeclared variable (that is, a variant) named Current Rate. At some point in your routine, you insert the following statement:
This misspelled variable, which is difficult to spot, will probably cause your routine to give incorrect results. If you use Option Explicit at the beginning of your module (and declare the Current Rate variable), Excel generates an error if it encounters a misspelled variation of that variable.
To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, turn on the Require Variable Definition option. You’ll find it in the Editor tab of the Options dialog box (in the VBE, choose Tools Options). I highly recommend doing so.
You now know the advantages of declaring variables, but how do you do this? Before getting into the mechanics, I need to discuss one more topic: a variable’s scope.
Recall that a workbook can have any number of VBA modules. And a VBA module can have any number of Sub and Function procedures. A variable’s scope determines which modules and procedures can use the variable. Table 7-2 describes the scopes in detail.
| Table 7-2 | Variable’s Scope |
| Scope | How the Variable Is Declared |
| Procedure only | By using a Dim or a Static statement in the |
| procedure that uses the variable | |
| Module only | By using a Dim statement before the first Sub |
| or Function statement in the module | |
| All procedures in all modules | By using a Public statement before the first Sub |
| or Function statement in a module |
If you’re completely confused at this point, don’t despair. I discuss each of these in the following sections.
Procedure-only variables
The lowest level of scope for a variable is at the procedure level. (A procedure is either a Sub or a Function procedure.) Variables declared with this scope can be used only in the procedure in which they are declared. When the procedure ends, the variable no longer exists, and Excel frees up its memory. If you execute the procedure again, the variable comes back to life, but its previous value is lost.
The most common way to declare a procedure-only variable is with a Dim statement placed between a Sub statement and an End Sub statement (or between a Function and an End Function statement). The Dim keyword is short for dimension, which simply means you are setting aside memory for a particular variable. You usually place Dim statements immediately after the Sub or Function statement and before the procedure’s code.
The following example shows some procedure-only variables declared by using Dim statements:
Notice that the last Dim statement in the preceding example doesn’t declare a data type; it declares only the variable itself. The effect is that the variable My Value is a variant.
By the way, you can also declare several variables with a single Dim statement, as in the following example:
Unlike some languages, VBA doesn’t allow you to declare a group of variables to be a particular data type by separating the variables with commas. For example, though valid, the following statement does not declare all the variables as integers:
In this example, only k is declared to be an integer; the other variables are declared to be variants.
If you declare a variable with procedure-only scope, other procedures in the same module can use the same variable name, but each instance of the variable is unique to its own procedure. In general, variables declared at the procedure level are the most efficient because VBA frees up the memory they use when the procedure ends.
Module-only Variables
Sometimes, you want a variable to be available to all procedures in a module. If so, just declare the variable before the module’s first Sub or Function statement — outside any procedures. This is done in the Declarations section, at the beginning of your module. (This is also where the Option Explicit statement, discussed earlier in this chapter, is located.) Figure 7-2 shows how you know when you are working with the Declarations section.
Figure 7-2:
Each VBA module has a Declarations section, which appears before any Sub or Function procedures.
As an example, suppose that you want to declare the Current Value variable so that it’s available to all the procedures in your module. All you need to do is use the Dim statement in the Declarations section:
With this declaration in place — and in the proper place — the Current Value variable can be used from any other procedure within the module, and it retains its value from one procedure to another.
Public variables
If you need to make a variable available to all the procedures in all your VBA modules in a workbook, declare the variable at the module level (in the Declarations section) by using the Public keyword. Here’s an example:
The Public keyword makes the Current Rate variable available to any procedure in the workbook — even those in other VBA modules. You must insert this statement before the first Sub or Function statement in a module.
If you would like a variable to be available to modules in other workbooks, you must declare the variable as Public and establish a reference to the workbook that contains the variable declaration. Set up a reference by using the Tools References command in VBE.
Static variables
Normally, when a procedure ends all of the variables are reset. Static variables are a special case because they retain their value even when the procedure ends. You declare a static variable at the procedure level. A static variable may be useful if you need to track the number of times you execute a procedure. You can declare a static variable and increment it each time you run the procedure.
As shown in the following example, you declare static variables by using the Static keyword:
The code keeps track of the number of times the procedure was executed. The value of the Counter variable is not reset when the procedure ends.
Even though the value of a variable declared as Static is retained after a variable ends, that variable is unavailable to other procedures. In the preceding MySub procedure example, the Counter variable and its value are available only within the MySub procedure. In other words, it’s a procedure-level variable.
Working with constants
A variable’s value may (and usually does) change while your procedure is executing (that’s why they call it a variable). Sometimes, you need to refer to a value or string that never changes — a constant. A constant is a named element whose value doesn’t change.
As shown in the following examples, you declare constants by using the Const statement:
Using constants in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it’s better to declare the value as a constant and refer to its name rather than the value. This makes your code more readable and easier to change; should the need for changes arise, you have to change only one statement rather than several.
Like variables, constants have a scope. Keep these points in mind:
To make a constant available within only a single procedure, declare the constant after the procedure’s Sub or Function statement.
To make a constant available to all procedures in a module, declare the constant in the Declarations section for the module.
To make a constant available to all modules in the workbook, use the Public keyword and declare the constant in the Declarations section of any module.
If you attempt to change the value of a constant in a VBA routine, you get an error. This isn’t surprising because a constant is constant. Unlike a variable, the value of a constant does not vary. If you need to change the value of a constant, what you really need is a variable.
Excel and VBA contain many predefined constants, which you can use without the need to declare them yourself. In general, you don’t need to know the value of these constants to use them. The macro recorder usually uses constants rather than actual values.
The following simple procedure uses a built-in constant (xlCalculationManual) to change the Calculation property of the Application object. (In other words, this changes the Excel recalculation mode to manual.)
I discovered the xlCalculationManual constant by recording a macro that changed the calculation mode. I also could have looked in the Help system under “Microsoft Excel Constants.” As shown in Figure 7-3, the Help system lists all the built-in constants.
The actual value of the built-in xlCalculationManual constant is -4135. Obviously, it’s easier to use the constant’s name than to look up the value (even if you knew where to look). By the way, the constant for changing to automatic calculation mode is xlCalculationAutomatic; its value is —1105. As you can see, many of the built-in constants are just arbitrary numbers that have special meaning to VBA.
Figure 7-3:
The Help system lists Excel constants.
To find the actual value of a built-in constant, execute a VBA statement such as the following:
Working with strings
Excel can work with both numbers and text, so it should come as no surprise that VBA has this same power. Text is often referred to as a string. You can work with two types of strings in VBA:
Fixed-length strings are declared with a specified number of characters. The maximum length is about 65,526 characters.
Variable-length strings theoretically can hold as many as two billion characters.
Each character in a string takes one byte of storage. In addition, a variable-length string consumes an additional 16 bytes. Therefore, if you’re striving for efficiency, it’s better to use fixed-length strings if possible.
When declaring a string variable with a Dim statement, you can specify the maximum length if you know it (it’s a fixed-length string) or let VBA handle it dynamically (it’s a variable-length string). The following example declares the MyString variable as a string with a maximum length of 50 characters. (Use an asterisk to specify the number of characters, up to the 65,526 character limit.) Your String is also declared as a string but its length is unspecified:
When declaring a fixed-length string, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a value in VBA.
Working with dates
Another data type you may find useful is date. You can use a string variable to store dates, but then you won’t be able to perform date calculations. Using the date data type gives your routines greater flexibility. For example, you might need to calculate the number of days between two dates. This would be impossible if you used strings to hold your dates.
A variable defined as a date uses eight bytes of storage and can hold dates ranging from January 1, 0100 to December 31, 9999. That’s a span of nearly 10,000 years and more than enough for even the most aggressive financial forecast. You can also use the date data type to work with time data (seeing as VBA lacks a time data type).
These examples declare variables and constants as a date data type:
In VBA, place dates and times between two hash marks, as shown in the preceding examples.
Date variables display dates according to your system’s short date format, and display times according to your system’s time format (either 12- or 24-hour). The Windows Registry stores these settings and you can modify them via the Regional and Language Options dialog box in the Windows Control Panel. Therefore, the VBA-displayed date or time format may vary, depending on the settings for the system on which the application is running.
Using Assignment Statements
An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object. Excel’s Help system defines the term expression as
“. . . a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data.”
I couldn’t have said it better myself.
Much of your work in VBA involves developing (and debugging) expressions. If you know how to create formulas in Excel, you’ll have no trouble creating expressions. With a worksheet formula, Excel displays the result in a cell. A VBA expression, on the other hand, can be assigned to a variable.
Assignment statement examples
In the assignment statement examples that follow, the expressions are to the right of the equal sign:
Expressions can be as complex as you need them to be; use the line continuation character (a space followed by an underscore) to make lengthy expressions easier to read.
Often, expressions use functions: VBA’s built-in functions, Excel’s worksheet functions, or functions that you develop using VBA. I discuss functions in Chapter 9.
About that equal sign
As you can see in the preceding example, VBA uses the equal sign as its assignment operator. You’re probably accustomed to using an equal sign as a mathematical symbol for equality. Therefore, an assignment statement like the following may cause you to raise your eyebrows:
How can z be equal to itself plus 1? Answer: It can’t. In this case, the assignment statement is increasing the value of z by 1. Just remember that an assignment uses the equal sign as an operator, not a symbol of equality.
Other operators
Operators play a major role in VBA. Besides the equal sign operator (discussed in the previous section), VBA provides several other operators. Table 7-3 lists these operators, with which you are familiar from your worksheet formulas experience.
| Table 7-3 | VBA’s Operators |
| Function | Operator Symbol |
| Addition | + |
| Multiplication | * |
| Division | / |
| Subtraction | - |
| Exponentiation | A |
| String concatenation | & |
| Integer division (the result | \ |
| is always an integer) | |
| Modulo arithmetic (returns | Mod |
| the remainder of a division | |
| operation) |
The term concatenation is programmer speak for “put together.” Thus, if you concatenate strings, you are combining strings to make a new and improved string.
As shown in Table 7-4, VBA also provides a full set of logical operators. Consult the Help system for complete details.
| Table 7-4 | VBA’s Logical Operators |
| Operator | What It Does |
| Not | Performs a logical negation on an expression |
| And | Performs a logical conjunction on two expressions |
| Or | Performs a logical disjunction on two expressions |
| XoR | Performs a logical exclusion on two expressions |
| Eqv | Performs a logical equivalence on two expressions |
| Imp | Performs a logical implication on two expressions |
The precedence order for operators in VBA is exactly the same as in Excel formulas. Exponentiation has the highest precedence. Multiplication and division come next, followed by addition and subtraction. You can use parentheses to change the natural precedence order, making whatever’s sandwiched in parentheses come before any operator.
Working with Arrays
Most programming languages support arrays. An array is a group of variables that have a common name; you refer to a specific variable in the array by using the array name and an index number. For example, you may define an array of 12 string variables to hold the names of the months of the year. If you name the array Month Names, you can refer to the first element of the array as Month Names(1), the second element as Month Names(2), and so on.
Declaring arrays
Before you can use an array, you must declare it. You declare an array with a Dim or a Public statement, just as you declare a regular variable. However, you also need to specify the number of elements in the array. You do this by specifying the first index number, the keyword to, and the last index number — all inside parentheses. The following example shows how to declare an array of 100 integers:
When you declare an array, you can choose to specify only the upper index. VBA assumes that 0 is the lower index. Therefore, the following statements both declare the same 101-element array:
If you want VBA to assume that 1 is the lower index for your arrays, simply include the following statement in the Declarations section of your module:
Option Base 1
This statement forces VBA to use 1 as the first index number for arrays that declare only the upper index. If this statement is present, the following statements are identical, both declaring a 100-element array:
Multidimensional arrays
The arrays created in the previous examples are all one-dimensional arrays. Arrays you create in VBA can have as many as 60 dimensions — although you rarely need more than two or three dimensions in an array. The following example declares a 100-integer array with two dimensions:
You can think of this array as occupying a 10-by-10 matrix. To refer to a specific element in this array, you need to specify two index numbers. The following example shows how you can assign a value to an element in this array:
This statement assigns a value to a single element in the array. If you’re thinking of the array in terms of a 10-by-10 matrix, this assigns 125 to the element located in the third row and fourth column of the matrix.
You can think of a three-dimensional array as a cube. Visualizing an array of more than three dimensions is more difficult. Sorry, I haven’t yet mastered the fourth dimension and beyond.
Dynamic Arrays
You can also create dynamic arrays. A dynamic array doesn’t have a preset number of elements. Declare a dynamic array with a blank set of parentheses:
Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running. You can use the ReDim statement any number of times, changing the array’s size as often as you need. The following example demonstrates how to change the number of elements in a dynamic array. It assumes that the NumElements variable contains a value, which your code calculated.
When you redimension an array by using ReDim, you wipe out any values currently stored in the array elements. You can avoid this by using the Preserve keyword. The following example shows how you can preserve an array’s values when you redimension the array:
If MyArray has ten elements and you execute the preceding statement, the first ten elements remain intact and the array has room for additional elements (up to the number contained in the variable NumElements).
The topic of arrays comes up again in Chapter 10, when I discuss looping.
Using Labels
In early versions of BASIC, every line of code required a line number. For example, if you were writing a BASIC program in the ’70s (dressed, of course, in your bell bottoms) it may have looked something like this:
VBA permits the use of such line numbers and it even permits text labels. You don’t typically use a label for each line, but you may occasionally need to use a label. For example, insert a label if you use a GoTo statement (which I discuss in Chapter 10). A label must begin with the first nonblank character in a line and end with a colon.
The information in this chapter becomes clearer as you read subsequent chapters. If you want to find out more about VBA language elements, I refer you to the VBA Help system. You’ll find as much detail as you need, or care to know.
