VBA command or routine to "reset" all processes?

[EssKayKay]

Is there a VBA command or routine that “resets” all VBA processes?

As example (there may be others), what I’ve noticed is if an erroneous date is entered (say 01-33-24) an error will appear – “The value you entered is not valid”. I understand that. However, from that point I believe all other actions being controlled by either the Worksheet_Change or Worksheet_SelectionChange subroutines are halted. The only way I have been able to resolve this is by closing the file and reopening it. Any suggestions?

Thanks,
Steve

 

[Micron]

Hard to say without seeing the relevant procedures, but I'm going to guess that you don't have proper error handling and you are altering application settings. Then when you raise an error, those settings are left that way. I've said many times in here that I would not disable events (for example) without using a proper error handler. If Excel leaves events disabled when you re-open it then I have not pegged the problem, but I don't think it does that.

 

[EssKayKay]

Thank once again Micron. I am sure you are correct that my problem lies with my error handling. However, I have to say I do not understand that process (error coding, syntax, hierarchy, etc.). I have tried reading, testing, reading some more, testing some more. . ., but apparently it is just a bit beyond my pay grade. I’ll look at this again but I suspect I will just have to leave it as is. I was hoping there was some simple “reset” command or process to do so. Oh well, now I know.

Much appreciated,
Steve K.

 

[kevin9999]

As @Micron says Steve, hard to say without seeing your procedure, but here's an example of how to handle errors in a Worksheet_Change event code that resets Application settings following an error.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A:A"), Target) Is Nothing Then  '<-- Example only
        On Error GoTo Escape                        '<-- If error encountered, jump to "Escape" line
        Application.EnableEvents = False            '<-- Application settings changed here
        Application.ScreenUpdating = False
        Application.Calculation = xlManual
        
        '*** Your code goes here ***                '<-- Your code sits here
    
    End If
Continue:
    Application.EnableEvents = True                 '<-- Application settings reset here
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Exit Sub
Escape:                                             '<-- Code jumps to here if error encountered
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue                                 '<-- Code jumps to "Continue" line after error is articulated
End Sub

 

[Alex Blakenburg]

I am not sure I should be suggesting this since handling the error is the preferred approach but Paul Kelly from Excel Macro Mastery used to recommend using a subroutine turn off settings and another to turn them back on. One benefit of this is that they can be run independently in the way you are asking.

Turning them back on is done by this sub:

' Purpose   : turn on automatic calculations, events and screen updating
' https://excelmacromastery.com/
Public Sub TurnOnFunctionality()
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

You can find this and the TurnOff version here in the link below under the heading "Speed Up Your Code"

Excel VBA Copy - The Complete Guide to Copying and Filtering Data - Excel Macro Mastery

This article is the most complete guide to the Excel VBA Copy methods. Written in clear English, it includes tons of practical real-world code examples.

excelmacromastery.com

 

[dmt32]

Paul Kelly from Excel Macro Mastery used to recommend using a subroutine turn off settings and another to turn them back on.

Hi Alex,
just for interest, for most projects, I use a common code which some may find useful that is similar to Paul Kelly but I have included a Boolean parameter allowing both actions (True / False) to be performed in a single code.

Sub EventsEnable(Optional ByVal State As Boolean = True)

    With Application
        .ScreenUpdating = State
        .EnableEvents = State
        .DisplayAlerts = State
        .DisplayStatusBar = State
        .Calculation = IIf(State, xlCalculationAutomatic, xlCalculationManual)
    End With
    
End Sub

Default is True

Dave

 

[Alex Blakenburg]

@dmt32 Thanks for sharing Dave

 

[Micron]

I never thought of making application settings handling into a stand alone procedure but if I did that I think I would declare all the settings variables as optional strings. Then if the parameter is not "" one line per setting should switch the state without needing True or False:

If strEvents <>"" Then Application.EnableEvents = Not ApplicationEvents

So if your intention is to switch screen updating only, pass a value to that parameter and not any other. The drawback to using a stand alone procedure might be the number of application property settings one might be in the habit of using could make one procedure declaration rather long.

 

[dmt32]

I never thought of making application settings handling into a stand alone procedure but if I did that I think I would declare all the settings variables as optional strings.

You can do whatever works for you - In my post I just included the common settings most turn off & then turn back on so in such a case, a single parameter would normally suffice.

- point of post was really just show that you can avoid repeating the same routine in a different state

Dave

 

[Micron]

And I was just stating an opinion. No need for you to get defensive.