Common VBA Mistakes

I recently taught a VBA (Visual Basic for Applications, also known as Microsoft Office Macros) course using courseware that is frequently used. Although the person who wrote the courseware generally did a fine writing job, he made a few fundamental mistakes in the sample code. None of the mistakes actually stops the code from running, but they show a misunderstanding of how VBA works. Here are the mistakes, the reason why they are wrong, and what can be done to fix them.

Declaring Multiple Variables on a Line

Here is some code similar to what was in the book:

Dim var1, var2, var3 As Integer

In this example, it is obvious that the author intended to declare three integers, but that is not what happened. In VBA, the default data type is a variant. Variables declared with the variant data type can hold any kind of data, including integers. In VBA, although you can declare multiple variables on a line, you must give a data type to each variable. This is the code as it should have been written:

Dim var1 As Integer, var2 As Integer, var3 As Integer

Instead, the code as written results in two Variants and an integer. It is the equivalent of writing this:

Dim var1 As Variant, var2 As Variant, var3 As Integer

It still works because variables of the variant data type can still hold integers, but there is a larger overhead (an extra 16 bytes of memory), and the code runs slower because internally it must resolve any variant into an integer before performing math operations on it.

In my VBA coding conventions, declaring multiple variables on a line is prohibited to avoid just this error. This is the preferred way to write this code:

Dim var1 As Integer
Dim var2 As Integer
Dim var3 As Integer

Wrapping Arguments With Parentheses

Here is some code similar to what is in the book:

MsgBox ("Hello World")

VBA has two ways to call this function that are equally valid:

MsgBox "Hello World"
Call MsgBox("Hello World")

If you use the word "Call", you must use parentheses, otherwise they are omitted. The reason why the example works is that you can surround any expression with parentheses. For example:

Debug.Print ((((7) + (((4))) - ((9)))

The extra parentheses are discarded by VBA. The mistake becomes apparent when you add a second argument. If you try to add Yes and No buttons to the message box like this:

MsgBox ("Delete the Database?", vbYesNo)

This results in a syntax error, because it would be similar to writing this:

Debug.Print (7, 4)

The expressions (7, 4) has no meaning in VBA. To fix the error you would need to write either of these:

MsgBox "Delete the Database?", vbYesNo
Call MsgBox ("Delete the Database?", vbYesNo)

Extraneous Colons

Here is some code similar to what is in the book:

Select Case var1
    Case 1:
        MsgBox "1"
    Case 2:
        MsgBox "2"
End Select

The colons after the 1 and 2 are extraneous and are not needed. The reason why it works is because VBA allows multiple statements on a line separated by colons. This is an example of that:

Dim var1 As Integer : var1 = 1

This is treated exactly the same as doing this:

Dim var1 As Integer
var1 = 1

The colon separates the multiple statements on a line. However, you could put a colon at the end of any line because the second statement could be blank. VBA will accept this code as well:

Dim var1 As Integer:
var1 = 1:

In this case there is a second statement on each line that happens to be blank.