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
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.
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
Now if you use a variable name which has not been declared, you will receive an error message Compile error: Variable not defined
