Chapter 3: Understanding Program Variables
In this chapter
Learning why variables are useful
Declaring variables for use in your macros
Understanding the different types of variables
Learning to use array variables and constants
Using techniques that help you avoid the most common variable errors
Your VBA procedures often will need to store temporary values for use in statements and calculations that come later in the code. For example, you might want to store values for total sales and total expenses to use later in a gross margin calculation. Although you probably could get away with using the underlying application to store these values (in, say, a cell in an Excel worksheet), this almost always isn't very practical. Instead, VBA (like all programming languages) lets you store temporary values in variables. This chapter explains this important topic and shows you how to use variables in your VBA procedures.
Declaring a variable tells VBA the name of the variable you're going to use. (It also serves to specify the data type of the variable, which I'll explain later in this chapter.) You declare variables by including Dim statements (Dim is short for dimension) at the beginning of each Sub or Function procedure.
In its simplest form, a Dim statement has the following syntax:
Technically, you can put variable declarations anywhere you like within a procedure and VBA won't complain. The only real restriction is that the Dim statement must precede the first use of the variable in a procedure. Having said all that, however, it's not only traditional, but also clearer, to list all your Dim statements together at the top of a procedure.
Here, variableName is the name of the variable. You make up these names yourself, but there are a few restrictions you need to bear in mind:
The name must begin with a letter
The name can't be longer than 255 characters
The name can't be a VBA keyword (such as Dim or Sub or End)
The name can't contain a space or any of the following characters: . ! # $ % & @
For example, the following statement declares a variable named totalSales:
Most programmers set up a declaration section at the beginning of each procedure and use it to hold all their Dim statements. Then, after the variables have been declared, you can use them throughout the procedure. Listing 3.1 shows a Function procedure that declares two variablestotalSales and totalExpensesand then uses Excel's Sum function to store a range sum in each variable. Finally, the GrossMargin calculation uses each variable to return the function result.
To conserve space, you can declare multiple variables on a single line. In the GrossMargin function, for example, you could declare totalSales and totalExpenses using the following statement:
Dim totalSales, totalExpenses
Listing 3.1 A Function that Uses Variables to Store the Intermediate Values of a Calculation
Function GrossMargin() ' ' Declarations ' Dim totalSales Dim totalExpenses ' ' Code ' totalSales = Application.Sum(Range("Sales")) totalExpenses = Application.Sum(Range("Expenses")) GrossMargin = (totalSales - totalExpenses) / totalSales End Function
In the GrossMargin function, notice that you store a value in a variable with a simple assignment statement of the following form:
variableName = value
To avoid confusing variable names with the names of things that are built into the VBA language, many programmers begin their variable names with a lowercase letter. If the name contains multiple "words," then each subsequent word should use an uppercase first letter (for example, totalSales or newFileName). This is the style I use in this book.
Also, note that VBA preserves the case of your variable names throughout a procedure. For example, if you declare a variable named totalSales and you later enter this variable name as, say, totalsales, VBA will convert the name to totalSales automatically as part of its syntax checking. This means two things:
If you want to change the case used in a variable, change the first instance of the variable (usually the Dim statement).
After you've declared a variable, you should enter all subsequent references to the variable entirely in lowercase.