Option Explicit set at Application Level by Excel Add-in?

[inews]

I am getting the errors normally associated with using undimensioned
variables in an Option Explicit module. But I haven't set Option
Explicit on that module nor anywhere else in my project (an Excel 2K3
workbook with some VBA routines).

If I dim the variables, the problem goes away.

So, I can work with it, but the problem is contrary to my understanding
of "Option Explicit".

My understanding is that "Option Explicit" only operates at module
level (says so in the Help file), but this I haven't done.

***Is it possible for a third party addin to somehow set "Option
Explicit" at the application level?

The reason I ask is that the only machine out of 11 which has the
problem is one which has a third party add-in installed. Some of the
add-in code is exposed and uses "Option Explicit", though, as expected,
on a per-module basis. Perhaps there is some code for the add-in that
I can't see.

***More generally, how can "Option Explicit" type dimensioning
requirements come to be in my module without my having set them?

 

[NickHK]

Yes, Option Explicit is module level. However, I have never heard of a good
reason not to use it.

There is the setting Tools>Options>Editor>Require Variable Declaration,
which will add "Option Explicit" to all subsequently created modules, but
not existing ones.

NickHK

 

[inews]

Thanks Nick. Yep, it's not caused by that option as it isn't selected.

6 lines of code, 2 variables, acceptable time for completion. Simple
stuff. No burning need to dim those, other than this weird behaviour.
Also it's just slopping around with some test code before using it in
the real thing (it gets dimmed there), and don't want to have to dim
every variable as I introduce or delete it as it takes longer to finish
playing about with it.

You're right though, the need to not use Option Explicit is relatively
marginal.

Am mostly looking to solve the mystery or to discover if my Excel
installation is doing things that it shouldn't do.

 

[NickHK]

Well, if you do not have Option Explicit at the top of that module, I cannot
see how you would get :
"Compile Error
Variable not defined"

Any undefined variable would take its default value depending on its data
type.

NickHK

 

[Jon Peltier]

"don't want to have to dim every variable as I introduce or delete it as it
takes longer to finish playing about with it"

And you don't find that it's too easy to mistype a variable name and get
unexpected results, if the lack of Option Explicit allows you to use an
undeclared variable?

For example, this sub will produce a message box with a value of zero rather
than ten, and it may take minutes to notice the misspelling.

Sub TestRange
Set MyRange = ActiveSheet.Range("A1:A10")
MsgBox MyRnage.Rows.Count
End Sub

This type of problem is eliminated if Option Explicit is used, as it will
detect the misspelled variable when the sub is compiled or first run.

- Jon

 

[inews]

I understand your point Jon and it makes sense.

Horses for courses I guess. I can type 60WPM @ 99% accuracy. I type
code slower than that and rarely mistype things. I find
experimentation easier without Option Explicit. That's my experience.
Who knows, maybe I'm fooling myself... doesn't really matter.

Any ideas on why I might be getting those errors when not using Option
Explicit? It still seems mighty peculiar.

 

[Jon Peltier]

Any ideas on why I might be getting those errors when not using Option

Explicit? It still seems mighty peculiar.

I have no suggestion for this, sorry.

- Jon

 

[inews]

Thanks for your help Jon.

Anybody else?

Any ideas on why I might be getting those errors when not using Option
Explicit? It still seems mighty peculiar.


I have no suggestion for this, sorry.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

 

[inews]

The mystery appears to be solved.

I went hunting for anything that looked out of place.

The Analysis Toolpack reference was "missing". Ticked ATP VBA addin in
Excel. Restart. No more errors in modules without "Option Explicit".
So, it WAS being caused by an addin (sorta).

No idea why it was throwing the undefined variable error instead of the
"can't find project or library" error. Any ideas why? Anyone else see
a missing reference manifest this way?

 

[NickHK]

Surprising cause. I would have expected the normal error on some standard
VBA function, but ...

NickHK

 

[Jon Peltier]

Missing references can have unexpected symptoms. Often what happens is that
the string manipulation functions, like Left(), Right(), Mid(), etc., will
seem to be broken, and you will get compile errors, unless you include a
prefix: Strings.Left(). This is a core VBA library, and it's not broken. But
it's like a canary keeled over in a coal mine, letting you know to check
other references.

- Jon