Declaring Your Variables
VBA is a lazy programmers dream. You can take your variable name and assign practically any data to it. But it is a good habit to declare your variables which means indicating what type of data will be contained in them. This will help by taking less memory and speeding up execution time. But the most compelling reason is that it helps tremendously with one of the most common problems in programming, typos. If you declare your variable name, then misspell it later on, you’ll get an error before your program will even run rather than having the code execute with a zero or some other random value in a variable that should hold something else . This makes debugging much easier.
Like a lot of things, sometimes the best way to encourage a good habit is to force yourself to do it. You can force yourself to declare your variables by going to Tools>Options>Editor Tab in the VBE and checking the box for "Require Variable Declaration”. This adds two words to the top of every code module, "Option Explicit”. Ok, so I suppose now you’re going to want to know how to declare variables since you’ve have no choice!
Scope
First, a little discussion about variable "Scope”. The scope of a variable determines which code modules or procedures the variable’s contents will be accessible to. There are essentially three different variable scopes and they are summarized in Table 2:
Table 2: Variable Scope
Scope |
How Variable is Declared |
Single procedure |
Dim statement within the procedure (Sub or Function) |
Single Module |
Dim or Private statement before the first procedure in a module |
All Modules |
Public statement before the first procedure in a module |
The best way to help you understand is with a simple little example. This code could stand on its own in a Module:
Listing 7: Variable scope example
Option Explicit Const PI As Single = 3.14159265359 'Constants work for all Modules Dim strFile As String 'This Module only Public lngCount As Long 'All Modules Sub Test() 'Variable Declaration - Single procedure, ie. the values are only 'accessible within the "Test" subroutine. 'This is the most common way to declare your variables Dim blnTrueFalse As Boolean Dim intX, intY As Integer Dim sngCalc As Single Dim strName As String Dim varUnknown As Variant Dim varDefault 'A Variant by default 'Begin rest of your code below 'Finished, now exit the Sub End Sub
Info: If you omit the “as VariableType” portion of the declaration statement, VBA implicitly assigns it as a data type Variant as seein near the bottom of Listing 7 above.
Very nice. I have avoided vba and macros up to this point but I think I’ll give it a try.