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]

to carefully insert lines if needed.

If NEEDED???

Needed for WHAT???

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

 

[combo]

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

combo

 

[vladk]

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.

 

[combo]

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

 

[macropod]

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

Sub AddExplicit()
Dim VBComp As VBComponent, StrCode As String, i As Long
For Each VBComp In ThisDocument.VBProject.VBComponents
  With VBComp.CodeModule
    StrCode = Trim(.Lines(1, .CountOfLines))
    For i = 0 To UBound(Split(StrCode, vbCrLf))
      If Trim(Split(StrCode, vbCrLf)(i)) = "Option Explicit" Then
        Exit For
      ElseIf InStr(Trim(Split(StrCode, vbCrLf)(i)), "(") > 0 Then
        .AddFromString ("Option Explicit" & vbCrLf)
        Exit For
      End If
    Next
  End With
Next
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[TonyJollans]

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