MAKING EXCEL WORK FOR YOU
What does it mean?
"Option Explicit" is a statement at the top of a code module (which includes forms,modules,classes, etc.) This statement forces you to declare all variables that you have used in that module, using Dim or similar. If you try to run your code when a variable hasn't been declared, it will be highlighted, and you will get a clear error: "Variable not defined"
Why use it?
Generally you want to prevent errors, but that is exactly what this statement does. It tells you about problems that are hard to spot without it like typos in your variable names or not declaring variables. the more code you have the harder it gets to find mistakes like this - generally all you know is that the code is not working properly, but you can't see why. Instead of spending lots of your time trying to work it out, simply placing Option Explicit at the top of the code module will tell you what and where the problem is. Once highlighted then all you need to do is correct the variable name or declare the variable if it missing.
Automatically add Option Explicit to your code?
You can set the VB Editor to automatically add this statement to all new code modules that you create. You will need to add it manually to existing files.
To have it added to all new files you create, simply open the VB Editor and select Tools -> Options, and tick the "Require Variable Declaration" box.
© Roy Cox 2005 to 2013
Option Explicit: what is it and why you should use it
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel UserInterfaceOnly <New>|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|