Error Autoloading Addin with UDFs

[Guest]

I have an addin which contains several user defined functions. I am using
the Application.MacroOptions command to assign the functions to custom
categories. An example is:

Application.MacroOptions Macro:="BaPeq", Category:="Miscellaneous Functions"

The addin installs without any problems when loading the addin with Excel
already opened using Tools…Addins. But when I try to have Excel autoload the
addin every time Excel starts the I get the following error message:

Run-time error ‘1004’
Cannot edit a macro on a hidden workbook. Unhide the workbook using the
unhide command.

Any suggestions on how to avoid this error message?

 

[Guest]

You can't change macro properties in hidden workbook.
There are several ways to solve your problem.
1. Set IsAddin property of your add-in workbook to off,
then using Object Browser edit macro properties, set IsAddin to on again,
and save it.
2. Use FunCustomize.dll, which is freely available via Internet. It is very
easy and convenient tool to add description and category to you UDFs(sub or
functions)

 

[Guest]

I found a solution to my problem which I never have seemed documented
anywhere. You can not access the Addin Manager in Excel unless you have a
work book opened. That is why my addin works when I load it using Tools -
Addin but does not work when I try and open Excel with the Addin
preinstalled. To get around this problem you can use the Workbooks.Add
command to create a workbook before Excel loads the Addin.

 

[Peter T]

There are quite a few settings that require a visible workbook, your
MacroOptions is indeed one of these. If you need to run code in an installed
addin's open event that needs a visible workbook try running from the
Workbook_Open event. I find this runs after a normal visible workbook has
loaded which typically occurs when starting Excel. The Auto_Open routine of
an installed addin runs before any normal workbook loads.

Not sure why but the order of these open events is reversed for
non-installed addins and workbooks.

An alternative is to call the routine with the OnTime method, to run after a
normal workbook has loaded. There are scenarios when no normal workbook will
be loaded so the routine would still require an error handler.

Regards,
Peter T