Show Argument List for a Custom Function

Hi experts.

Is there a way I can do this? I know how to show a list of IntelliSense options using Enum statements, but I'd like to add a list of argument options to other users of my custom function who use it for their Excel formulas.

Thanks,
Pablo.
JohnPabloAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe HowardCommented:
I gather that you want all users to be able to use IntelliSense for your custom UDF. If they have access to the function they will have access to IntelliSense too. Just make sure to put your public function and enum in a template that loads when they start Excel. The \XLStart folder is recommended. (to find the correct \XLStart folder, run MsgBox Application.StartupPath).
Zack BarresseCEOCommented:
Hi there Pablo,

Are you talking about a UDF written for use in the IDE, or a worksheet function? As MacroShadow says, so long as they have access to the UDF (i.e. it's in their workbook) they will get the syntax information. It of course depends on how you're deploying this though. If this is an add-in they'll need to have the UDF transferred into the workbook itself, which can be done, although it can get a little tricky.

If you're making a COM add-in it's a different situation. While you can make the COM UDF's visible through traditional means, it's difficult. It'd be easier to use something like Excel DNA (free) or Add-In Express (paid).

In any case, a clearer definition of what you want would help.

Regards,
Zack Barresse
JohnPabloAuthor Commented:
Hi Zack, MacroShadow.

I normally write UDFs for personal use, or to be used along with some other code. In this case, I need to write several UDFs that will be used by other people, and they need to be as straightforward as possible. I'm writing the UDFs i a workbook module, so anyone can see them and use them, but you can't see the argument list, let alone the possible options for each argument.

I just want to know if I'm missing something, or this is just not available for custom functions.

Thanks for your help,
Pablo.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Zack BarresseCEOCommented:
The UDF would need to reside in the file it's being called from to see the syntax. So you'd have to copy it to the destination workbook. Is that what you mean?

Zack
JohnPabloAuthor Commented:
The UDF resides in the same file. The problem is, whenever I try inserting the UDF as a formula, I'm not getting any info on its arguments.
Joe HowardCommented:
Oh, so your looking for a tooltip not for IntelliSense. I'm afraid that is quite impossible.
Zack BarresseCEOCommented:
Not impossible, just difficult. You just can't do it with VBA. You'd need to write this as a managed add-in, using C#/VB, etc. Generally when writing your own UDF's in VBA, training is the best option.

Zack
Joe HowardCommented:
Actually I believe it can be done in VBA using api, but I don't have the time to prove it.
Zack BarresseCEOCommented:
AFAIK that is impossible and I've never seen it done. But if you do get time, I'd certainly love for you to prove me wrong. That'd be pretty cool. ;)

Zack
JohnPabloAuthor Commented:
I'm sorry for leaving this question.

I found a more creative way to allow the users to deal with the arguments that my UDF requires. I offered a set of aliases for each possible value and it seems to be working. nonetheless, I'd like to know if there is a simple solution to my original question.

Thanks,
Pablo.
Joe HowardCommented:
Simple? No! Possible? Maybe.
Zack BarresseCEOCommented:
If you're talking about calling a UDF as a worksheet function, there is a workaround, a couple actually, one of which Jan Karel Pieterse has on his website:

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

It uses the MacroOptions method which I forgot about (and I believe also details out an API and XLM method, but I'd not recommend using the latter, I never had much luck in keeping it stable for others to use).

Zack

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnPabloAuthor Commented:
Zack's reply isn't a simple solution, but it does the job when there's no other option. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.