Excel Application.MacroOptions and libraries references problems

I created an add-in for excel that contains several user defined functions.

The add-in is meant to be distributed among a wide range of users so that they have access to the udfs. In the add-in I use the following statement to add each of the udfs to a new category under the Insert Function Menu:

Application.MacroOptions macro:="'add-in name.xla'!FunctionName", _
Description:="Function Description.", _
Category:="Company Functions"

I created the add-in using excel 2003 on an XP platform and it works well under the XP platform (and on any XP computer). However, when I install the add-in into an excel 2003 running on Windows 2000 platform, I get quite a few errors (one is more serious than the others):

1) the most important one is that I get an error for the
Application.MacroOptions statement above I get a:
"Run-time error '1004'; Method 'MacroOptions' of object '_Application' failed". I have checked the macro security options and they are low, also the "trust all installed add-ins and templates" and the "trust access to Visual Basic Project" options are checked. A workbook is opened and visible. The MacroOptions statements are written under the Workbook_AddinInstall event.

If I comment out the MacroOptions statements, I loose the new category name and therefore the company udfs show under "User Defined" which is not what I am trying to accomplish.

I tried to work around the problem by automatically inserting a macro sheet, then adding a dummy macro name, adding the category that I need, and finally deleting the macro sheet. This option works, except that I end up with a dummy function in the list that doesn't do anything. It therefore creates a new problem.

If I automatically delete that dummy function, after deleting the macro sheet. The Insert Function list still keeps a reference to it and so it crashes Excel if the user happens to click on that name. I therefore tried naming the dummy function as something that I already have.

When I named the dummy function as a function that I already have, it then creates two instances of the function in the "Insert Function" list and one of the instances has no code behind it.

Is there a way that I can get the udfs added to the "Company Functions" category in the Insert Menu for the Windows 2000 users

As I mentioned before, item #1 is the most important item. I have found a workaround to the following item but I will mention it just in case it is related to the problem I'm having on item #1.

2) As soon as I click to add the the add-in, I get the following message: "Can't find project or library". And it looses its reference to the following three libraries:

- Microsoft Windows Common Controls 6.0 (SP6)
C:\WINNT\System32\MSCOMCTL.OCX

- Microsoft Windows Common Controls-2 6.0 (SP4)
C:\WINNT\System32\Mscomct2.ocx

- MISSING: Microsoft ADO Ext 2.8 for DDL and Security
C:\Program Files\Common Files\System\ado\msadox.dll

The files exist under the paths indicated, however it seems to not being able to find them. I ran all the available service packs (windows and microsoft office) and the problem wasn't fixed. I also made sure that the libraries were registered but that didn't help the problem.

I therefore resolved this problem by unregistering and deleting the current files, then copying the files from the XP computer and then registering them (on the Windows 2000 computer). And this solves the problem, however i don't know if item #1 and item #2 problems are related (even after the add-in has references to the same libraries).

Thank you for any input you can give on this,
Alex

Microsoft ISV Community Center Forums3

Hello Alex,

I tried this out and got a 1004 runtime error like yourself, the MacroOption was in the Workbook open event. I moved the MacroOption code from the workbook to a module and ran it there, and the runtime error never happened.

Try moving all the Application.MacroOption calls to a function in a Module and call that function from the Workbook_AddinInstall event.

I tried several things and I found out that it really doesn't have to do with the Windows version but it actually has to do with the Office Version, this is what I found:

The add-in works fine with the application.macroOptions on machines that have excel version 11 (which is the version in which it was created).

I had to make a version for excel 10 that adds the UDFs by adding a dummy macro sheet and creates a dummy UDF. Moreover, I had to copy the following files into the system and register them.

- Microsoft Windows Common Controls 6.0 (SP6)
C:\WINNT\System32\MSCOMCTL.OCX

- Microsoft Windows Common Controls-2 6.0 (SP4)
C:\WINNT\System32\Mscomct2.ocx

- MISSING: Microsoft ADO Ext 2.8 for DDL and Security
C:\Program Files\Common Files\System\ado\msadox.dll

after that, it works fine.

thanks for your help.