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

in the VB Editor

Tools > Options Editor TAB -- REQUIRE VARIABLE DECLARATION

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

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

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.

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

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,

_{Just traded in my old subtlety...
for a NUANCE!}

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.

The devil made me do it!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Nah, that is impossible.

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

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

combo,

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

Thank you again!

vladk

>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  

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)
 

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

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]

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.

Quote:

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!

Quote:

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.

Exactly my point, macropod

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:

 

combo

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?


   

Quote:

to carefully insert lines if needed.

If NEEDED???

Needed for WHAT???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Ti's VBE setting, so you should rather look at VBIDE objects. But there's nothing here.

combo

Skip, it does not matter for what. This is just a question - how to play with such settings in VBA?

:0)

Yes, combo, I also think that it was supposed to be in VBIDE. I still believe it can done without direct registry reading.

It's neither in office application object model nor in VBE one. So the only solution is to build your own access or search for third party solution, that will do the same.

But, going back to the rationale of reading or changing this option, the only sense is to indirectly check if the "Option Explicit" statement is added to new code module, and maybe inform the user. A sample code in my post above clears the module whatever it is empty or not, and add whatever you like. One don't need to care about registry access.

As I pointed above, it's not a global statement, it refers only to a module that contains it, so one can have a mix of modules with various requirements even in one project.
I guess that you plan to allow users to access your code. How do you prevent them from deleting the "Option Explicit" at the top of your module? And why should you? Of course, as a good programmer you may like to add it to client's code, but your power ends here.
 

combo

One way of adding "Option Explicit" if it's missing:

Cheers
Paul Edstein
[MS MVP - Word]

Having "Require Variable Declaration" set means that new Modules will have "Option Explicit" automatically added as the first line. It has no effect on pre-existing modules, and you cannot tell from the setting which modules have the option set and which don't.

So the only time you will see any effect at all is when you insert a new module. In your case this will happen when you add a new sheet. At that point you can examine the code in that module and if the first line is "Option Explicit" you know the option was in effect, and if it isn't, you know it wasn't. From that point on you can take whatever action you wish based on what you have found out.

The significant setting that you need to worry about is the "Trust access to VBA object model". If that is not set, you won't be able to add any code, with or without variable declaration.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website