CODEWITHSUNDEEP
vbaexcel
Martin
Martin

Reputation: 57

VBA: Global Variable Cleared after 'Workbook_Open' sub

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

Upvotes: 3

Views: 1449

Answers (1)

gembird
gembird

Reputation: 14053

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

Upvotes: 0

Related Questions