A
Alex281
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 direction you can give on this,
Alex
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 direction you can give on this,
Alex