Open the "Function Arguments" dialog box via VBA

[calisooner]

I'm hoping to open the Function Arguments dialog box via vba for a user-defined function. I couldn't find any reference to this dialog box (or to the Insert Function dialog box, for that matter) on Microsoft's support site. I'm using Excel 2003. Thanks in advance for any insight.

 

[Phox]

Try this:

Range("Your Range").FunctionWizard

Edit: Ah, I just reread and didnt notice you wanted it to be a user-defined function. In order for this to work, you need to register the function with the function library. I've never done this, but you at least have some new terms to search for.

 

[xld]

    Application.SendKeys "%if"

 

[Phox]

Ok, its pretty simple apparently. To register function:

Application.MacroOptions Macro:="YourMacro", Description:="Description of your macro", Category:=9

The category number refers to the type of macro it is, with the following options being available:
0 - All
1 - Financial
2 - Date & Time
3 - Math & Trig
4 - Statistical
5 - Lookup & Reference
6 - Database
7 - Text
8 - Logical
9 - Information

 

[calisooner]

Thanks for the quick replies...Unless I'm misinterpreting the guidance, why would I need to register the UDF? It already shows up in the "User Defined" category. While the .FunctionWizard code opens the Insert Function dialog box, I was hoping to open the Function Arguments dialog box for a specific UDF, which already shows up in the functions list. Any thoughts on whether this is doable?

 

[Phox]

I'm not sure if that is a native capability or not. After a little searching, I was able to find this site which offers a downloadable addin, but I haven't used it so no recommendations.

 

[xld]

You don't need to register it unless you want your function in a specific category, which I don't recall you asking.

What you could try is to build that formula in the cell and then call function wizard, something like

Activecell.Formula = "INDEX(A1:A2,1)"
Activecell.FunctionWizard

not ideal I would suggest, but maybe workable.