Is it possible to make a custom function behave like a built in on

[Magritte]

I set up some custom functions in Excel 2007 and saved the workbook as an
addin, then tried both installing the add-in through the add-in manager or
simply sticking it in the XLStart folder.

In either case the functions work fine if I just type the function name or
pick it form the user defined functions category in the function wizard.

However, the dropdown with the autocomplete list of available functions
doesn't show the custom functions, nor is there a popup showing the argument
list as you type the function, the way built in functions work.

Worse is that when I use a function in an expression, after re-opening the
spreadsheet, Excel seems to expand the function name to include the full path
of the add-in file from which it comes so instead of custfun() I get
something like 'c:\program files
(x86)\Office12\XLSTART\myfunctions.xlam'!custfun(). This is problematic
because when I sync the file to another computer, where the add-in is
installed in a different location, the function fails. I need to do a search
replace to remove the path from the function name in order to get the
functions to work again.

Is there any method of getting my custom functions to behave like normal
built in functions to fix the issues above?

Also, is there any relatively easy way to add help information that would
appear n the Function Wizard dialogs?

Thanks!

 

[Peter T]

AFAIK there's no way to get the popup as you type but all the rest can be
included

http://www.jkp-ads.com/Articles/RegisterUDF00.asp

Unless you are very familiar with VBA I suggest you follow the template
exactly, though change the "helper" functions if you understand that aspect.

If basic information is enough (ie without individual argument details) -
select your project in Object browser
select the module that contains your functions
rt-click the function
select properties
add your information in the "description" box

You will normally get the full path if the addin is not loaded or the path
to the addin is different to that saved with the file. If you use the file
in a different system you may need to re-link to the different location of
the addin.

Regards,
Peter T

 

[Magritte]

Thanks for the info and link. I'll give it a try.