excelexcel-addins

Excel Add-In behavior with multiple open spreadsheets


I have not been able to find an answer to this question. Maybe I'm using the wrong search terms.

If I have multiple spreadsheets open that use the same Excel Add-In does each spreadsheet get its own instance of the Add-In so that data saved in variables by the Add-In from one workbook are separate from data saved by the Add-In from another workbook?

Expect data stored to be separate.

Don


Solution

  • As per my comment, below you can see a simple code in test_add_in which shows hosting process' id:

    Option Explicit
    
    Declare Function GetCurrentProcessId Lib "kernel32" () As Long
    
    Sub test_addin_prodId()
    Dim a As AddIn
    Dim w As Workbook
    
    
    On Error Resume Next
    Set w = ActiveWorkbook
    On Error GoTo 0
    
        If w Is Nothing Then
            Debug.Print "No workbook is active"
        Else
            Debug.Print ActiveWorkbook.Name
        End If
    
    For Each a In Application.AddIns
        Debug.Print "Add-in name: " & a.Name & ", process Id: " & GetCurrentProcessId
    Next
    
    End Sub
    

    And on a screenshot you may see the result in two different instances of an excel application: enter image description here

    I think, that this example may confirm my assumption regarding the fact that different instances of an Excel application host different instances of an add-in, which in turn, answers your question:

    each spreadsheet get its own instance of the Add-In so that data saved in variables by the Add-In from one workbook are separate from data saved by the Add-In from another workbook?

    No, each workbook doesn't get an instance of an add-in, but each instance of an excel application does despite the number of open workbooks:

    First application instance (on the right), two workbooks open: enter image description here

    Second application instance (on the left), no open workbooks: enter image description here