VBA's main purpose in life is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables that you create.
A variable is simply a named storage location in your computer's memory. Variables can accommodate a wide variety of data types — from simple Boolean values (True or False) to large, double-precision values (see the following section).
You assign a value to a variable by using the equal sign operator (more about this in the upcoming section, "Assignment Statements").
You'll make your life easier if you get into the habit of making your variable names as descriptive as possible. VBA does, however, have a few rules regarding variable names:
♦ You can use alphabetic characters, numbers, and some punctuation characters, but the first character must be alphabetic.
♦ VBA does not distinguish between case. To make variable names more readable, programmers often use mixed case (for example, InterestRate rather than interestrate).
♦ You cannot use spaces or periods. To make variable names more readable, programmers often use the underscore character (Interest_Rate).
♦ Special type declaration characters (#, $, %, &, or !) cannot be embedded in a variable name.
♦ Variable names can comprise as many as 254 characters — but no one in his right mind would create a variable name that long!
The following list contains some examples of assignment expressions that use various types of variables. The variable names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the variable on the left.
InterestRate = 0.075 LoanPayoffAmount = 243089 DataEntered = False x = x + 1
MyNum = YourNum * 1.25 UserName = "Bob Johnson" DateStarted = #3/14/98#
VBA has many reserved words, which are words that you cannot use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next might make a very descriptive variable name, the following instruction generates a syntax error:
Next = 132
Unfortunately, syntax error messages aren't always very descriptive. The preceding instruction generates this error message: Compile error: Expected variable. It would be nice if the error message were something like Reserved word used as a variable. So if an instruction produces a strange error message, check the VBA Help system to ensure that your variable name doesn't have a special use in VBA.
Was this article helpful?