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
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: