Variables Data Types and Constants

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 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 be as long as 254 characters - but using such long variable names is not recommended.

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.87 DataEntered = False x = x + l

MyNum = YourNum * l.2 5 UserName = "Bob Johnson" DateStarted = #l2/l4/2006#

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

Unfortunately, syntax error messages aren't always very descriptive. The preceding instruction generates this error message: Compile error: Syntax error. 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.

0 0

Post a comment