If you’ve ever written VBA code in Excel, Access, or any of the other products in the Microsoft Office suite, you may already be familiar with Option Explicit. If you’re not, it'll be worth a few minutes of your time to learn about it. It’s a useful tool that will save you a lot of headaches.
In order to understand the Option Explicit statement, you need to start by understanding that Visual Basic allows implicit variable declaration by default. This means that you are not required to declare your variables before you can use them. For example, here's a procedure I created in a standard module in Microsoft Access:
Sub VariableDemo()
Var1 = 10
Var2 = 25
Msgbox Var1 + Var2
End Sub
To try this yourself, create a new standard module in either Excel or Access, and type in the procedure you see here. Make sure the module you create this in does NOT have the “Option Explicit” statement at the top. If it is, take it out.
Now run the procedure. A message box will pop up with the expected result, 35.
Notice that this procedure did NOT declare Var1 or Var2 before they were used. When Visual Basic encounters a variable that has not been declared, it goes ahead and creates it in memory for you. While this may sound convenient, there are a couple of problems with this. The first problem is that Visual Basic will create a Variant - a very inefficient type of variable. If you’ve got lots of variants in your program, it’ll take up more memory than it needs to and they’ll slow the program down. The second problem is that this type of on-the-fly variable declaration won’t catch your spelling mistakes. Consider this slight change to the code from above:
Sub VariableDemo()
Var1 = 10
Var2 = 25
Msgbox Var1 + Vr2
End Sub
See the change? In the Msgbox statement, I misspelled the second variable (Vr2 instead of Var2). It’s a subtle mistake, and one that’s easy to make. If you run this code now, the Msgbox will show the result as 10. This is because the variable “Vr2” has not been assigned a value, so there is nothing to add to Var1.
This is where Option Explicit comes in. When you add this statement to your program, it informs Visual Basic that all variables must be explicitly declared before they can be used. This statement always gets added to the General Declarations section of a module, which is at the top of the module, above any sub or function procedures.
When I add this statement to my program, it ends up looking like this:
Option Explicit
Sub VariableDemo()
Var1 = 10
Var2 = 25
MsgBox Var1 + vr2
End Sub
When I run the procedure now, I get this error:
As you can see, Option Explicit is now complaining about my undeclared variable. In particular, it’s got the “Var1” highlighted. In order to fix that, I add the new variable declarations:
Option Explicit
Sub VariableDemo()
Dim Var1 As Integer
Dim Var2 As Integer
Var1 = 10
Var2 = 25
MsgBox Var1 + vr2
End Sub
But notice that I left the spelling mistake. When I try to run the program, I still get the “Variable not Defined” error, but this time the “vr2” is highlighted:
This makes it clear to me that I’ve misspelled the “Var2” variable. When I correct the spelling, the program now runs as expected, giving me the correct result, 35.
Option Explicit is a useful statement that will help to catch your spelling mistakes. You’ll want to add it to the top of each modules. Not only will it make your programs more accurate, they’ll be more efficient as well.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2017. All rights reserved.