VBA Tip: Option Explicit

When I first started dabbling in writing my own macros I often saw the words "Option Explicit" before any code that was demonstrated on help sites.  I wondered what it was.

So what does "Option Explicit" mean?  Well, if you plan on using variables within your VBA code and inevitably you will, option explicit requires you to declare each variable.

How do I declare a variable?  The syntax for declaring a variable within VBA is:

Dim [your variable name] as [Type of variable]

An example:

Dim MyVar as Integer

So what do I get out of using Option Explicit you ask? When the code compiles prior to running, you will receive an error message if you have a variable that has not been declared.  This protects you from accidental typos of variables.

Using the example above, if I typed MVyar instead of MyVar and was not using Option Explicit -  as long as the typo is not one of the VBA reserved words, methods or properties - VBA would by default assign MVyar as a new variable.  This can make troubleshooting your code very difficult.

In conclusion, best practice states we should just set Option Explicit as a default setting.  Here's How (Using Excel 2013):

• Open an Excel Workbook > Open VBA (alt + F11)
• Tools > Options > "Editor" Tab
• Check "Require Variable Declaration"  (See picture below)