Changing the VBE's settings programmatically

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
Hi,

I'm having some problems with some code breaking on other peoples computers. The code breaks are all in this type of format:
Code:
on error resume next
'do something that deletes something that might not exist
on error goto 0
I've figured out that it's caused by the VBE having Tools -> Options -> General -> 'Break on all errors' selected.

What I want to know is if there's a way to change the setting programmatically, or if I'm going to have to run around to everyone's computer and change it. Or if there's a better way to handle this, because as cpearson.com says:
CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Hi,

I'm having some problems with some code breaking on other peoples computers. The code breaks are all in this type of format:

Code:
on error resume next
'do something that deletes something that might not exist
on error goto 0I've figured out that it's caused by the VBE having Tools -> Options -> General -> 'Break on all errors' selected.

What I want to know is if there's a way to change the setting programmatically, or if I'm going to have to run around to everyone's computer and change it. Or if there's a better way to handle this, because as cpearson.com says:

Quote:
CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details
Interesting. I don't see a lot of posts describing this as a problem (occasionally, yes, but frequently, no). Maybe someone in your IT dept has decided to set up Excel this way - it appears that the normal case is otherwise. I'd just go ahead and check everyone's computer and that should be then end of it. Or wait to get the calls about how your code is broken and fix it as it happens...
 

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
Yeah, I thought the solution would be a manual fix. There's 30 or 40 associate computers to change, so it'll be a good afternoons work...

As to why IT set up everyone's computer like this, I suspect it wasn't intentional, but rather someone in the distant past messed around with VBA just enough to find out that liberal use of "On Error Resume Next" wasn't a good idea, changed their setting, and then new hires all had their new computer setups copied from that persons setting.

It's a good thing I transfered my computer from my previous department.;)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,767
Office Version
365
Platform
Windows
Chris

I really don't think an IT department would set up Excel with this option as standard.

For one thing they would probably be the first port of call when the user got the error message, so they might prefer a setting that ignored errors.:)

If they wanted to stop people mucking around with VBA they might disable macros instead.
 

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454

ADVERTISEMENT

So far as I can tell, I'm the first person who really did anything with Excel Vba (at least more than a recorded macro) in the related departments in my office, so it'd never really have come up before. Also, the department I'm in has a poorly designed Access reporting system (running from poorly designed tables from a third party contracter), which might have caused someone previously in the department to select that option. (So far as I can tell, all your VBE settings in Office sync up.)

Of course, I'm still here at 7 because I can't figure out how to run a series of queries without hitting Access's 2 gig bomb out limit, so who am I to talk?:)

Oh, and IT here takes the hiring manager's word for how to set up a new hire's computer, and they always say "Make it like Joe's".
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,767
Office Version
365
Platform
Windows
Chris

I've never used Excel (or Access, or Word) VBA where the Break On All Errors option is set by default.

I've always switched it on myself.

As far as I know the option does not sync across Office application.

Or so I thought until about 10 secs ago, I checked Excel first and found I had Break on Unhandled picked - same for Word and Access.

Closed them all down, reopened Excel, changed the option and lo and behold it had changed in Word and Access too.:eek:

So maybe it can be set by default when installing or using witchcraft or something.:)
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows

ADVERTISEMENT

Or so I thought until about 10 secs ago, I checked Excel first and found I had Break on Unhandled picked - same for Word and Access.
That's interesting - I had no idea either.
 

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
After many months of aggravation, and having to teach users how to change the editors setting, I finally figured out the cause of the VBE changing the error handling setting. (I found out about this when someone asked me to fix a division by zero error in a report, and found a thousand lines of VBA)

In an Access reporting tool there's a bunch of subs to format out Excel reports. They are all of the form like this: (no real code, it's immaterial)

Code:
Sub Stuff234()
    On Error Resume Next
    Dim a
    Dim b
    Dim c
    [COLOR=red]Application.SetOption "Error Trapping", 0[/COLOR]
 
    'Buncha code, including a line with a potential division by zero problem
    '(which might be the reason for the massive On Error Resume Next)
    'and another line that attempts to save over a potentially open workbook
    'and maybe a line that might try to open a nonexistent workbook
    '(which might be why someone else used the .setoption line to prevent
    'completely bizarre results when errors are skipped, with the 'handling'
    'being done by the users when the code crashes)
 
    On Error GoTo 0
End Sub
ERGO
So maybe it can be set by default when installing or using witchcraft or something.
turns out to be the answer after all...

Plus, now I can stop putting these lines at the start of every macro:
Code:
    On Error Resume Next
    Debug.Print 1/0
    'IF YOU CAN SEE THIS,
    'change the Visual Basic Editor's error handling setting.
    On Error GoTo 0
 
Last edited:

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
Windows
Incredibly frustrating that some sloppy code which changes the VBE settings in Access also has an effect on Excel when it is next opened. Thanks for letting us know what was going on. :)
 

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
I'm just glad that the Division by zero error was never really fixed before me. :) As it was, it still took seven months before it was brought to my attention.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,924
Messages
5,514,195
Members
408,989
Latest member
tommo1949

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top