(Solved) – What to do when VBA Error handler causes error?


I am currently working on a larger program which may throw an error at some point. So I added an error handler for that case. The primary goal of which is to close the opened excel workbook wbk so that I don’t get too many excel-applications running in the background. As I don’t know, which part of the program may cause the error, I don’t know the state of wbk and whether it is open or not. I tried adding a check for Nothing but wbk is Nothing seems to be false at that point. With the following code I will get The remote server machine does not exist or is unavailable (Error 462) when the error handler is called:

Error Handler

On Error Resume Next
resp = MsgBox(prompt:="Es ist ein Fehler aufgetreten! " & vbCrLf & vbCrLf & "Soll versucht werden weiter fortzufahren?" & _
    vbCrLf & vbCrLf & "Fehlercode: " & str(Err.Number) & " entstanden durch " & Err.Source & Chr(13) & Err.Description, _
    Buttons:=vbCritical   vbYesNo   vbDefaultButton1, _
    title:="Unbekannter Fehler", _
    HelpFile:=Err.HelpFile, _

If Not wbk Is Nothing Then
    wbk.Close False
End If

If resp = vbNo Then Exit Sub
Resume Next

Afaik, I shouldn’t even get an error because of the On Error Resume Next. Edit: It should since “The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.” – source

TL;DR: wbk should be closed in error handling if it is open, but it throws an error.

I force an error in order to call the error handler. Said error does only happen in the second execution so the wbk value is set normally, then the workbook is closed and the program is called again and the error is forced before wbk is initialized again.

Leave a Reply

Your email address will not be published. Required fields are marked *