VBA: Global Variable Cleared after 'Workbook_Open' sub

Question

I have set up an application level event class to monitor when new workbooks are created/ opened by following CPearson's guide. This works fine in isolation. However, it is intended as part of add-in I'm writing where several other subs are also called in the 'Workbook_Open' sub, see below code:

Private XLApp As baseCXlEvents

Private Sub Workbook_Open()
    Set XLApp = New baseCXlEvents
    test
    AddLocalReferences
    AddModules
    AddClassModules
    Debug.Print "testing"
End Sub

So the XLApp variable is called in the module scope as a baseCXlEvents class. I have added a Class_Terminate event to this class and this is triggered after the Debug.print "testing" is run, i.e. XLApp is terminated after the Workbook_Open sub has run. This does not happen when I quote out the subs AddLocalReferences, AddModules, and AddClassModules, which do exactly as their names would imply. The sub test only prints a messages in debug to test whether calling additional subs caused XLApp to be terminated.

My current 'hunch' is that adding references, modules, or class modules counts as "editing", causing it to be terminated, as explained in this MS Support document. But, if so, why doesn't XLApp get terminated until the end of the sub? As opposed to as soon as AddLocalReferences is run.

Any suggestions why the class is terminated? I need it to 'stay alive' and also need to load additional modules and references for the addin upon workbook_open. If needed more details of this code can be provided.

I've decided to add my baseCXlEvents class module's code:

Option Explicit

Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "New Workbook: " & Wb.Name
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Workbook opened: " & Wb.Name
End Sub

Private Sub Class_Initialize()
    Debug.Print "Initializing baseCXlEvents instance.."
    Set App = Application
End Sub

Private Sub Class_Terminate()
    Debug.Print "Terminating baseCXlEvents instance.."
End Sub

In the functions you do probably something which triggers the termination of the object XLApp. But have you tried to move the Set XLApp = New baseCXlEvents to the very end of the Workbook_Open() procedure? — Daniel Dušek
If it isn't used after that it might be the garbage collector? The only problem is the garbage collector for vba is not really documented so I am not sure on this, just a possibility. — Evan
@dee I have thought of that and actually did try moving the Set to the the back. This does not work. And wouldn't you expect to see the Class_Terminate trigger 'mid-sub' if this was the case? @Evan It is not used afterward directly, but it should be 'on watch', monitoring events. Any suggestions on how to test this? This article suggests garbage is collected when all references are either gone or out of scope. Which is not the case here. — Martin
Should your declaration of XLApp include the hook for events... as in Private WithEvents XLApp As baseCXlEvents? — PeterT
@PeterT : My baseCXlEvents class has Private WithEvents App As Application and Private Sub Class_Initialize() Set App = Application End Sub This should add the events hook for the object. — Martin

Daniel Dušek Daniel Dušek · Accepted Answer · 2015-07-21T13:06:40

Well then try to separate the Workbook_Open event handler stuff (where you add references etc.) from the creation of the instance of the class baseCXlEvents using Auto_Open.

Workbook_Open runs first, Auto_Open runs then.

Note: baseCXlEvents Instancing must be set to Public.

enter image description here

ThisWorkbook class

Public XLApp As baseCXlEvents

Private Sub Workbook_Open()

    Test
    AddLocalReferences
    AddModules
    AddClassModules

End Sub

Standard module

Sub Auto_Open()
   Set ThisWorkbook.XLApp = New baseCXlEvents
End Sub

VBA: Global Variable Cleared after 'Workbook_Open' sub

1 Answers