Experts: Pls advise on VBA error handlers

[Micron]

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

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.

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

 

[zero269]

The only way things will get back to normal is if the problem procedure (or one like it) eventually runs to its completion.

And just like that, I now have a good Error Handling Solution for my code.
I do this all the time with my preschool level macros... trial and error - and always have to turn Event, Screenupdating, etc. back on as the default.

This makes for a great template!

However, this makes me think of a question... but first I'll try testing it out first.

 

[MARK858]

Wouldn't the code above hide any errors in the procedure whether from the application settings being changed causing the error or any other error?

 

[Micron]

Wouldn't the code above hide any errors in the procedure whether from the application settings being changed causing the error or any other error?

Not really. The line 'do error handling stuff is where you'd take care of that. I was just trying to keep the post as short as I could while seeking an answer to a general question/dilemma. Here is a more complete example I just grabbed from a procedure.

Sub SaveActiveInCurrentFolder()

On Error GoTo errHandler
With Application 'Note; this is where application settings get altered and can remain altered if code errors
   .ScreenUpdating = False
   .EnableEvents = False
   .DisplayAlerts = False
End With

ActiveSheet.Copy
With ActiveWorkbook
   .SaveAs ActiveSheet.Range("C2") & ".xlt"
   .Close SaveChanges:=True
End With

exitHere:
With Application
   .ScreenUpdating = True
   .EnableEvents = True
   .DisplayAlerts = True
End With
'this is also where I'd clean up any object variables that were Set (e.g. Set myObject = Nothing)
Exit Sub

errHandler:
'simple example of presenting custom error message only
MsgBox "Error " & Err.Number & ": " & Err.Description
'other handling could be employed; e.g. Select Case block to present various messages according to error number.
' Case Else could be used for any error that did not relate to the Case statements

Resume exitHere 'resume where the settings will be re-set
End Sub

I am influenced by my Access vba learning. As a result, I would never alter Access app settings without an error handler like the one above. I also use the handler to clean up object variables. The topic is somewhat contested. Some say that default garbage handling (reclaiming memory held by object variables when procedures terminate) is good enough. Others say that it should not be relied on. My take is that if one wants to do what one can to maximize memory resources, why not write a few lines of code to explicitly destroy object variables?

Perhaps I digress too much. Basically, I just wanted to know why few of the people who are a lot smarter with Excel vba than me don't seem to use error handlers to ensure application settings are restored.

 

[MARK858]

Perhaps I digress too much. Basically, I just wanted to know why few of the people who are a lot smarter with Excel vba than me don't seem to use error handlers to ensure application settings are restored.

I will speak for myself but I only use error handlers to handle expected errors (like when using SpecialCells), any other errors I want to appear so I can code it so the error doesn't occur rather than deal with the symptom (I'm not an expert though)

The line 'do error handling stuff is where you'd take care of that.

That isn't fixing what caused the error, it is trying to code around the cause

 

[Micron]

I will speak for myself but I only use error handlers to handle expected errors

The implication is that you have envisioned every error that could terminate the procedure and have coded accordingly. Not my style, but if it works for you that speaks to your level of expertise. It is likely more advanced than mine.

any other errors I want to appear so I can code it so the error doesn't occur

What I posted raises an error message, so it does 'appear' and when the message box is dealt with, the settings are restored in that example. Your comment suggests to me that at that point you would still be in the development stage. However, if you release the workbook to others you are expecting that any and all traps you covered are the only ones that others might fall in to. Also, it may be useful to point out that the error handler can fix an issue and resume execution of the code at the point where the error happened,, or even at the next line. That part is all about the error itself and choosing a point at which to Resume. There is
Resume (returns to the line that raised the error)
Resume Next, (resume at the line after the line that raised the error) and
Resume 'line label' (resume at the line after the line label)
I only showed one method.

@MARK858 I dare say that you are much more experienced with Excel vba than I am, so please don't take any of my comments as though they are coming from anyone who claims to be better at this. I just don't seem to grasp why others don't code for the unforeseen in a way that ensures that when they try to do stuff after an error, it will work because events haven't been disabled and/or screen views are not updating.

 

[MARK858]

it will work because events haven't been disabled and/or screen views are not updating.

Screen updating automatically resets to true when a macro ends (bar a few quirks with userforms), events need resetting.

However, if you release the workbook to others you are expecting that any and all traps you covered are the only ones that others might fall in to

If you are asking when I release a workbook to others do I place a generic error handler in the code the answer is no, I expect to pick up errors created by the code during the Beta testing.
I do put in code to test the state of the application settings at the start of the code so it is restored to the same settings but that is a different subject to error handling.

 

[zero269]

Hi @Micron

So, the thing I was thinking of earlier is to first store the state of each of the settings at the beginning so different users would have their specific settings restored at the end.

Do you think something like this would be better suited in a Function; the top part that captures the current state?

Below is just a modification of yours...

Sub WIP_Error_Handling()

  'Declarations to capture current settings that can be reverted at the end in the event of an error
  Dim Events As Boolean: Events = Application.EnableEvents
  Dim Calculations As Integer: Calculations = Application.Calculation
  Dim Screen As Boolean: Screen = Application.ScreenUpdating
  Dim Display As Boolean: Display = Application.DisplayAlerts
 
  On Error GoTo errHandler
 
  'some code next
 
exitHere:   'a line label
 
  With Application
      .EnableEvents = Events
      .Calculation = Calculations
      .ScreenUpdating = Screen
      .DisplayAlerts = Display
  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

References:

Application.EnableEvents property (Excel)

Office VBA reference topic

learn.microsoft.com

XlCalculation enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

Application.ScreenUpdating property (Excel)

Office VBA reference topic

learn.microsoft.com

Application.DisplayAlerts property (Excel)

Office VBA reference topic

learn.microsoft.com

Data type summary

Office VBA reference topic

learn.microsoft.com

 

[Alex Blakenburg]

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?

I think a lot of it revolves around the nature of the Forum.
We are generally trying to provide solutions to specific problems and often working with small segments of the overall code.
Best Practice would be as @zero269 suggested, to reset the settings to their original condition but then you might have the issue that they were all off due to a macro crashing, so a more common practice would be to turn them all back on.
(the one I find particularly nasty is code that turns iterations on and doesn't reset it back)

We also tend to provide solutions that are in a single module whereas in full development mode they would broken up into called subs and functions and Paul Kelly from Macro Mastery puts his Application functions into a Class module. This tends to be confusing to the OPs and needs a lot more web page space and explanation.

Jaafar tends to give more complete function call type solutions. If you have a look at some of his solutions in the Forum, he definitely has the error handling you are referring to.

 

[Micron]

you might have the issue that they were all off due to a macro crashing, so a more common practice would be to turn them all back on.

Exactly my point and why I was asking that so many provided code solutions don't at least advocate ensuring they are restored. The only way I know how to do that is to have an error handler (note, that does not mean 'coding around the error'). I guess you gave the best answer in that for some reason it doesn't seem to fit into the nature of the forum so I'll mark yours as the solution.
@zero269; just my opinion but I look at that as just extra code that isn't required since if I turn them off and
- advise of the error and perhaps give choices on what to do about it
- direct code execution to the block that resets the settings that were altered
If there was no error, that block still gets executed.
However, I might not fully grasp the intent of storing the values first. Still learning Excel vba but it's only for the purpose of trying to help members here. I'm retired thus have no use for complicated workbooks, code, charts or formulas any more.