Why variables should be declared properly
Article contributed by Dave Rado
Almost all variables should be dimensioned as whatever they are (Dim MyRange As Range, Dim MyString As String, etc.), for several reasons:
|
1. |
Otherwise you can sometimes get unexpected results |
|
2. |
Your code will run much faster and use less memory |
|
3. |
You'll then have access to intellisense – for instance, if you type MyRange, and then type a dot, a list of all properties and methods supported by the range object will pop up – provided that MyRange was Dim'd as a Range. You can then cursor to the one you want and press Return (or select it with the mouse) to insert it. |
|
4. |
It makes debugging far easier, because the VBA Editor will then be able to “spot” errors it would otherwise miss. |
To force yourself to explicitly dimension all variables, your code window should have the line “Option Explicit” at the top of it. Select Tools + Options; and tick the “Require variable declaration” checkbox. This will have the effect of inserting “Option Explicit” at the top of all new modules you create. Doing this will give you all of the above benefits plus one more:
|
5. |
It makes debugging far easier in another way, because if you accidentally misspell a variable name somewhere, this will be picked up as an undeclared variable if you compile your project (Debug menu). |
Important note: In order to explicityly declare variables, each one must be declared separately. For example, the statement:
Dim Str1, Str2 As String
is declaring all except the final one as a Variant, not as a string! You must declare each variable explicitly, as in:
Dim Str1 As String, Str2 As String
Or:
Dim Str1As
String
Dim Str1 As String