SOLVED

Custom excel formula, add helper pane visual basic editor to show order of inputs

Copper Contributor

When making a custom excel formula, how do you add a helper pane in the excel visual basic editor to show which variable to enter when you are using the excel function.

 

See Below:

 

OfficerKD637_0-1704118606672.png

 

I want to be able to see the names of the inputs in the order I enter them.

2 Replies

@OfficerKD6-37 

Let's say you create a UDF MyFunc in the Visual Basic Editor:

Function MyFunc(AName As String, ANumber As Long) As Variant
    ... ' your code goes here
End Function

In a cell, type =MyFunc

Press Ctrl+Shift+A.

HansVogelaar_0-1704119486277.png

best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@OfficerKD6-37 

You can even create a description for the function and its arguments, by creating and running a macro:

Sub DescribeMyFunc()
    Application.MacroOptions _
        Macro:="MyFunc", _
        Description:="A custom VBA function", _
        ArgumentDescriptions:=Array("A text string", "A whole number")
End Sub

The descriptions will be used in the Insert Function wizard:

HansVogelaar_0-1704119815421.png

HansVogelaar_1-1704119879031.png

1 best response

Accepted Solutions
best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@OfficerKD6-37 

You can even create a description for the function and its arguments, by creating and running a macro:

Sub DescribeMyFunc()
    Application.MacroOptions _
        Macro:="MyFunc", _
        Description:="A custom VBA function", _
        ArgumentDescriptions:=Array("A text string", "A whole number")
End Sub

The descriptions will be used in the Insert Function wizard:

HansVogelaar_0-1704119815421.png

HansVogelaar_1-1704119879031.png

View solution in original post