Microsoft Office Tutorials and References
In Depth Information
Before you use a variable in a procedure, you may want to declare it. Declaring a variable tells VBA its name
and data type, providing two main benefits:
Your procedures run faster and use memory more efficiently. The default data type — Variant — causes
VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA
knows the data type for a variable, it does not have to investigate; it can reserve just enough memory to
store the data.
If you use an Option Explicit statement at the top of your module, you avoid problems resulting from
misspelled variable names. Suppose that you use an undeclared variable named CurrentRate. At some
point in your procedure, however, you insert the statement CurrentRate = .075. This misspelled variable
name, which is very difficult to spot, will likely cause your function to return an incorrect result. See the
nearby sidebar, “Forcing yourself to declare all variables.”
You declare a variable by using the Dim keyword. For example, the following statement declares a variable
named Count to be a Long:
Dim Count As Long
You also can declare several variables with a single Dim statement. For example:
Dim x As Long, y As Long, z As Long
Dim First As Long, Last As Double
Unlike some languages, VBA does not permit you to declare a group of variables to be
a particular data type by separating the variables with commas. For example, the fol-
lowing statement — although valid — does not declare all the variables as As Long:
Dim i, j, k As Long
In the preceding statement, only k is declared to be an integer. To declare all variables as As Long, use
this statement:
Dim i As Long, j As Long, k As Long
If you don't declare the data type for a variable that you use, VBA uses the default data type — Variant. Data
stored as a variant acts like a chameleon: It changes type depending on what you do with it. The following pro-
cedure demonstrates how a variable can assume different data types:
Function VARIANT_DEMO()
MyVar = “123”
MyVar = MyVar / 2
MyVar = “Answer: “ & MyVar
VARIANT_DEMO = MyVar
Search JabSto ::




Custom Search