How can I call a subroutine after an error?

Question

I'm working on user-proofing a VBA app in Excel and I realized I'm constantly rewriting a lot of the same code to create specific error messages that are fed strings of info for the user to be able to tell me when they inevitably call me to say they're getting errors.

I want to create something like this to simply call on each error and pass the particulars:

Sub GenericError(ErrNum As Long, ErrDesc As String, sequence As String, errtype As String)
'In case of error, returns actual error code and calls for abort; if no abort then can debug
Dim msgstrng As String
msgstring = sequence & " error " & errtype _
    & vbLrCf & "Error code " & ErrNum & " - " & ErrDesc
MsgBox msgstring, , sequence & " Error!"
cont = Abort_Check()
On Error GoTo 0
End Sub

Then ideally I could use something like

On Error Call GenericError(Err.Number, Err.Description, "Startup Sequence", "File Not Found")
'code that opens a file....
On Error goto 0

It seems like On Error only works with goto or resume; there are a bunch of modules that would need this in a lot of subs so writing basically this in a bunch of different GoTo blocks is annoying. Is there a solution for me to use an error to call a sub like this?

There's been a lot written about generic error handlers. Eg [this](https://stackoverflow.com/q/6028288/445425) — chris neilsen, May 13 '22 at 21:27
I was giving an example possibility; there will be hundreds of users on this and lots of things that could go wrong — J_Nyiri, May 13 '22 at 21:28
To mitigate errors, consider the UI features available, e.g. protect ranges on a sheet, protect workbook structure to prevent sheet deletion, keep `xlVeryHidden` any sheets your code needs to read so users can't change them etc. Also, consider log files to capture the error details; the user won't necessarily remember everything on the MsgBox - if you output a log file to a shared drive, then you could consult that after a user alerted you, and have proper debug info to you — Spectral Instance, May 13 '22 at 21:52

freeflow · Answer 1 · 2022-05-13T21:33:17.783

Encapsulate your error generators in a try function

Public Function TryDoThisThing( byval ipInput1 as variant, byval input2 as variant,byref opReturnValue as variant, byref opErrorNumber as long, byref opErrorDescription as String) as boolean

   On Error Resume Next
   opReturnValue = DoThistThing(ipInput1, ipInput2)
   TryDoThisThing = err.number = 0
   opErrorNumber = err.number
   opErrorDewscription = Err.description
   Err.Clear

Exit Function

Then in your code you'd have

If not TryDoThisThing(val1, val2, myResult, myErrNo,myErrDesc) then

    GenericError myErrNo, myErrDesc, sequence , errtype
    <any other corrective actions>
end if

@scottcraner Thanks for the edit. Unfortunatly I hit a wrong key combination and the answer was added before I'd finished typing, that's why it looked so bad. — freeflow, May 14 '22 at 07:50

How can I call a subroutine after an error?

1 Answers1