Microsoft Office Tutorials and References
In Depth Information
it Count , and the second time you type in COUNT , the second entry will be changed to Count
by the editor.
You should use descriptive variable names — that is, names that describe the data the variable holds.
For instance, a variable that holds an interest rate might be called IntRate or InterestRate .
You could call it MickeyMouse or Q99 and VBA would not care, but descriptive names make
your code a lot easier to read and debug.
It is common practice, although not required, to use a combination of upper- and lowercase letters
for variable names as shown in these examples:
All variables should be declared before being used for the first time. This tells VBA about the
variable, specifically its name and the kind of data it will hold. A variable declaration takes this form:
Dim varname As type
varname is the name of the variable, and type is the name of the variable’s data type (as explained
in the following sections). You can place multiple declarations on the same line:
Dim varname1 As type1, varname2 As type2, varname3 As type3
I say that variables should be declared, not that they must be declared. VBA gives you the option of
not requiring variable declaration — you simply use new variable names as needed in your code.
This may sound like a good idea, but in fact it is not. When variable declaration is required,
misspelling a variable name results in an error message when you run the program, because the (mis-
spelled) variable has not been declared, and the misspelling can be easily corrected. If variable
declaration is not required, misspellings go undetected and can cause lots of problems. To make
sure variable declaration is required, select Tools, Options in the VBA Editor and select the Require
Variable Declaration option on the Editor tab. You can obtain the same effect by placing the
Option Explicit statement at the start of every VBA module.
Numeric variables hold number data. VBA has six different numeric variable types that differ in
whether they hold integer data (numbers without a decimal part such as 2, –145, and 32,190) or
floating-point data (numbers with a decimal part such as –1.143, 0.0045, and 123,900.5). These
six numeric variable types are summarized in Table 23.1.