Experts: Pls advise on VBA error handlers

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
7,146
Office Version
  1. 365
Platform
  1. Windows
In trying to help out here wherever I can I notice that members who are far better at Excel vba than me will post code that does things like alter (turn off) application settings but they don't use an error handler in that code. Nor do they suggest that their code should be modified to include one. I'm trying to understand why those members don't use one or advocate it. I have copied/pasted code that the OP says raises an error and found that it does. Then I notice that when the procedure terminates (I click OK to the error message), my app settings remain altered. Here's an example of the type of altered settings I'm referring to
VBA Code:
Application.EnableEvents = False
Application.Calculation = xlCalculationManual = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If you resume using the workbook, do you not risk doing so while things are likely altered in a way that might be detrimental (e.g. events are disabled)? The only way things will get back to normal is if the problem procedure (or one like it) eventually runs to its completion. Why is it that I don't see anyone else recommending an error handler that will ensure these settings are restored to normal if the procedure terminates early due to an error?
Perhaps to clarify what I'm saying ought to be done, review the following pseudo code.
VBA Code:
Some Sub()
'Declarations

On Error GoTo errHandler

'some code next

exitHere: 'a line label
With Application
    .EnableEvents = True
    .Calculation = xlCalculationManual = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
Exit Sub 'don't forget this line!!

errHandler:
' do stuff to handle errors
Resume exitHere 'go to line after exitHere line label, which will restore the altered app settings

End Sub

TIA
 
However, I might not fully grasp the intent of storing the values first.
Because for example if their calculation was set as Manual before they ran the code then they might not appreciate it being set as Automatic at the end, they would probably want it returned to the original state before the code was ran (the same with all the other settings)
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I think you are saying that the block of code that would restore it needs to know what it was because there are multiple options, whereas the commonly altered settings are either True or False only. Certainly, knowing which ones have multiple settings would be important if you're going to alter them in code.
 
Upvote 0
I think you are saying that the block of code that would restore it needs to know what it was because there are multiple options
Exactly, if you change the state of the applications then you should return them to the state they were at the start of the code, not make the user have to change what they had set as False back to True.

Most code we post on the forum assumes the most commonly set options, Calculation is the obvious one that you see we normally set to True at the end when really we should return it to the state it was in at the start of the code (as @zero269 has done and I do when I distribute code at work, I also record the state of page breaks as I also often turn that off)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,113
Messages
6,189,048
Members
453,522
Latest member
Seeker2025

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
Back
Top