Open Function Dialog in VBA

[Jon von der Heyden]

Hi

Does anybody know how to open the function dialog on a specific worksheet function?

I have a few UDFs and I would like to create a button that will open the dialog on a specific UDF so that the required arguments can be better seen. Almost as though to function as a wizard.

I am familar with this code:

Application.Dialogs(xlDialogFunctionWizard).Show

But I want some adaptation to navigate to UDF, e.g. EVA.

Regards
Jon

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    ActiveCell.Formula = "=AddUp(A1,A2)"
    Application.CommandBars("Standard").Controls("Paste Function").Execute
End Sub

Function AddUp(Val1 As Variant, Val2 As Variant) As Variant
    AddUp = Val1 + Val2
End Function

 

[Jon von der Heyden]

Hi Andrew

Thanks for this. Still having a problem though, grateful for some further help.

Using your example this produces a run-time error on:

Application.CommandBars("Standard").Controls("Paste Function").Execute

Invalid procedure call or argument.

Also, I notice this code populates the arguments. I am wondering if it is possible to leave blank so that it can be populated by the user when the function dialog pops up.

Many thanks
Jon

 

[Jon von der Heyden]

Open Function Dialog in VBA (Solved)

Hi Andrew

You are a star. I have this working with one small adaptation:

Sub Test()
    ActiveCell.Formula = "=AddUp()"
    Application.Dialogs(xlDialogFunctionWizard).Show
End Sub

Function AddUp(Val1 As Variant, Val2 As Variant) As Variant
    AddUp = Val1 + Val2
End Function

Many thanks for your help.
Jon