VBA Programming Series...20

Using Variables, Constants, and Data Types

VBA’s main purpose is to manipulate data. VBA stores the data in your comput-er’s memory; it may or may not end up on disk. Some data, such as worksheet ranges, resides in objects. Other data is stored in variables that you create.

Understanding variables

A variable is simply a named storage location in your computer’s memory. You have lots of flexibility in naming your variables, so make the variable names as descriptive as possible. You assign a value to a variable by using the equal sign operator. (More about this later in the “Using Assignment Statements” section.)

The variable names in these examples appear on both the left and right sides of the equal signs. Note that the last example uses two variables.

x = 1

InterestRate = 0.075

LoanPayoffAmount = 243089

DataEntered = False

x = x + 1

UserName = “Bob Johnson”

DateStarted = #3/14/2010#

MyNum = YourNum * 1.25

VBA enforces a few rules regarding variable names:

✓ You can use letters, numbers, and some punctuation characters, but the first character must be a letter.

✓ You cannot use any spaces or periods in a variable name.

✓ VBA does not distinguish between uppercase and lowercase letters.

✓ You cannot use the following characters in a variable name: #, $, %, &, or !.

✓ Variable names can be no longer than 255 characters. Of course, you’re only asking for trouble if you use variable names 255 characters long.

To make variable names more readable, programmers often use mixed case (for example, InterestRate) or the underscore character (interest_rate).

VBA has many reserved words that you can’t use for variable names or pro-cedure names. These include words such as Sub, Dim, With, End, Next, and For. If you attempt to use one of these words as a variable, you may get a compile error (which means your code won’t run). So, if an assignment state-ment produces an error message, double-check and make sure that the vari-able name isn’t a reserved word.

VBA does allow you to create variables with names that match names in Excel’s object model, such as Workbook and Range. But, obviously, using names like that just increases the possibility of getting confused. So resist the urge to use a variable named Workbook, and use something like MyWorkbook instead.