Microsoft Office Tutorials and References
In Depth Information
Using Comments in Your Code
Table 24-1: VBA Data Types (continued)
Data Type
Bytes Used
Range of Values
14 bytes
+/–79,228,162,514,264,337,593,543,950,335 with no
decimal point; +/–7.9228162514264337593543950335 with
28 places to the right of the decimal
8 bytes
January 1, 0100 to December 31, 9999
4 bytes
Any object reference
String (variable length)
10 bytes +
string length
0 to approximately 2 billion
String (fixed length)
Length of
1 to approximately 65,400
Variant (with numbers)
16 bytes
Any numeric value up to the range of a double data type
Variant (with characters)
22 bytes +
string length
0 to approximately 2 billion
Declaring variables
Before you use a variable in a procedure, you may want to declare it. Declaring a variable tells
VBA its name and data type. Declaring variables provides two main benefits:
h 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.
h 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
Search JabSto ::

Custom Search