3 Reasons for Option Explicit (Excel VBA)

If you learnt a bit about VBA programming (check this out if you don’t yet know what VBA is), you’ve probably come across the notion of “Option Explicit” in VBA. Option Explicit is, in a way, a restriction or limitation to the coding experience in VBA. Normally you can just write sentences like “i = 10” with no issues, but with Option Explicit you will always have to do “Dim i” before actually using “i = 10“.

From the look of it, Option Explicit seems to be too restrictive – do I necessarily need to do “Dim i” before using the variable i? Here we present 3 arguments on why Option Explicit is necessary for better Excel VBA programming, and the best practice in using Option Explicit.

If you don’t yet know what VBA is…

Excel VBA is a set of automation codes that we can use to greatly improve efficiency in working with Excel. I’d recommend you to check this article out if you don’t have a good idea of what Excel VBA is! And feel free to check this article out again once you have an idea of VBA!

A lovely introduction to Excel VBA

Reason 1: It Avoids Confusions

By confusion, I mean weird little mistakes that could go undetected and cause you a great deal of time looking at what’s the error, i.e. debugging.

Let’s look at the subroutine Confusion() below, which puts 10 as the value for the variable “L1” (in lowercase), and then proceed to print the value of the variable “LL”.

Sub Confusion_WithoutOptionExplicit()
    l1 = 10
    MsgBox ll
End Sub

Without Option Explicit, unassigned variables have value “Empty”, which means you will get an empty message box by running this little piece code.

Imagine this being a much longer subroutine and you are trying to figure out why you get an empty value instead of 10. From the VBA Editor “L1” and “LL” looks virtually the same, and you will probably spend hours trying to figure out what’ wrong, before realizing that it is a simple mistake there.

Option Explicit

Sub Confusion_WithOptionExplicit()
    ' Note that with Option Explicit, we will need to declare "L1" with a Dim statement
    Dim l1
    l1 = 10
    MsgBox ll
End Sub

With Option Explicit, however, you are forced to declare variables before you use them. If you run the same code after declaring “L1”, you will get a compile error before being able to run the code, and the VBA Editor will also highlights the wrong variable name “LL” for us.

This is now easier for us to debug, especially because we only need to change the wrong variable name “LL” back to the declared “L1” variable name to get the script running.

Remember, compile error (errors which the VBA Editor will throw at you) are much easier to fix and debug than logic error (errors which the VBA code runs successfully but the result is wrong)!

You may also like: How to Activate an Excel Sheet using VBA?

Reason 2: It Keeps the Integrity of Variables

Another important use of Option Explicit is to maintain the integrity of your variables, so you don’t get unexpected value from running your codes!

Let’s look at the subroutine Integrity(), which simplifies a program that asks the user to give us a number, and we compare that number against 10.

Sub Integrity_WithoutOptionExplicit()
    userInput = "Nine Point Nine"
    
    If userInput < 10 Then
        MsgBox "Yes"
    Else
        MsgBox "No"
    End If
End Sub

Without Option Explicit, we are not required to Dim userInput, so our users can basically type in whatever they wanted to, even if it is not a number.

Our user decide to go with writing out 9.9 in English instead of Arabic Numbers. Problem is, VBA think “Nine Point Nine” is correct and go ahead to compare it against 10. As the “smaller or equal to” sign (“<“) in VBA compares the lexicographical order of text character by character, the by comparing the first character, “N” is basically larger than “1”. This gives us “Nine Point Nine” > 10, which is not something we want.

Option Explicit

Sub Integrity_WithOptionExplicit()
    Dim userInput As Double
    userInput = "Nine Point Nine"
    
    If userInput < 10 Then
        MsgBox "Yes"
    Else
        MsgBox "No"
    End If
End Sub

With Option Explicit, we are required to Dim userInput before using it, and this time we specifically tell Excel VBA that userInput needs to be a number.

When you run the subroutine again, you’ll see that it gives you a “Type Mismatch” error, and after pressing “Debug” you’ll see the row with problem highlighted. This compile error helps us easily identify what’s wrong with our codes.

You may also like: How to Check/Test if Sheets Exist in Excel?

Reason 3: It Saves Some Memory

When you deal with slightly more data with Excel VBA, memory-saving soon become very essential to improve the speed of your codes, or sometimes even dictates if your codes will run at all!

Let’s look at the subroutine Memory() to shed some light on what is the memory usage with and without Option Explicit, using the Locals window to our help.

Sub Memory_WithoutOptionExplicit()
    i = 10
    j = 10
    Stop
End Sub

Without Option Explicit, we don’t need to Dim our variables, so VBA Editor helps us default the variable type to Variant, even though we intend the variables i and j to be integers.

Variant is expensive, in the sense that it will eat up 16 bytes of our memory. A usual integer will only cost us 2 bytes, so using a Variant will basically make us spend 8 times of what we actually needed!

If you have small sets of data, the waste of memory will probably not impact you, but when you have a large dataset, you may run out of memory and that’s where every bits of memory that you can save counts!

Option Explicit

Sub Memory_WithOptionExplicit()
    Dim i As Integer, j As Integer
    i = 10
    j = 10
    Stop
End Sub

With Option Explicit, we Dim i and j to be integers, and from the Locals window we finally see that they are both Integer type instead of Variant/Integer type!

Bottomline

The benefits that Option Explicit brings about is that you have to explicitly define what your variables are. While this may sometimes seems redundant and time-consuming, it is actually a best practice especially when dealing with large chunk of VBA codes, which will help you with debugging and saving up computing resources that are scarce!