Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1246
  • Last Modified:

Programmatically change Excel VBA VBE IDE Options Settings

In Excel 2003, in the VBA IDE, when you go to Tools -> Options you can change Editor code settings (e.g. Require Variable Declaration) or "Break on All Errors" error trapping.

How can you programmatically change these settings?
0
jnash67
Asked:
jnash67
  • 2
  • 2
2 Solutions
 
Rory ArchibaldCommented:
I could be wrong, but I don't believe you can. It might be possible via changing the registry but I'm not sure how you would get the VBE to pick up the changes if you did that.
0
 
jnash67Author Commented:
Your comment put me on the right track.  Abandoning an elegant approach, I figured out a sendkeys approach which works:
Sub breakOnAllErrors()
    Application.VBE.CommandBars("Menu Bar").Controls("Tools").Execute
    SendKeys "O"
    SendKeys "+{TAB}"
    SendKeys "{RIGHT}{RIGHT}"
    SendKeys "%B"
End Sub

Open in new window

0
 
Dave BrettVice President - Business EvaluationCommented:
Rory,
I used RegMon and found the key for the  "Require Variable Declaration" setting, but deleting or adding it didn't impact the current Excel instance. Plus the setting must be stored somewhere else as Excel "reset" my registry change on relaunch
I got to the same point as the author on  the commandbar, but frustratingly I couldn't bring up the properties of the Options control programmictally, so given my dislike of Sendkeys I let it go wondering if there would be a more purist VBA solution.
Cheers
Dave
0
 
Rory ArchibaldCommented:
Dave,
What was the key? I may have an idea...
Rory
0
 
Dave BrettVice President - Business EvaluationCommented:
'Require Variable Declaration' is  
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\BaseSuite\A2B280D420FB472099F740C09FBCE10A
I tried wscript to delete and add the value
Cheers
Dave
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now