I have written an Excel Add-In that adds an item to the
standard Excel menu, with various functions that are
called by the sub-menu items. I last worked on the code in
July 2003, at which time it compiled and executed without
problems. I now need to work on the code again, but it no
longer compiles. When I attempt to compile from the Visual
Basic window, it fails on the first line (Private
mobjExcelBook As Excel.Workbook), with the
message "Compile Error: Automation error". If I comment
out this line, it fails on the next object declaration
line: Private mcolSheets As VBA.Collection. The object
browser shows these objects, and the References are
correct, and unchanged since the previous work that
compiled. (Microsoft Excel 9.0 Object Library, EXCEL9.OLB,
and others)

When I shut down the Add-In, I get another error message
entitled "Microsoft Visual Basic", with the message:
Automation Error, Unspecified Error, and then the VBA
window opens again in debug mode.

Now the clincher: the code actually executes correctly
when I invoke the menu items, and does not throw any
errors!

I am developing on Windows 2000 Professional (5.0.2195 SP-
4), and using Excel 2000 (9.0.3821 SR-1). I open and run
this Add-In not as a selected Add-In in Excel, but by
simply double-clicking the .xla file.

I do not recall installing any other software since the
last time I worked on this code, other than a couple of
Windows updates. I have run Regclean.exe a couple of times
since these errors first appeared, and allowed it to
repair the registry.

The only other hint might be that I wrote a bit of Windows
API code when I was working on the Add-In in July. But at
the time, this compiled and executed correctly too.

Thanks for any help.

Can I assume it doesn't run on other machines too? I'd be willing to try it
if you want but I have no ideas at the moment why this is happening.

--
Jim Rech
Excel MVP