What is Option Explicit for? From time to time I thought I would post answers to the most common questions I get asked during the classroom based courses that we run. We will also do this for the online Excel courses and also feel free to ask questions on the blog if you have them

So the topic for this post is one of the many settings in the VBA Editor that should be turned on. Please note, is not by default. The “Require Variable Declaration” setting when turned on requires you to explicitly declare all variables.

The advantage of this is twofold:

• Declaring variables is just simply good practice.
• It can often cut hours of debugging out of an application because you misspelled a variable name.

So what is the effect of turning this setting on? Well let’s have a look. If you add the following into a module in the VBA editor:

Sub Demo()

Dim ItemCost As Currency

Dim Quantity As Integer

Dim Discount As Single

Dim TotalCost As Currency

    ItemCost = 10#  '10 dollars per item

    Quantity = 5    '5 items

    Discount = 20   '20% discount

    TotalCost = (ItemCost * Quantity) * ((100 - Discount) / 100)

    MsgBox "The total cost is: " & TotalCost, vbOKOnly, "Demo"

End Sub

If we run this macro it works fine, we get the following:

But what if we change one of the variable so it is misspelled? Change the calculation to the following:

'NOTE: Disscount with a double s

TotalCost = (ItemCost * Quantity) * ((100 - Disscount) / 100)

Now when you run your macro you don’t get an error but you get the output of:

When you change your settings in the VBA Editor to require variable declaration then the editor will place the keywords ‘Option Explicit’ at the top of each of the code windows automatically for you. It will only do this for new code windows so if you have already created some you will need to add this manually. Add the following to the top of your code window above your sub:

Option Explicit

Your module should now look like this:

Option Explicit

Sub Demo()

Dim ItemCost As Currency

Dim Quantity As Integer

Dim Discount As Single

Dim TotalCost As Currency

    ItemCost = 10#  '10 dollars per item

    Quantity = 5    '5 items

    Discount = 20   '20% discount

    'NOTE: Disscount with a double s

    TotalCost = (ItemCost * Quantity) * ((100 - Disscount) / 100)

    MsgBox "The total cost is: " & TotalCost, vbOKOnly, "Demo"

End Sub

Now when you run your code you get the following:

You are being told that you have not declared a variable and the editor points out where the problem is in your code. As I said earlier this can save you hours of searching through your code trying to find the problem. So go to the Tools menu in the VBA editor and select Options… and tick Require Variable Declarations. If you add a new module now you will see Option Explicit added by default.