Option Explicit

One of the easiest ways to improve your VBA code is to include Option Explicit as the first line of each module within a VBA Project. Using Option Explicit forces all variables to be declared in some manner before they can be used. This might not sound important now since most of your code will work just fine without declaring all variables.

However, when you don’t declare all variables your code can quickly become messy and hard to follow. It can lead to tricky coding bugs and code that behaves in ways you don’t expect. Finally, your code will be slower and take up more space!

Forgetting Option Explicit Causes Errors

To understand how using Option Explicit can prevent errors let’s take a look at the example below. Try adding a new (empty) module to a VBA Project and pasting the code for the subroutine into it. When you click on compile you won’t get any errors.

Public Sub myBuggyAdd()
    variableA = 5
    variableB = 6
    variableC = varaibleA + variableB
    Debug.Print " --- Option Explicit Demo ---"
    Debug.Print " variableA = " & variableA
    Debug.Print " variableB = " & variableB
    Debug.Print " variableC = " & variableC
    Debug.Print " --- End of Explicit Demo ---"
End Sub

When you run the subroutine you might expect the Immediate Window to show variableC has a value of 11. However, instead it has a value of 6. The question is why …

To answer this create a new module then copy and paste the following code into that module.

Option Explicit
Public Sub myBetterAdd()
    Dim variableA As Integer
    Dim variableB As Integer
    Dim variableC As Integer
    variableA = 5
    variableB = 6
    variableC = varaibleA + variableB
    Debug.Print " --- Option Explicit Demo ---"
    Debug.Print " variableA = " & variableA
    Debug.Print " variableB = " & variableB
    Debug.Print " variableC = " & variableC
    Debug.Print " --- End of Explicit Demo ---"
End Sub

This time when we try compiling the code you should get an error on line 10. The error occurs since variableA is undefined.

If we don’t use Option Explicit, a new variable is created each time the compiler comes across something it thinks might be the name of a variable. The new variable is given a Variant data type and empty value (here 0).

In the first piece of code there was a spelling mistake on line 4. Instead of flagging this, the compiler assumed it was a new variable and so created one. In the second piece of code we are using Option Explicit. The compiler forces each variable to be declared and flags an error when it comes across the spelling mistake. The code below is the final corrected version

Option Explicit
Public Sub myBetterAdd()
    Dim variableA As Integer
    Dim variableB As Integer
    Dim variableC As Integer
    variableA = 5
    variableB = 6
    variableC = variableA + variableB
    Debug.Print " --- Option Explicit Demo ---"
    Debug.Print " variableA = " & variableA
    Debug.Print " variableB = " & variableB
    Debug.Print " variableC = " & variableC
    Debug.Print " --- End of Explicit Demo ---"
End Sub

Variants are Slow and Unpredictable

When we don’t use Option Explicit the compiler will create a new Variant variable each time it encounters something it thinks should be a variable. This may make your life easier at first (you don’t have to define everything) but it slows down your code and makes it unpredictable. Variants take up more space than other data types. They slow down calculations since the compiler has to do more “thinking” before it can perform them. That being said, on modern computers this difference in performance is hardly noticeable. In fact, if you really want performance then you probably shouldn’t be using VBA anyway.

The main reason for avoiding Variants (in all but a few specific cases) is to avoid unpredictable code and errors. There may be cases where you expect your code to done one thing … but it does something else. We had an example of this above. Essentially, consider the expression A = B + C. If both B and C are strings (i.e., both equal to “1”) then A finishes with value “11”. This might be what we wanted but then again it might not. Since we didn’t declare A as a numerical data type VBA assumes it is a string and behaves accordingly.

Option Explicit Makes Life Easier

When we use Option Explicit and compile frequently we enable auto-complete for newly created variables. It sounds like a small thing but I use it all the time to speed up my coding within the VBE and to make sure I don’t accidentally mis-type a variable name.

Including Option Explicit by Default

To include it by default, from the VBE select Tools -> Options -> Editor Tab and the check the box for “Require Variable Declaration” (see below). Click on “OK” and any new modules you create will automatically contain the statement as the very first line.

Anyway, I hope this post has convinced you to use Option Explicit as the first line in all modules.

Original Version of this Post

This post was originally published in November 2016 on The Art of Quant Finance¹www.theartofquantfinance.com. The old website is no longer online. I have re-written the post and re-posted here.

Notes

• 1
  www.theartofquantfinance.com