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