Excel - Require Variable Declaration - How to set this option in code? 2

[vladk]

Hi, I would like to check this setting, store the value, set to 'require' if needed and then set it to initial value - all in VBA.

Thank you!

vladk

 

[SkipVought]

in the VB Editor

Tools > Options Editor TAB -- REQUIRE VARIABLE DECLARATION

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vladk]

Hi Skip,

Thank you for your response! I actually would like to do it VBA (programmaticaly), you might know this way - line by line write something that would do something!

:0)

vladk

 

[fumei]

WHY?????? Option Explicit should always be set to true. Why would you want it not to be?

I do not know how to access the Editor options themselves programmatically.

 

[vladk]

fumei, yes, it should, but life is full of surprises

 

[SkipVought]

This is YOUR programming environment. Why would it change unless YOU changed it? If life is full of surprises, then it's YOUR FAULT, in this instance!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Yes, it is not like anyone, or any thing, else is going to change it. It is one-time thing. It is set in the environment of that mnachine.

"set to 'require' if needed "

It is always needed.

Let me see if I understand...

I check to see if I have MY environment has Option Explicit.

I see that I do not.

I store that True/False, and change it.

After.....WHAT happens???....I change it back.

Ummmmmmmmm, that seems silly. But hey, enlghten me. Give an example of any situation where this would either useful or needed.

 

[SkipVought]

The devil made me do it!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Nah, that is impossible.

 

[vladk]

fumei and Skip,

Thank you again for the answers. First of all, I should say that occasionally developers write code not only for their own programming environment but for others too. In my case, I write code that writes another code in the workbooks created on fly. This would happen not on my machine, but on some other machines. It is accepted to call them "clients" machines. You might heard of them. Sometimes they have Option Explicit sometimes NOT. And what is even more interesting, fumei, the NOT is a default setting.

When a new code lines inserted (programmatically), it might be important to know what option is selected in clients(!) Excel.

There are at least two ways to handle this. One is controlling this option programmatically, another is reading the first lines and building the code accordingly.

I chose second one however still curious regarding the first one...

Guys, Skip, I am actually gratefull - you helped me so many times in the past. Can you find the answer this time? I don't actually need it anymore since I found a way around, but I am still curious.

:0)

vladk

 

[combo]

You can read/write registry settings with API, key "RequireDeclaration", values 0 (for no) and 1 (require).
Path in 2003: KEY_CURRENT_USER\Software\Microsoft\VBA\6.0\Common

combo

 

[vladk]

combo,

Thank you very much! This is a good way to handle this!

Thank you again!

vladk

 

[strongm]

>it might be important to know what option is selected in clients(!) Excel

The only time I can see Option Explicit potentially being a problem for programatically inserted code is if the client's setting is Option Explicit, but your code is not making explicit declarations. In which case you are inserting what we might describe as 'poor' code


All other scenarios are moot, as far as I can see

 

[vladk]

strongm, the code inserted is short- three or so lines, I just want to insert it taking into account existence of the option explicit in the newly created sheet. It is not "poor" code.

Thank you for your reponse, I expected it.

:0)

 

[vladk]

And, btw, there is still no code for this, engaging the EXCEL object model, this is what I would like to see...

 

[macropod]

vladk: Whether the client has 'Option Explicit' set for code you provide is completely irrelevant - unless you've developed your code without it and you haven't declared your variables properly. If that's the case, you might want to turn it off - and the client should be looking for a different programmer.

Cheers
Paul Edstein
[MS MVP - Word]

 

[fumei]

I agree, and I STILL fail to see why you want to turn it OFF, even if it was off to start.

If it was off, and you turn it on, why would you want to turn it off again? In order to make it easier to write code that does not require declarations? That seems very very odd to me.

I should say that occasionally developers write code not only for their own programming environment but for others too.

Yes they do...and when they do they should ALWAYS declare their variables!

It is accepted to call them "clients" machines. You might heard of them. Sometimes they have Option Explicit sometimes NOT. And what is even more interesting, fumei, the NOT is a default setting.

Yes, I have heard of them. Are you being sarcastic?

We are all aware that Option Explicit is not on by default. I will wager that 99.9999% of us have thought many times that this is a mistake by Microsoft.

 

[strongm]

Exactly my point, macropod

 

[combo]

In fact, the "Require Variable Declaration" setting does nothing except of automatic adding the "Option Explicit" statement at the top of newly created code modules. Only existence of this statement decides that the compiler requires variable declarations.
If you plan to r/w the setting directly in the registry, mind that you need to restart vba to affect changes. It's much simpler to clean-up new module (whatever was added) and add the code whatever you like:

Dim cm As CodeModule
Set cm = ...
cm.DeleteLines 1, cm.CountOfLines
cm.AddFromString strNewCode

combo

 

[vladk]

Guys, the only thing I wanted is to see if Option Explicit is On or Off to carefully insert lines if needed. I had no intention to leave it ON or Off on someone PC permanently - just wanted consistency.

Still see nothing wrong with my question (question is always just a question) and do not see the answer (may be just for the sake of curiousity), which whould employ the Excel object model. Looks like it is cannot be done?