Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
The following procedure uses six local variables declared by using Dim statements:
Dim x As Integer
Dim First As Long
Dim InterestRate As Single
Dim TodaysDate As Date
Dim UserName As String
‘ - [The procedure’s code goes here] -
Notice that the last Dim statement in the preceding example doesn’t declare a data type; it
simply names the variable. As a result, that variable becomes a variant.
You also can declare several variables with a single Dim statement. For example:
Dim x As Integer, y As Integer, z As Integer
Dim First As Long, Last As Double
Unlike some languages, VBA doesn’t let you declare a group of variables to be a
particular data type by separating the variables with commas. For example, the following
statement, although valid, does not declare all the variables as integers:
Dim i, j, k As Integer
In VBA, only k is declared to be an integer; the other variables are declared variants. To
declare i , j , and k as integers, use this statement:
Dim i As Integer, j As Integer, k As Integer
If a variable is declared with a local scope, other procedures in the same module can use the
same variable name, but each instance of the variable is unique to its own procedure.
In general, local variables are the most efficient because VBA frees up the memory that they use
when the procedure ends.
Sometimes, you want a variable to be available to all procedures in a module. If so, just declare
the variable before the module’s first procedure (outside of any procedures or functions).