Declaring Variables

Declaring Variables

When you start using Visual Basic for Applications (VBA) code in your application, you will normally declare variables as being of one type or another, for example:

Dim strFoo1 As String
Dim lngFoo2 As Long

When you do this, there are several things to bear in mind.

Firstly, ensure that each Module has "Option Explicit" at the top. This can be set by Tools|Options|Module|Require Variable Declaration. This will ensure that any spelling mistakes that you make when typing a variable will cause an error ('Compile Error: Variable Not Defined') when you try to run the code, instead of allocating the value to this misspelt variable name instead of the correct name:

Sub sTest()
    Dim lngFoo As Long
    lngFoo2 = 1
    MsgBox lngFoo
End Sub

This will result in 0 being output, rather than the 1 that was expected. Obviously, this is a very basic example, but when you have a large amount of code in one procedure, it can be difficult to track down. Note that when you set Option Explicit to appear in each module, it will not add it to those modules that you have already created, so you will need to add them in yourself.
Always declare variables as being of the most efficient datatype, but be aware of the implications of doing this. For example, if you declare a variable to be of type Integer, that means that the permissible values are whole numbers between -32768 and 32767 inclusive. Any number that is supplied outside this range will cause an error (in this case Error Number 6, Overflow).
Always declare variables as being of a type. For example:

Dim strOne, strTwo As String

Is not the same as:

Dim strOne As String, strTwo As String

In the first example, strOne is actually created as being a Variant rather than the expected String.
Be aware of the differences between Strings and Variants:
- Strings do not accept Null values (a value that is not known), and if you attempt to store a Null value in a String variable then you will get an error (Error Number94, 'Invalid Use of Null')
- Strings take up less overhead than a Variant, so if you can guarantee that there will be no Null values then using a String is a better idea.
- If you use a String variable, then be aware that there are various string handling functions, such as Left, that are faster than their counterparts, such as Left$, due to the fact that the latter need to be able to cope with Variants.

Top

Last modified at 06/06/2006 14:57:19