Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
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.
x = 1
InterestRate = 0.075
LoanPayoffAmount = 243089.87
DataEntered = False
x = x + 1
MyNum = YourNum * 1.25
UserName = “Bob Johnson”
DateStarted = #12/14/2009#
VBA has many reserved words, which are words that you can’t 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 descriptive. If the Auto Syntax Check option
is turned on you get the error: Compile error: Expected: variable . If Auto Syntax
Check is turned off, attempting to execute this statement results in: Compile error: Syntax
error . It would be more helpful 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.
Defining data types
VBA makes life easy for programmers because it can automatically handle all the details involved
in dealing with data. Not all programming languages make it so easy. For example, some
languages are strictly typed, which means that the programmer must explicitly define the data type
for every variable used.
Data type refers to how data is stored in memory — as integers, real numbers, strings, and so on.
Although VBA can take care of data typing automatically, it does so at a cost: slower execution
and less efficient use of memory. As a result, letting VBA handle data typing may present
problems when you’re running large or complex applications. Another advantage of explicitly
declaring your variables as a particular data type is that VBA can perform some additional error
checking at the compile stage. These errors might otherwise be difficult to locate.
Table 8-1 lists VBA’s assortment of built-in data types. (Note that you can also define custom
data types, which I describe later in this chapter in “User-Defined Data Types.”)