Declaring Variables

     

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.

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.

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 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 

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 

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 

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.

In the GrossMargin function, notice that you store a value in a variable with a simple assignment statement of the following form:

  variableName = value