Set a reference to a Parent xlam from a Child xlam-CodePudding

I would like to setup a Parent xlam that encapsulates some common functionality and then reference the Parent xlam from a Child xlam so that I can call Parent xlam public subs. If I spin through the AddIns collection when the Child xlam opens, I can see the Parent but I can't figure out how to define a global variable to hold the Parent xlam in such a manner that I can see it's public subs. Is this possible? If so, can someone give me some ideas of what the VBA code would look like? Thanks.

With some help from comments below, a lot of googling and playing around, I have made some progress, although the code is not yet completely working.

This is what I am looking to do:

Create a parent AddIn (xlam) that uses Application events to trap NewWorkbook, WorkbookOpen and WorkbookActivate events. For appropriate files, it will raise public custom events: NewWorkbook, WorkbookOpen and WorkbookActivate, passing the Workbook and a blank case insensitive dictionary (think of it as a glorified Tag property) as event arguments
Child AddIns (xlam) would hook the Parent Addin and its custom events and do whatever custom processing was required

Here is what I have done so far on the parent AddIn based on the above:

Created a parent AddIn
Changed the Application name using Application.VBE.ActiveVBProject.Name, otherwise when I set a reference to the parent AddIn, it displayed as VBAProject
Created a public Manager class that raises the custom events based upon Application events as described above

The Parent AddIn

Option Explicit

' Required references
'   1. Microsoft Scripting Runtime, c:\Windows\SysWOW64.scrrun.dll

Private WithEvents m_EventManager As EventManager

Public Property Get EventManager() As EventManager
    Set EventManager = m_EventManager
End Property

Private Sub Workbook_Open()
    Set m_EventManager = New EventManager
    Call m_EventManager.Initialize
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set m_EventManager = Nothing
End Sub

Private Sub Workbook_AddinInstall()
    Set m_EventManager = New EventManager
    Call m_EventManager.Initialize(bIterateOpenWorkbooks:=True)
End Sub

Private Sub Workbook_AddinUninstall()
    Set m_EventManager = Nothing
End Sub

The Parent AddIn Manager class

Option Explicit

Public Event NewWorkbook(Wb As Workbook, OpenWorkbooks As Dictionary)
Public Event WorkbookOpen(Wb As Workbook, OpenWorkbooks As Dictionary)
Public Event WorkbookActivate(Wb As Workbook, OpenWorkbooks As Dictionary)

Private m_ManagedWorkbooks As Dictionary

Private WithEvents m_Application As Application

Private Sub Class_Initialize()
    Set m_ManagedWorkbooks = New Dictionary
    m_ManagedWorkbooks.CompareMode = TextCompare
    Set m_Application = Application
End Sub

Private Sub Class_Terminate()
Dim oDictionary As Dictionary
Dim vKey As Variant
    If Not m_ManagedWorkbooks Is Nothing Then
        For Each vKey In m_ManagedWorkbooks.Keys
            Set oDictionary = m_ManagedWorkbooks.Item(vKey)
            Set oDictionary = Nothing
        Next
    End If
    Set oDictionary = Nothing: Set m_ManagedWorkbooks = Nothing
End Sub

Public Sub Initialize(Optional bIterateOpenWorkbooks As Boolean = False)
Dim oBook As Workbook
    If bIterateOpenWorkbooks Then
        For Each oBook In Workbooks
            Call m_Application_WorkbookOpen(oBook)
        Next
    End If
End Sub

'***********************************

Private Sub m_Application_NewWorkbook(ByVal Wb As Workbook)
Dim oDictionary As Dictionary
    If Not IsHiddenExtension(Wb, oDictionary) Then
        If m_ManagedWorkbooks.Exists(Wb) Then
            Set m_ManagedWorkbooks.Item(Wb) = oDictionary
        Else
            Call m_ManagedWorkbooks.Add(Wb, oDictionary)
        End If
        RaiseEvent NewWorkbook(Wb, m_ManagedWorkbooks)
    End If
    Set oDictionary = Nothing
End Sub

Private Sub m_Application_WorkbookOpen(ByVal Wb As Workbook)
Dim oDictionary As Dictionary
    If Not Wb Is ThisWorkbook Then
        If Not IsHiddenExtension(Wb, oDictionary) Then
            If m_ManagedWorkbooks.Exists(Wb) Then
                Set m_ManagedWorkbooks.Item(Wb) = oDictionary
            Else
                Call m_ManagedWorkbooks.Add(Wb, oDictionary)
            End If
            RaiseEvent WorkbookOpen(Wb, m_ManagedWorkbooks)
        End If
    End If
    Set oDictionary = Nothing
End Sub

Private Sub m_Application_WorkbookActivate(ByVal Wb As Workbook)
Dim oDictionary As Dictionary
    If Not IsHiddenExtension(Wb, oDictionary) Then
        RaiseEvent WorkbookActivate(Wb, m_ManagedWorkbooks)
    End If
    Set oDictionary = Nothing
End Sub

'***********************************

Private Function IsHiddenExtension(Wb As Workbook, Optional BlankDictionary As Dictionary = Nothing) As Boolean
Const m_HiddenExtensions As String = ",xlam,xlsb,xla,xlb,"
Dim fso As FileSystemObject
    Set BlankDictionary = New Dictionary
    ' Case insensitive dictionary.
    BlankDictionary.CompareMode = TextCompare
    Set fso = New FileSystemObject
    If fso.GetExtensionName(Wb.Name) <> vbNullString Then
        IsHiddenExtension = (InStr(1, m_HiddenExtensions, fso.GetExtensionName(Wb.Name), vbTextCompare) > 0)
    End If
    Set fso = Nothing
End Function

Here is what I have done so far on the child AddIn based on the above:

Create a child AddIn
Browse to and set a reference to the parent AddIN
Declare private WithEvents variable to hold the ParentAddIn.Manager class
Declare event headers for the Manager custom events

The Child AddIn with attempted dependency on Parent AddIn

Option Explicit

' Required references
'   1. Microsoft Scripting Runtime, c:\Windows\SysWOW64.scrrun.dll
'   2. EventManager_New, Application.UserLibraryPath & EventManager_New.xlam
'      (e.g. C:\Users\UserName\AppData\Roaming\Microsoft\AddIns\EventManager.xlam)

Private WithEvents m_EventManager As EventManager_New.EventManager

Private Sub m_EventManager_NewWorkbook(Wb As Workbook, OpenWorkbooks As Scripting.Dictionary)
    Call MsgBox("Event m_EventManager_NewWorkbook: " & Wb.Name)
End Sub

Private Sub m_EventManager_WorkbookActivate(Wb As Workbook, OpenWorkbooks As Scripting.Dictionary)
    Call MsgBox("Event m_EventManager_WorkbookActivate: " & Wb.Name)
End Sub

Private Sub m_EventManager_WorkbookOpen(Wb As Workbook, OpenWorkbooks As Scripting.Dictionary)
    Call MsgBox("Event m_EventManager_WorkbookOpen: " & Wb.Name)
End Sub

The parent AddIn Manager class correctly reacts to events as expected and raises its public events as expected.

The child AddIn class event subs based on the parent AddIn manager class are NOT running.

CodePudding user response：

My original question is becoming pretty big and unwieldy, so this is kind of question 1a with some additional information.

If you write an AddIn that processes events, then the code will be run any time a New worksheet is created (e.g. File/New), any time a New workbook is opened, anytime a Workbook is activated, etc. even though most of the time it is only a very small subset of Excel files that need to be processed. As an example, create the small Sample AddIn below. All it does is write a message to the Immediate Window on a handful of Application Events. You will see how much noise and chatter is generated by this approach. Just click around in any given sheet or do Row or Column scrolls, PageUp & PageDown to generate all kinds of activity and chatter. The events are paired, 3 is Open, 4 is Close, 5 is Activate, 6 is Deactivate, etc.

SimpleExample01

Option Explicit

Private m_Count As Long

Private WithEvents m_Application01 As Application

Private Sub Workbook_Open()
    m_Count = 0
    Set m_Application01 = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set m_Application01 = Nothing
End Sub

'*******************************************************************

Private Sub m_Application01_NewWorkbook(ByVal Wb As Workbook)
    m_Count = m_Count   1
    Debug.Print "1. m_Application01_NewWorkbook: " & Wb.Name & "  " & m_Count
End Sub

Private Sub m_Application01_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim oBook As Workbook
    m_Count = m_Count   1
    Set oBook = Sh.Parent
    Debug.Print "  7. m_Application01_SheetSelectionChange: " & oBook.Name & ", " & Sh.Name & ", " & Target.Address & ", " & Target.Cells.Count & "  " & m_Count
    Set oBook = Nothing
End Sub

Private Sub m_Application01_WorkbookOpen(ByVal Wb As Workbook)
    m_Count = m_Count   1
    Debug.Print "3. m_Application01_WorkbookOpen: " & Wb.Name & "  " & m_Count
End Sub

Private Sub m_Application01_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    m_Count = m_Count   1
    Debug.Print "4. m_Application01_WorkbookBeforeClose: " & Wb.Name & "  " & m_Count
End Sub

Private Sub m_Application01_WorkbookActivate(ByVal Wb As Workbook)
    m_Count = m_Count   1
    Debug.Print "5. m_Application01_WorkbookActivate: " & Wb.Name & "  " & m_Count
End Sub

Private Sub m_Application01_WorkbookDeactivate(ByVal Wb As Workbook)
    m_Count = m_Count   1
    Debug.Print "6. m_Application01_WorkbookDeactivate: " & Wb.Name & "  " & m_Count
End Sub

'*******************************************************************

CodePudding user response：

My original question is becoming pretty big and unwieldy, so this is kind of question 1b with some additional information.

The goal is to get rid of all of the noise and chatter so that only files that ultimately need to be processed handle events. The way I do this is to have a second WithEvents application object, but this one is only activated if the Excel workbook being processed meets the Child AddIn criteria.

The original SimpleExample01 is recreated as SimpleExample02
Only 4 events are handled by the first Application object, NewSheet, WorkbookOpen, WorkbookBeforeClose and WorkbookActivate
All other events are handled by the second Application object Application02, which is only activated upon request. For purposes of this sample, a dialog will be displayed and that will control whether the current workbook is processed or not.
To keep track of everything in #3 above, a global dictionary MonitoredWorkbooks is maintained where the key is the Workbook and the value is True if the Workbook is to be monitored, or False if it is not being monitored.
When the Application01 WorkbookActivated event is handled, the MonitoredWorkbooks dictionary is checked. If the workbook is to be monitored then Application02 is set, otherwise if the workbook should NOT be monitored, then Application02 is set to nothing so no events will fire.

SimpleExample02

Option Explicit

Private WithEvents m_Application01 As Application

Private WithEvents m_Application02 As Application

Private m_MonitoredWorkbooks As Dictionary

Private m_Count As Long

Private Sub Workbook_Open()
Const sHiddenFileExtensions As String = ",xla,xlam,xlb,xlsb,"
Dim fso As FileSystemObject
    m_Count = 0
    Set fso = New FileSystemObject

    ' Case insensitive dictionary.
    Set m_MonitoredWorkbooks = New Dictionary
    m_MonitoredWorkbooks.CompareMode = TextCompare

    Set m_Application01 = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim vKey As Variant
    If Not m_MonitoredWorkbooks Is Nothing Then
        For Each vKey In m_MonitoredWorkbooks.Keys
            Call m_MonitoredWorkbooks.Remove(vKey)
        Next
    End If
    Set m_Application01 = Nothing: Set m_Application02 = Nothing
End Sub

' Begin Application01 events ************************************************************************************

Private Sub m_Application01_NewWorkbook(ByVal Wb As Workbook)
Dim bMonitor As Boolean
    If (vbYes = MsgBox("Monitor workbook: " & Wb.Name & "?", vbYesNo Or vbQuestion Or vbDefaultButton2, "Monitor Workbook")) Then
        bMonitor = True
        Set m_Application02 = Application
    Else
        Set m_Application02 = Nothing
    End If
    If m_MonitoredWorkbooks.Exists(Wb) Then
        m_MonitoredWorkbooks.Item(Wb) = bMonitor
    Else
        Call m_MonitoredWorkbooks.Add(Wb, bMonitor)
    End If

    m_Count = m_Count   1
    Debug.Print "3. 01 - m_Application01_WorkbookOpen: " & Wb.Name; "  "; m_Count; "  Monitor: "; bMonitor
End Sub

Private Sub m_Application01_WorkbookOpen(ByVal Wb As Workbook)
Dim bMonitor As Boolean
    If (vbYes = MsgBox("Monitor workbook: " & Wb.Name & "?", vbYesNo Or vbQuestion Or vbDefaultButton2, "Monitor Workbook")) Then
        bMonitor = True
        Set m_Application02 = Application
    Else
        Set m_Application02 = Nothing
    End If
    If m_MonitoredWorkbooks.Exists(Wb) Then
        m_MonitoredWorkbooks.Item(Wb) = bMonitor
    Else
        Call m_MonitoredWorkbooks.Add(Wb, bMonitor)
    End If

    m_Count = m_Count   1
    Debug.Print "3. 01 - m_Application01_WorkbookOpen: " & Wb.Name; "  "; m_Count; "  Monitor: "; bMonitor
End Sub

Private Sub m_Application01_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    m_Count = m_Count   1
    Debug.Print "4. 01 - m_Application01_WorkbookBeforeClose: " & Wb.Name; "  "; m_Count
End Sub

Private Sub m_Application01_WorkbookActivate(ByVal Wb As Workbook)
Dim bMonitor As Boolean
    If m_MonitoredWorkbooks.Exists(Wb) Then
        bMonitor = m_MonitoredWorkbooks.Item(Wb)
        If bMonitor Then
            Set m_Application02 = Application
        Else
            Set m_Application02 = Nothing
        End If
    Else
        Call MsgBox("Unexpected result in a", vbOKOnly Or vbCritical, "Fatal Error")
        Debug.Assert False
        End
    End If

    m_Count = m_Count   1
    Debug.Print "3. 01 - m_Application01_WorkbookOpen: " & Wb.Name; "  "; m_Count; "  Monitor: "; bMonitor
End Sub

' End Application01 events ************************************************************************************

' Begin Application02 events ####################################################################################

Private Sub m_Application02_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim oBook As Workbook
    m_Count = m_Count   1
    Set oBook = Sh.Parent
    Debug.Print "      3. 02 - m_Application02_SheetSelectionChange: " & oBook.Name & ", " & Sh.Name & ", " & Target.Address & ", " & Target.Cells.Count; "  "; m_Count
    Set oBook = Nothing
End Sub

Private Sub m_Application02_WorkbookActivate(ByVal Wb As Workbook)
    m_Count = m_Count   1
    Debug.Print "    1. 02 - m_Application02_WorkbookActivate: " & Wb.Name; "  "; m_Count
End Sub

Private Sub m_Application02_WorkbookDeactivate(ByVal Wb As Workbook)
    m_Count = m_Count   1
    Debug.Print "    2. 02 - m_Application02_WorkbookDeactivate: " & Wb.Name; "  "; m_Count
End Sub

' End Application02 events ####################################################################################

CodePudding user response：

My original question is becoming pretty big and unwieldy, so this is kind of question 1c with some additional information.

SimpleExample01 above displays Immediate Window output for every defined event for every workbook, whether or not they are the ones we want to process
This occurs because there is only one WithEvents Application object defined, Application01
SimpleExample02 above incorporates a second WithEvents Application object, Application02 which is controlled by a Global dictionary where the key is the Workbook and the value is a boolean which determines whether or not the Workbook should be monitored - If the Workbook is to be monitored, then Application02 is setup so that Application02 events will be called
This results in a lot less noise and chatter in the Immediate Window since only basic events are called from Application01 and all others are called from Application02

This situation then becomes the basis of my original question which was admittedly pretty sketchy because I wasn't exactly sure what I was asking.

If you look at SimpleExample02 above, everything pertaining to WithEvents Application object Application01 I would like to put in a Base AddIn named EventMonitor. All of the situation specific stuff handled by Application02 I would like to split out into a separate Child AddIn that would have a reference back to the Base AddIn EventMonitor.

The reason I want to do it this way is because Child AddIn requirements differ. In one situation I may be dealing with Pivot Tables, in another something keyed off of SheetSelectionChange or SheetChange, etc. So each separate Child AddIn could have a unique set of Events that it handles and everything would be encapsulated inside the Child AddIn. I apologize that this is so verbose but it is very difficult to make it make sense without the samples to demonstrate the issues.

Thanks for taking time to follow this through.