Open the "Function Arguments" dialog box via VBA

Phox

Well-known Member

Joined

Jul 26, 2004

Messages

522

• Jul 28, 2009

• #2

Try this:

Code:

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.

 

Upvote 0

xld

Banned

Joined

Feb 8, 2003

Messages

5,378

• Jul 28, 2009

• #3

Code:

    Application.SendKeys "%if"

 

Upvote 0

Phox

Well-known Member

Joined

Jul 26, 2004

Messages

522

• Jul 28, 2009

• #4

Ok, its pretty simple apparently. To register function:

Code:

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

 

Upvote 0

C

calisooner

New Member

Joined

Jul 28, 2009

Messages

2

• Jul 28, 2009

• #5

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?

 

Upvote 0

Phox

Well-known Member

Joined

Jul 26, 2004

Messages

522

• Jul 28, 2009

• #6

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.

 

Upvote 0

xld

Banned

Joined

Feb 8, 2003

Messages

5,378

• Jul 29, 2009

• #7

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

Code:

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

not ideal I would suggest, but maybe workable.

 

Upvote 0