Excel Add-In behavior with multiple open spreadsheets

Question

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

Answer 1

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:

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:

Second application instance (on the left), no open workbooks: