Back to index

Declarations in VBA

In this example the workbook is called "VBA Sample.xlsx" and can be seen at the bottom of the tree. It has one worksheet called Sheet1. There are two code modules, one for the workbook and one for the worksheet. If you double-click on Sheet1, you will see the corresponding code window on the right side, as shown.You will notice that there is already one line of code in the window, even though this is a new workbook that was just created.
VBA Development Window

This line

Option Explicit

means that explicit declarations are required for all variables. VBA puts that line there automatically because I have selected the option, which applies to all VBA modules in all files. I highly recommend that you include this. From the menu select Toos, Options, and check the option as shown. Require Declarations

Why would you want to force VBA to require declarations when VBA works perfectly well without them?

Humans type code, and humans make mistakes. A very common mistake is to mistype a variable name in one place:


Private Sub Test()
   Set MyCell = Range("A1")
   If MyCll = "" Then
      MsgBox "Your cell is blank!"
   End If
End Sub

This code will always display the message box. MyCll is a typo. Because it is undeclared, VBA assumes it has type Variant. Because it was never given a value, when compared to a string it will be interpreted as a null string, so the comparison show here will be True.

If we had required all variables to be declared, and had written this code


Option Explicit

Dim MyCell as Range
Set MyCell = Range("A1")
If MyCll = "" Then
   MsgBox "Your cell is blank!"
End If

the code would generate a compilation error due to the undeclared variable, and we would have found our error immediately, before even running the code.