I have an add-in with custom functions. I'm trying to use MacroOptions to
add descriptions for them in the Insert Function dialog, and to create
custom categories for them, and put them in the custom categories.

It works fine if I just run the code and then look at the Insert Function
dialog. But if I exit and restart Excel (2003), the custom category is gone
and the custom function is listed in the Engineering category (I have no
idea why it gets put there).

I tried putting the code in the Workbook_AddinInstall event, and unchecking
the Add-in from the Add-Ins dialog and then rechecking it. Again, works fine
until I restart Excel, then it's back to the same problem.

I tried the putting it in the Workbook_Open event, but that gives me an
error message about not being able to edit a hidden workbook. I tried
putting it in the main module's auto_open (which otherwise works fine), but
it doesn't seem to do anything there.

Any idea what I need to do differently? My code is like this:

Application.MacroOptions "MyFunction", "This is the description for
MyFunction.", , , , , "My Category"


Thanks,

Greg

Greg

when your workbook has IsAddin = true you must
fully qualify the function's address (include the addin's name)

dont use the addin_install event, that's only triggered when the addin
is activated via the dialog. instead use workbook_open


Private Sub Workbook_Open()
Dim itm, arr
arr = Array("Function1", "Function2")
On Error Resume Next
For Each itm In arr
Application.MacroOptions ThisWorkbook.Name & "!" & itm, Category:="My
Category"
If Err Then Debug.Print "oops:", itm
Next

End Sub

Also note that I've left the descriptive text out of the macro:
To add descriptive text to functions I prefer to add them
via the Object Browser... so they are stored with the code.

Open Object Browser.
Select the project of your addin.
(your addin must be the active project!)

Select <globals
Select the function.., right click and select properties


now add the description
the box is a bit small but never mind.

to enter linebreaks use ctrl+Enter.
Since you cannot add descriptions for arguments, (laurent longre has a
solution for that) you could add some extra text on line 2/3 of the
description.

Note the function wizard allows for 3 lines of text, but in the object
browser (at the bottom) you'll see only 2 lines.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :

I have an add-in with custom functions. I'm trying to use
MacroOptions to add descriptions for them in the Insert Function
dialog, and to create custom categories for them, and put them in the
custom categories.

It works fine if I just run the code and then look at the Insert
Function dialog. But if I exit and restart Excel (2003), the custom
category is gone and the custom function is listed in the Engineering
category (I have no idea why it gets put there).

I tried putting the code in the Workbook_AddinInstall event, and
unchecking the Add-in from the Add-Ins dialog and then rechecking it.
Again, works fine until I restart Excel, then it's back to the same
problem.

I tried the putting it in the Workbook_Open event, but that gives me
an error message about not being able to edit a hidden workbook. I
tried putting it in the main module's auto_open (which otherwise
works fine), but it doesn't seem to do anything there.

Any idea what I need to do differently? My code is like this:

Application.MacroOptions "MyFunction", "This is the description for
MyFunction.", , , , , "My Category"


Thanks,

Greg

Thanks, but I'm still getting the same error running it from workbook_open.
Here's the error:

================================================== ====
Run-time error '1004':
Cannot edit a macro on a hidden workbook. Unhide the workbook using the
unhide command.
================================================== ====

However, I don't see an unhide method. As far as I can tell, the error
message is referring to the Unhide menu item in the Window menu, which isn't
enabled for an add-in. I tried setting DisplayAlerts to False in the
workbook_open, and I tried turning on Trust Access to Visual Basic Project.
Same error.

I tried fully qualifying the function's address and putting it in
addin_install, but I get the same problem as before. It works fine if I
press F5 to run the code from the editor, and if I uncheck and recheck the
addin in the addins dialog, but doesn't stick if I restart Excel. I verified
that the code was being saved -- the code is still there in the
workbook_open event after restarting Excel.

There is one difference, though -- if I run the code from auto_open while
looking at the code in the editor, then it does stick. I thought this was
strange so I tested a few times to be sure -- it only sticks if I do ALL of
the following:
-- Run the code from auto_open.
-- Run the code from the editor (doesn't work when auto_open runs normally
when Excel starts and the addin is loaded).
-- Fully qualify the function address.

Without any one or more of the above conditions, it doesn't stick when I
restart Excel.

That's fine for my machine, but it doesn't help with other poeople who use
these functions on their machines. Any idea what I need to do to get it to
stick without having to bring up the editor and press F5 in the auto_open?

Where is the setting stored? I searched the registry and didn't get a hit.
I'm not using an xlstart.xls. Is it a binary setting in the registry?


Thanks,

Greg



"keepITcool" wrote in message
.com...
Greg

when your workbook has IsAddin = true you must
fully qualify the function's address (include the addin's name)

dont use the addin_install event, that's only triggered when the addin
is activated via the dialog. instead use workbook_open


Private Sub Workbook_Open()
Dim itm, arr
arr = Array("Function1", "Function2")
On Error Resume Next
For Each itm In arr
Application.MacroOptions ThisWorkbook.Name & "!" & itm, Category:="My
Category"
If Err Then Debug.Print "oops:", itm
Next

End Sub

Also note that I've left the descriptive text out of the macro:
To add descriptive text to functions I prefer to add them
via the Object Browser... so they are stored with the code.

Open Object Browser.
Select the project of your addin.
(your addin must be the active project!)

Select <globals
Select the function.., right click and select properties


now add the description
the box is a bit small but never mind.

to enter linebreaks use ctrl+Enter.
Since you cannot add descriptions for arguments, (laurent longre has a
solution for that) you could add some extra text on line 2/3 of the
description.

Note the function wizard allows for 3 lines of text, but in the object
browser (at the bottom) you'll see only 2 lines.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :

I have an add-in with custom functions. I'm trying to use
MacroOptions to add descriptions for them in the Insert Function
dialog, and to create custom categories for them, and put them in the
custom categories.

It works fine if I just run the code and then look at the Insert
Function dialog. But if I exit and restart Excel (2003), the custom
category is gone and the custom function is listed in the Engineering
category (I have no idea why it gets put there).

I tried putting the code in the Workbook_AddinInstall event, and
unchecking the Add-in from the Add-Ins dialog and then rechecking it.
Again, works fine until I restart Excel, then it's back to the same
problem.

I tried the putting it in the Workbook_Open event, but that gives me
an error message about not being able to edit a hidden workbook. I
tried putting it in the main module's auto_open (which otherwise
works fine), but it doesn't seem to do anything there.

Any idea what I need to do differently? My code is like this:

Application.MacroOptions "MyFunction", "This is the description for
MyFunction.", , , , , "My Category"


Thanks,

Greg

I found a way that works, but it seems like going the long way around the
barn. Surely there is an easier way than this?

Here's what I did:

Most of the code for my custom worksheet functions are in a VB6 DLL. I put a
timer on a form in the DLL. In the class_initialize event of the class in
the DLL that the Excel add-in instantiates, I enable the timer and set its
interval. In the timer event, I attempt to do a GetObject on the running
instance of Excel. If not successful, I keep trying at each timer interval
for a while before giving up. If the Get_Object is ever successful, I use
that to run the MacroOptions code.

I'm still fully qualifying the function's address per keepITcool's
suggestion below.

It works fine, but if the user happens to have more than one instance of
Excel running, I might not get the right instance. Eventually I may have to
see if there is a way to iterate through all instances of Excel. Also, if
the user has a lot of startup stuff that takes a long time to run, I'm not
sure if it will prevent Get_Object from working. I don't want it to keep
trying forever.

Is there a more straightforward way to do it than this?


Thanks,

Greg



"Greg Lovern" wrote in message
...
Thanks, but I'm still getting the same error running it from
workbook_open. Here's the error:

================================================== ====
Run-time error '1004':
Cannot edit a macro on a hidden workbook. Unhide the workbook using the
unhide command.
================================================== ====

However, I don't see an unhide method. As far as I can tell, the error
message is referring to the Unhide menu item in the Window menu, which
isn't enabled for an add-in. I tried setting DisplayAlerts to False in the
workbook_open, and I tried turning on Trust Access to Visual Basic
Project. Same error.

I tried fully qualifying the function's address and putting it in
addin_install, but I get the same problem as before. It works fine if I
press F5 to run the code from the editor, and if I uncheck and recheck the
addin in the addins dialog, but doesn't stick if I restart Excel. I
verified that the code was being saved -- the code is still there in the
workbook_open event after restarting Excel.

There is one difference, though -- if I run the code from auto_open while
looking at the code in the editor, then it does stick. I thought this was
strange so I tested a few times to be sure -- it only sticks if I do ALL
of the following:
-- Run the code from auto_open.
-- Run the code from the editor (doesn't work when auto_open runs normally
when Excel starts and the addin is loaded).
-- Fully qualify the function address.

Without any one or more of the above conditions, it doesn't stick when I
restart Excel.

That's fine for my machine, but it doesn't help with other poeople who use
these functions on their machines. Any idea what I need to do to get it to
stick without having to bring up the editor and press F5 in the auto_open?

Where is the setting stored? I searched the registry and didn't get a hit.
I'm not using an xlstart.xls. Is it a binary setting in the registry?


Thanks,

Greg



"keepITcool" wrote in message
.com...
Greg

when your workbook has IsAddin = true you must
fully qualify the function's address (include the addin's name)

dont use the addin_install event, that's only triggered when the addin
is activated via the dialog. instead use workbook_open


Private Sub Workbook_Open()
Dim itm, arr
arr = Array("Function1", "Function2")
On Error Resume Next
For Each itm In arr
Application.MacroOptions ThisWorkbook.Name & "!" & itm, Category:="My
Category"
If Err Then Debug.Print "oops:", itm
Next

End Sub

Also note that I've left the descriptive text out of the macro:
To add descriptive text to functions I prefer to add them
via the Object Browser... so they are stored with the code.

Open Object Browser.
Select the project of your addin.
(your addin must be the active project!)

Select <globals
Select the function.., right click and select properties


now add the description
the box is a bit small but never mind.

to enter linebreaks use ctrl+Enter.
Since you cannot add descriptions for arguments, (laurent longre has a
solution for that) you could add some extra text on line 2/3 of the
description.

Note the function wizard allows for 3 lines of text, but in the object
browser (at the bottom) you'll see only 2 lines.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :

I have an add-in with custom functions. I'm trying to use
MacroOptions to add descriptions for them in the Insert Function
dialog, and to create custom categories for them, and put them in the
custom categories.

It works fine if I just run the code and then look at the Insert
Function dialog. But if I exit and restart Excel (2003), the custom
category is gone and the custom function is listed in the Engineering
category (I have no idea why it gets put there).

I tried putting the code in the Workbook_AddinInstall event, and
unchecking the Add-in from the Add-Ins dialog and then rechecking it.
Again, works fine until I restart Excel, then it's back to the same
problem.

I tried the putting it in the Workbook_Open event, but that gives me
an error message about not being able to edit a hidden workbook. I
tried putting it in the main module's auto_open (which otherwise
works fine), but it doesn't seem to do anything there.

Any idea what I need to do differently? My code is like this:

Application.MacroOptions "MyFunction", "This is the description for
MyFunction.", , , , , "My Category"


Thanks,

Greg

what version(s) of excel do your need to program for?

xlXP (and I think xl2000) expose their hWnd and hInstance
via the application object

also have a look at Laurent Longre's site.
his FunCustomize might be to your liking.
http://xcell05.free.fr/




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :

I found a way that works, but it seems like going the long way around
the barn. Surely there is an easier way than this?

Here's what I did:

Most of the code for my custom worksheet functions are in a VB6 DLL.
I put a timer on a form in the DLL. In the class_initialize event of
the class in the DLL that the Excel add-in instantiates, I enable the
timer and set its interval. In the timer event, I attempt to do a
GetObject on the running instance of Excel. If not successful, I keep
trying at each timer interval for a while before giving up. If the
Get_Object is ever successful, I use that to run the MacroOptions
code.

I'm still fully qualifying the function's address per keepITcool's
suggestion below.

It works fine, but if the user happens to have more than one instance
of Excel running, I might not get the right instance. Eventually I
may have to see if there is a way to iterate through all instances of
Excel. Also, if the user has a lot of startup stuff that takes a long
time to run, I'm not sure if it will prevent Get_Object from working.
I don't want it to keep trying forever.

Is there a more straightforward way to do it than this?


Thanks,

Greg



"Greg Lovern" wrote in message
...
Thanks, but I'm still getting the same error running it from
workbook_open. Here's the error:

================================================== ====
Run-time error '1004':
Cannot edit a macro on a hidden workbook. Unhide the workbook using
the unhide command.
================================================== ====

However, I don't see an unhide method. As far as I can tell, the
error message is referring to the Unhide menu item in the Window
menu, which isn't enabled for an add-in. I tried setting
DisplayAlerts to False in the workbook_open, and I tried turning on
Trust Access to Visual Basic Project. Same error.

I tried fully qualifying the function's address and putting it in
addin_install, but I get the same problem as before. It works fine
if I press F5 to run the code from the editor, and if I uncheck and
recheck the addin in the addins dialog, but doesn't stick if I
restart Excel. I verified that the code was being saved -- the code
is still there in the workbook_open event after restarting Excel.

There is one difference, though -- if I run the code from auto_open
while looking at the code in the editor, then it does stick. I
thought this was strange so I tested a few times to be sure -- it
only sticks if I do ALL of the following:
-- Run the code from auto_open.
-- Run the code from the editor (doesn't work when auto_open runs
normally when Excel starts and the addin is loaded).
-- Fully qualify the function address.

Without any one or more of the above conditions, it doesn't stick
when I restart Excel.

That's fine for my machine, but it doesn't help with other poeople
who use these functions on their machines. Any idea what I need to
do to get it to stick without having to bring up the editor and
press F5 in the auto_open?

Where is the setting stored? I searched the registry and didn't get
a hit. I'm not using an xlstart.xls. Is it a binary setting in the
registry?


Thanks,

Greg



"keepITcool" wrote in message
.com...
Greg

when your workbook has IsAddin = true you must
fully qualify the function's address (include the addin's name)

dont use the addin_install event, that's only triggered when the
addin is activated via the dialog. instead use workbook_open


Private Sub Workbook_Open()
Dim itm, arr
arr = Array("Function1", "Function2")
On Error Resume Next
For Each itm In arr
Application.MacroOptions ThisWorkbook.Name & "!" & itm,
Category:="My Category"
If Err Then Debug.Print "oops:", itm
Next

End Sub

Also note that I've left the descriptive text out of the macro:
To add descriptive text to functions I prefer to add them
via the Object Browser... so they are stored with the code.

Open Object Browser.
Select the project of your addin.
(your addin must be the active project!)

Select <globals
Select the function.., right click and select properties


now add the description
the box is a bit small but never mind.

to enter linebreaks use ctrl+Enter.
Since you cannot add descriptions for arguments, (laurent longre
has a solution for that) you could add some extra text on line 2/3
of the description.

Note the function wizard allows for 3 lines of text, but in the
object browser (at the bottom) you'll see only 2 lines.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :

I have an add-in with custom functions. I'm trying to use
MacroOptions to add descriptions for them in the Insert Function
dialog, and to create custom categories for them, and put them in
the custom categories.

It works fine if I just run the code and then look at the Insert
Function dialog. But if I exit and restart Excel (2003), the
custom category is gone and the custom function is listed in the
Engineering category (I have no idea why it gets put there).

I tried putting the code in the Workbook_AddinInstall event, and
unchecking the Add-in from the Add-Ins dialog and then rechecking
it. Again, works fine until I restart Excel, then it's back to
the same problem.

I tried the putting it in the Workbook_Open event, but that gives
me an error message about not being able to edit a hidden
workbook. I tried putting it in the main module's auto_open
(which otherwise works fine), but it doesn't seem to do anything
there.

Any idea what I need to do differently? My code is like this:

Application.MacroOptions "MyFunction", "This is the description
for MyFunction.", , , , , "My Category"


Thanks,

Greg

Thanks, but I'm not sure how knowing the hWnd and hInstance of the instance
I got with GetObject will help me get a different instance with GetObject.

I could use the Win32API to iterate through hWnds and identify the one or
ones I want, but even when I know which ones I want, I'm not sure how to do
a GetObject on those specific instances. As far as I can tell, GetObject
only ever returns the instance that was started first.

FunCustomize looks interesting. Since it does more than MacroOptions allows,
I guess he's doing it with the Win32 API rather than through VBA. I'll keep
that in mind for future reference, but I would have to take the time to
figure out how to do it myself rather than redistribute his DLL, and there
are higher priorities at the moment.

What I'm doing for now is checking the installed add-ins of the instance I
get with GetObject, and running the code only if my add-in is installed in
that instance. Worst case is that my functions end up in the User Defined
category, with no descriptions, and the user has to go to my function
reference helpfile to get more information.

Thanks,

Greg



"keepITcool" wrote in message
.com...


what version(s) of excel do your need to program for?

xlXP (and I think xl2000) expose their hWnd and hInstance
via the application object

also have a look at Laurent Longre's site.
his FunCustomize might be to your liking.
http://xcell05.free.fr/




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg Lovern wrote :

I found a way that works, but it seems like going the long way around
the barn. Surely there is an easier way than this?

Here's what I did:

Most of the code for my custom worksheet functions are in a VB6 DLL.
I put a timer on a form in the DLL. In the class_initialize event of
the class in the DLL that the Excel add-in instantiates, I enable the
timer and set its interval. In the timer event, I attempt to do a
GetObject on the running instance of Excel. If not successful, I keep
trying at each timer interval for a while before giving up. If the
Get_Object is ever successful, I use that to run the MacroOptions
code.

I'm still fully qualifying the function's address per keepITcool's
suggestion below.

It works fine, but if the user happens to have more than one instance
of Excel running, I might not get the right instance. Eventually I
may have to see if there is a way to iterate through all instances of
Excel. Also, if the user has a lot of startup stuff that takes a long
time to run, I'm not sure if it will prevent Get_Object from working.
I don't want it to keep trying forever.

Is there a more straightforward way to do it than this?