I have a silly test automation server written in C# with just one class and one method in the class: double => Foo(double). In its constructor and destructor I pop a message box informing me of the hash code of the instance that was just instantiated or destroyed. That's the easy part. Now, in VBA I have this in Modules / Module1:
[vba]
Public Test1Impl As Test1
Function VBFoo(X As Double)
VBFoo = Test1Impl.Foo(X)
End Function
[/vba] while in Microsoft Excel Objects / ThisWorkbook I have
[vba]
Private Sub Workbook_Open()
Set Test1Impl = New Test1
End Sub
[/vba] Finally in the spreadsheet itself I have 2 cells with =Foo(10) and =VBFoo(10). My understanding is that the VBA code above should result in just one copy of the automation server being instantiated but I get two, exactly as if I had coded my VBA in the naive way, i.e.,
[vba]
Dim Test1Impl As New Test1
Function VBFoo(X As Double)
VBFoo = Test1Impl.Foo(X)
End Function
[/vba] I don't understand what is going on. What happens when you call Foo directly vs. through VB: what's the life cycle in each case and why don't they (apparently) share an instance?