Excel Reference To Addin

[AA2K5]

I've got a really bizzare problem when adding a reference to an Excel addin.

I've got an Excel workbook just over 1MB in size. It contains images, graphs and several VBA code modules.

This workbook has a reference set to an Excel Addin which is about 2MB in size and contains common code used in various places. The addin is installed and therefore loads automatically when Excel is started.

If I don't compile the workbook after adding the reference to the addin, everything seems to work fine. However, if after adding the reference I do compile the VBA project of the workbook and save the workbook I encounter a very weird problem.

If I then open the workbook and close Excel immediately by clicking on the X icon or File->Exit, Excel hangs. This only happens if the reference to the addin has been compiled. If I open the workbook and close only the workbook and not Excel, Excel does not hang.

I'm using Excel 2002(SP3).

I think Excel may be attempting to do something to the addin, maybe close it, and is encountering problems. However I don't see how the reference being compiled brings about this issue.

I know this is an obscure problem so I'd be surprised if anyone knew anything about it but I'd appreciate any insight anyone might have into this issue.

Thanks

AA

 

[AccordingToDale]

Are you making any references in you code to this addin? And if so, are you cleaning up?

e.g.

dim o as object
set o = new object

...do whatever

o.close
set o = nothing

d

 

[AA2K5]

I am making references to user defined types that I've declared in the addin and I don't think I'm cleaning up properly so I'll give that a try when I get the chance.

I'm fairly new when it comes to using objects in VBA so it's probably bad programming on my part.

I just thought it was weird that the problem only occurred when I closed the instance of Excel and didn't occur when I closed the actual workbook. I suppose it may be trying to close the addin first and the external references may stop it from being closed cleanly.

Thanks for the tip. I'll let you know how I get on.

 

[AA2K5]

I've tried cleaning up object references but unfortunately this hasn't solved the problem.

I've cleaned up any local object declarations i.e. range, worksheet, addin objects etc. I'm not using any objects from my addin but I am using user-defined types i.e Public Type NewType ...

I don't how to clean up after these types because I don't think they are objects.

Does anyone have any clues as to what might be causing this?

Thanks for the help