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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
76,344
Office Version
  1. 365
Platform
  1. 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
76,344
Office Version
  1. 365
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,918
Messages
5,834,370
Members
430,281
Latest member
fabcat1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top