Home and Learn - Free Excel VBA Course
2.3 Option Explicit in Excel VBA
In order to ensure that you don't have any variables that start with the Dim keyword, you can type Option Explicit at the very top of your coding window. What this does is to instruct VBA to check your variables for you. Take the following code as an Example:
At the very top of the code we have Option Explicit. The Sub has one variable set up with the Dim keyword - MyNumber. On the second line, we place a value of 1 in this variable. On the third line, however, we have this:
MyNumbers = 2
We have accidentally typed MyNumbers instead of MyNumber. If we didn't have Option Explicit at the top then VBA would have run this code and set up MyNumbers as a new variable of type As Variant. This could have led to errors further down the code, if we didn't realise that MyNumber and MyNumbers were two different variables. Instead, VBA will now not run the code at all. We'll get this error message:
The error message is telling us that there is an undeclared variable somewhere in our code, a variable not set up with the Dim keyword. (NOTE: There are other keywords besides Dim that you can start a variable declaration with. We haven't covered these yet, though.)
So it's a good idea to type Option Explicit at the top of your code window in order to prevent variables being set up accidentally.
General Declarations
If you look again at the top of the coding window, where we had Option Explicit, you'll see two dropdown lists:
The first dropdown list says General. The second one says Declarations. But the second one only says Declarations because we have clicked the cursor at the top of the coding window. If you click inside of the Sub, the second list changes to the name of that Sub.
You can set up variables at the top of your code, in the General Declarations area. If you do, then those variables can be seen from anywhere in the code for that window. Otherwise, variables are local to whatever Sub or Function you set them up in. For example, our MyNumber variable can only be accessed from the option_explicit_test Sub. If we set up other Subs in this window then MyNumber would not be available to these new Subs. However, if we moved the line Dim MyNumber As Integer to the General Declarations area then other Subs could access the MyNumber variable.
You'll get some practice with variables set up in the General Declarations in a later lesson. In the next lesson, you'll see how to add and subtract in Excel VBA.
Next Lesson: 2.4 Math Operators >