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

On Nov 14, 7:11 am, aerosha...@gmail.com wrote:
> 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

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


Mike Woodhouse wrote:
> On Nov 14, 7:11 am, aerosha...@gmail.com wrote:
> > 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