How to create separate instance of Add-in for new Workbook?

[aeroshanks]

Hi,

VBA version: 6.0.
OS: Win 2000

I have an Add-in which uses XML. The add-in has different user forms to
manipulate the XML data. Assume I have just one Workbook open (WB_1).
Now when I create a new Workbook (WB_2), WB_2 uses the SAME instance of
the add-in.

That means if I make some changes in a user form in WB_1's Add-in,
these changes are reflected in the same user form opened from WB_2's
Add-in.

1. Why is there a same copy of the Add-in for both Workbooks??
2. How can I prevent this from happening? I would like WB_1 and WB_2 to
have their own respective copies of my add-in.

(Instead of creating WB_2, had I opened a new instance of Excel itself,
there's no such problem. WB_1 in the first instance of Excel is totally
independent of the workbook in the second instance of Excel.)

Thanks in advance,
Shankar

 

[Mike Woodhouse]

Hi,

VBA version: 6.0.
OS: Win 2000

I have an Add-in which uses XML. The add-in has different user forms to
manipulate the XML data. Assume I have just one Workbook open (WB_1).
Now when I create a new Workbook (WB_2), WB_2 uses the SAME instance of
the add-in.

That looks about right.

That means if I make some changes in a user form in WB_1's Add-in,
these changes are reflected in the same user form opened from WB_2's
Add-in.
Correct.

1. Why is there a same copy of the Add-in for both Workbooks??

Because Add-ins apply to the instance of Excel, not to Workbooks. I
imagine this is by design.

2. How can I prevent this from happening? I would like WB_1 and WB_2 to
have their own respective copies of my add-in.

Move all the workbook-specific data into a class (representing the
"state"), then maintain a separate instance of the class for each
workbook as necessary. I would look at keeping a Collection of such
objects, keyed by Workbook.Name, so the add-in can ask for the correct
state object for the workbook.

(Instead of creating WB_2, had I opened a new instance of Excel itself,
there's no such problem. WB_1 in the first instance of Excel is totally
independent of the workbook in the second instance of Excel.)

Well, yes. Each instance of Excel has its own process space into which
the add-in has loaded.

HTH,

Mike

 

[aeroshanks]

Thanks, Mike.
I'll try out what you've said and see if that solves my problem.
Shankar