Option Explicit – Not Working – Fix

Yes. It is very strange that my client reported that Excel app I delivered gave “Variable not defined” error.

But, when  I opened by VB/VBA project -> Debug -> Compile Project. There was no error. It was all working fine when I run the code.

Then how to find the undeclared variable?

Read this solution carefully. It is the strangest ever solution you will ever hear.

Follow these steps:

After typing Option Explicit, type a space & then Press Enter.

  1. Type Option Explicit
  2. Don’t press Enter.
  3. Type a SPACE in same line as ‘Option Explicit’ (At end of this command).
  4. Then press Enter.

That’s it. You have just fixed the issue. Now compile the project again. Shocked – just like me!!!

It reports the undeclared variables one by one.

I was not able to find any root cause for this issue. But a SPACE + Enter after the command ‘Option Explicit’ is all what is required to make it work.

For Beginners – Option Explicit

For those who are just starting with VBA or VB6.

This Option Explicit is a compiler directive command.  This instructs the Compiler/Interpreter to throw error if any of the variable is used without declaring what is its data type.

It is a way to avoid typo or spelling errors in the variables used. For example, we may be using a variable as Var0 and when we use it some where we might have typed it as VarO (Zero & Oh) .,

We will end up in getting incorrect results & hours of time in fixing this bug. To avoid such hiccups, this command is a handy one.

Leave a Reply