Understanding Program Variables in VBA
This chapter is from the book
This chapter is from the book
This chapter is from the book
Your VBA procedures often 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 special memory locations called 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.) Note that at this point you're not assigning a value to the variable. That comes later. All you're doing now is telling VBA that the variable exists. 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
Here, variableName is the name of the variable. You make up these names yourself, but you need to bear a few restrictions 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.
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
|
→ |
Listing 3.1 gets its values from the Excel worksheet by using the Range method. For the details, see "Using the Range Method," p. 153. |