MS-Excel / General Formatting

---

Declaring Variables

Declaring a variable tells VBA the name of the variable you are going to use. (It also serves to specify the data type of the variable.) 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.

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

Remember

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). (Programming types call it camel style, thanks to the "humps" created by the uppercase letters.)

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 have declared a variable, you should enter all subsequent references to the variable entirely in lowercase. Not only is this easier to type, but you'll immediately know whether you've misspelled the variable name if you see that VBA doesn't change the case of the variable name after you enter the line.

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

The code for the examples used in this tutorial can be found on my website at
www.mcfedries.com/Office2007VBA.

Listing 1.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"))
    MonthMark = (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 conserve space, you can declare multiple variables on a single line. In the MonthMark function, for example, you could declare totalSales and totalExpenses using the following statement:

Dim totalSales, totalExpenses

Listing above gets its values from the Excel worksheet by using the Range method.