Excel VBA Option Explicit

Basic Description

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:

option explicit

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 realize that MyNumber and MyNumbers were two different variables. Instead, VBA will now not run the code at all. We’ll get an 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.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment