Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Show Argument List for a Custom Function

Posted on 2014-01-11
13
Medium Priority
?
1,251 Views
Last Modified: 2014-02-14
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.
0
Comment
Question by:JohnPablo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39774365
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).
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39777445
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
0
 

Author Comment

by:JohnPablo
ID: 39777601
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39777646
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
0
 

Author Comment

by:JohnPablo
ID: 39778371
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.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39778401
Oh, so your looking for a tooltip not for IntelliSense. I'm afraid that is quite impossible.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39783430
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
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39783480
Actually I believe it can be done in VBA using api, but I don't have the time to prove it.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39783489
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
0
 

Author Comment

by:JohnPablo
ID: 39820115
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.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39820133
Simple? No! Possible? Maybe.
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 1500 total points
ID: 39820145
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
0
 

Author Closing Comment

by:JohnPablo
ID: 39859395
Zack's reply isn't a simple solution, but it does the job when there's no other option. Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question