Be sure to run code before exit

[Stevenn]

I've got following code in the beginning of my procedure:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .Cursor = xlWait
    .DisplayStatusBar = True
End With

but sometimes my function will result in an error. Is it possible to 'restore' the settings by following code

With Application
    .Cursor = xlDefault
    .StatusBar = False
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

even if the function ends before the end of the code in the procedure?

 

[cjo]

What you're looking for is VBA's error handling, which is done with "On Error Goto" or "On Error Resume", depending on exactly what needs to be done. There's a fair bit out on the internet on error handling, and ways to use it. For example, be very conservative with the use of On Error Resume and/or On error Resume Next.

Sub ErrorExitSample()
    Dim strTest                    As String
    Dim i                          As Double

    On Error GoTo RestoreAppStates
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .Cursor = xlWait
        .DisplayStatusBar = True
    End With


'this is just a sample chunk of code... your real code
'would go into this area
    Do
        strTest = InputBox("Enter a number")
        If IsNumeric(strTest) Then Exit Do
        MsgBox ("That isn't a number")
    Loop
    i = CDbl(strTest)

    'next line will throw an error if '0' is entered in the input box
    MsgBox (1 / i)
    'this line (and any other code before the 'RestoreAppStates:' label) will
    'be skipped if 1/i gives an error.
    MsgBox ("Succesful division!!!")
'end of sample code


RestoreAppStates:
    With Application
        .Cursor = xlDefault
        .StatusBar = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Of course, the very best way to handle code errors is to make sure that they can't occur. In the sample above, I corrected for non-numeric input, but 'neglected' to correct for a input of 0.

 

[Stevenn]

What you're looking for is VBA's error handling, which is done with "On Error Goto" or "On Error Resume", depending on exactly what needs to be done. There's a fair bit out on the internet on error handling, and ways to use it. For example, be very conservative with the use of On Error Resume and/or On error Resume Next.

Sub ErrorExitSample()
    Dim strTest                    As String
    Dim i                          As Double

    On Error GoTo RestoreAppStates
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .Cursor = xlWait
        .DisplayStatusBar = True
    End With


'this is just a sample chunk of code... your real code
'would go into this area
    Do
        strTest = InputBox("Enter a number")
        If IsNumeric(strTest) Then Exit Do
        MsgBox ("That isn't a number")
    Loop
    i = CDbl(strTest)

    'next line will throw an error if '0' is entered in the input box
    MsgBox (1 / i)
    'this line (and any other code before the 'RestoreAppStates:' label) will
    'be skipped if 1/i gives an error.
    MsgBox ("Succesful division!!!")
'end of sample code


RestoreAppStates:
    With Application
        .Cursor = xlDefault
        .StatusBar = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Of course, the very best way to handle code errors is to make sure that they can't occur. In the sample above, I corrected for non-numeric input, but 'neglected' to correct for a input of 0.

I am trying to avoid any errors to occur, but I have to make use of "Exit Sub" in For Each-loop, and it wont go to my error handler. But your great example works just fine when my database call fails Is there any way to make sure to restore the app states when I manually exit the procedure with "Exit Sub"?

 

[Stevenn]

And it should also restore the app states even if there exists no errors.

 

[cjo]

Without seeing the code, I can't give an exact answer, however, here's some suggestions.

If the sub is being called from another sub, you could put the application.* lines in the calling sub, like this:

Sub ParentSub()

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .Cursor = xlWait
        .DisplayStatusBar = True
    End With

    'Called sub has an on error goto line, but 
    'does NOT make changes to the application object
    'properties.
    Call CalledSub

    With Application
        .Cursor = xlDefault
        .StatusBar = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Sub CalledSub()
    On Error GoTo ExitMySub

    'the sub that does stuff


ExitMySub:

End Sub

If you want this in a single procedure, you can just make sure to restore back to the default before each exit sub, something like this:

Sub SingletonSub()
    Dim strTest                    As String
    Dim i                          As Long

    On Error GoTo ExitMySub
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .Cursor = xlWait
        .DisplayStatusBar = True
    End With

    'your first loop with an exit sub condition
    For i = 1 To 10
        'some if then that causes an exit from sub
        If i = 7 Then
            With Application
                .Cursor = xlDefault
                .StatusBar = False
                .Calculation = xlCalculationAutomatic
                .ScreenUpdating = True
            End With
            Exit Sub
        End If
    Next i
    'second loop with an exit sub condition
    For i = 4 To 25
        If i / 3 = 7 Then
            With Application
                .Cursor = xlDefault
                .StatusBar = False
                .Calculation = xlCalculationAutomatic
                .ScreenUpdating = True
            End With
            Exit Sub
        End If
    Next i

    'if sub doesn't exit early, or if there is an error, this will run
ExitMySub:
    With Application
        .Cursor = xlDefault
        .StatusBar = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub