Understanding Program Variables in VBA
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 Variables
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:
Dim variableName
NOTE
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:
Dim 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 variables—totalSales and totalExpenses—and 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.
TIP
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
NOTE
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.