• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1295
  • 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
 
DaveCommented:
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
 
DaveCommented:
'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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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