Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1343
  • Last Modified:

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.
0
JohnPablo
Asked:
JohnPablo
  • 5
  • 4
  • 4
1 Solution
 
MacroShadowCommented:
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
 
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
0
 
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
0
 
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.
0
 
MacroShadowCommented:
Oh, so your looking for a tooltip not for IntelliSense. I'm afraid that is quite impossible.
0
 
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
0
 
MacroShadowCommented:
Actually I believe it can be done in VBA using api, but I don't have the time to prove it.
0
 
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
0
 
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.
0
 
MacroShadowCommented:
Simple? No! Possible? Maybe.
0
 
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
0
 
JohnPabloAuthor Commented:
Zack's reply isn't a simple solution, but it does the job when there's no other option. Thanks!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now