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.
- Type Option Explicit
- Don’t press Enter.
- Type a SPACE in same line as ‘Option Explicit’ (At end of this command).
- 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.