Excel application events: How to automatically reset WithEvents variable after a runtime error?

Question

I use PERSONAL.XLSB to execute global macros. Within it, I also listen to application events by doing this in the ThisWorkbook object:

Dim WithEvents app As Application
Private Sub Workbook_Open()

    Set app = Application

End Sub

Now I can listen to all workbook events without having to wire up an event to each workbook.

The problem is that, whenever a macro throws an exception and causes me to have to End the macro, all global variables get nuked, so the app variable gets set to Nothing which causes all global events to no longer fire.

Is there some way I can have this variable automatically set back to an instance of Application whenever it gets nulled out?

Answer 1

You can create a public function which contains the default values of all the global variable then call that function under your error handle. An example:

Dim WithEvents app As Application
Private Sub Workbook_Open()
  On Error GoTo reset
    Set app = Application
  On Error Goto 0   ' Turn off error trapping. 
  Exit Sub
Reset:
    Call resetvar
End Sub

Public Sub Resetvar()
    Set app = Application
End Sub