Excel VBA Option Explicit to force declaration of variables

This tutorial explains how to use Option Explicit to force declaration of variables in Excel VBA (enable Require Variable Declaration)

You may also want to read:

Difference between Dim and Set

Excel VBA Option Explicit to force declaration of variables

Difference between ByRef ByVal

Function, Sub, Method, Property Differences

What is VBA Option Explicit?

Option Explicit is a keyword declared on top of the Visual Basic Editor in the coding Windows outside any Procedures.

In the presence of Option Explicit keyword, all variables must be declared (such as Dim, Private, Public, ReDim, Static), otherwise an error message will prompt.

In the absence of Option Explicit keyword, if you don’t define variables, all variables are regarded as “Variant” data type, and the compiler will guess a proper data type.

Why do you need Option Explicit?

1) In the absence of Option Explicit keyword and declaration of variables, it takes extra time for compiler to guess the data type, thus the time of running Procedures become longer, especially when you need to handle a large amount of data.

2) Data type “Variant” occupies more memory than other variable types

3) Option Explicit keyword can avoid typo. For example, your variable name is called “sInput”, but you mistakenly type “s1nput”.  Mistyping variable will not prompt you an error because compiler thinks you are using another new variable. In the presence of Option Explicit keyword, compiler will prompts an error because you have never declared “s1nput”.

In my opinion, I use Option Explicit only in large project, because it is very time consuming to find out a typo out of hundreds lines of code. On the other hand, I don’t declare variables at all if I write <100 lines of code, because it is very easy to spot the mistake anyway and it doesn’t make much of a difference in terms of memory usage and speed.

To use Option Explicit

Press ALT+F11 to enter VBE, and then insert a Module. If you don’t see the keyword Option Explicit on top of the coding area, you can type the keyword directly.

option_explicit_01

Depending on your configuration, Option Explicit can be generated automatically every time you insert a Module, double click on ThisWorkBook, Worksheet.

To enable auto generation of Option Explicit, in VBE, navigate to Tools > Options > check the box “Require Variable Declaration” > reopen workbook to take effect

option_explicit_02

Now if you use a variable name which has not been declared, you will receive an error message Compile error: Variable not defined

option_explicit_03

Outbound References

https://www.youtube.com/watch?v=4-uhsCL2UHE

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *