Option Explicit
at the top of the code module(s). In this long post I'll try to explain why there should be such a line, and how you can have it added automatically to new modules.
The line 'Option Explicit' specifies that you must explicitly declare all variables that you use within the module.
If you don't have this line at the top a module, you can create variables "on the fly" by simply mentioning them in your code. This may seem handy in the beginning, but it is dangerous, because a small typo means that you create a new variable and you won't be warned about this.
An Example
Look at this simple macro:
Code: Select all
Sub ExampleCode()
intVariable1 = 5
intVariable2 = 6
intSum = intVariable1 + intVariablr2
MsgBox intSum
End Sub
If we don't have 'Option Explicit' at the top of the module, VBA will create variables intVariable1, intVariable2, intSum and intVariablr2 for us.
intVariable1 is assigned the value 5, and intVariable2 is assigned the value 6, but since we don't assign a value to intVariablr2 in our code, it has the default value 0. Therefore intSum is assigned the value 5 + 0 = 5, instead of 5 + 6 = 11.
In a procedure that processes financial information, that could have serious consequences!
Let's see what happens in our sample macro when we add the line Option Explicit at the top of the module:
Code: Select all
Option Explicit
Sub ExampleCode()
intVariable1 = 5
intVariable2 = 6
intSum = intVariable1 + intVariablr2
MsgBox intSum
End Sub
Code: Select all
Option Explicit
Sub ExampleCode()
Dim intVariable1 As Integer
Dim intVariable2 As Integer
Dim intSum As Integer
intVariable1 = 5
intVariable2 = 6
intSum = intVariable1 + intVariablr2
MsgBox intSum
End Sub
Having 'Option Explicit' doesn't guarantee that you will write correct, meaningful or effective code, but it does prevent a lot of mistakes.
You may find the 'Variable not defined' warnings irritating initially, but in the long run, you'll save time because you won't have to hunt for typos.
How to add 'Option Explicit' automatically
You can tell the Visual Basic Editor to add the line 'Option Explicit' automatically to each new module that you create:
- In the Visual Basic Editor, select Tools | Options...
- In the Editor tab, tick the check box 'Require Variable Declaration'.
- Click OK.
The setting will apply to all Office applications.
'Require Variable Declaration' will only add 'Option Explicit' to newly created modules, not to modules that already existed before you turned on 'Require Variable Declaration'. You can add the option manually to an existing module by typing it at the top of the module, as we did in the above example.
It is advisable to select Debug | Compile <project name> after inserting 'Option Explicit' to an existing module. The Visual Basic Editor will highlight the first undefined Variable (if any), giving you the chance to add the required declaration or to correct the code if necessary. Repeat until you get no more error messages.
