Declaring Variables in VBA: Three Keys for Success

A recent post in an Excel forum included some VBA code and asked why the code kept failing. The first few lines of code looked something like this:

Dim Count
Dim List
Dim Address

I didn’t study his code, because the way he declared his variables made his code hard to understand. In fact, his problem probably was caused by the way he handled his variables.

So let’s see what he should have done…

Use Option Explicit

By default, Excel doesn’t force you to declare your variables. However, you should change that setting immediately.

In any version of Excel, press Alt+F11 to launch the Visual Basic Editor (VBE). Choose Tools, Options. In the Editor tab of the Options dialog, make sure that there’s a check mark in front of “Require Variable Declaration.”

After you choose OK, insert a new Module. When you do so, you’ll see Option Explicit as the first line of the module. This line tells Excel that all variables must be declared.

Specify the Variable Type

To declare a variable, type Dim, the variable name, and the variable type…somewhat like this…

Sub Foo()
 Dim Count As Integer
 Dim List As Range
 Dim Address As String

End Sub

…or this…

Sub Foo()
 Dim Count as Integer, List As Range, Address As String

End Sub

If you don’t specify the type of variable, as shown in the first example at the top of this post, VBA declares the variable as a Variant type. A Variant can accept any type of variable.

In rare instances you’ll have a good reason for using a Variant. But about 95% of the time, you should use explicit variable types. One reason for doing so is that your code will run faster. But the primary reason is that you’ll reduce your coding errors, for at least two reasons.

The first reason you’ll reduce coding errors is that Excel enforces the type of variable you specify. To illustrate, if you run this macro…

Sub Foo()
 Dim Count as Integer, List As Range, Address As String

 Count="Smith"
End Sub

…Excel launches an error dialog, because an Integer data type can’t contain text. (However, Excel will accept a statement like…

Address=99

…because “99” can be expressed as text.)

The second reason that it’s always a good idea to declare your variable types explicitly is that it helps to document your intentions when you write code. This documentation is critical if you look at your code after several weeks have passed. Knowing whether a variable was intended to contain a number or text can make it much easier for you to read your old code, find errors, and then continue coding.

For this reason, when you do need to declare a variable as Variant, do it explicitly, like this:

Dim MyVariable as Variant

By always declaring your Variants explicitly, you’ll always know that you intended to use a Variant, rather than doing so as an oversight.

Use Hungarian Notation

Charles Simonyi, a Hungarian programmer at Xerox PARC in the 1970s, who later became Chief Architect at Microsoft, proposed that variable names use a prefix that tells something about the variable. According to Wikipedia, there are two versions of what has become known as Hungarian Notation.

The version that is the easiest to use, and that I find the most useful, is to use  the prefix to specify the data type of the variable. Here, for example, I use “n” to specify an integer, “rng” to specify a range, and “s” to specify a string.

Dim nCount As Integer
Dim rngList As Range
Dim sAddress As String

Many programmers use “int” or “i” for integer and “str” for string. That’s fine. As long as your notation is consistent and easy for others to understand, it doesn’t matter what notation you use.