CODEWITHSUNDEEP
c#excelvstooffice-interopxll
Johan Ahlqvist
Johan Ahlqvist

Reputation: 346

Excel VSTO 2016 - Show function arguments dialog for UDF

I have a XLL library with UDFs used in Excel. Users can use the UDFs by either directly typing the UDF name in a cell, or by launching the Function Wizard Dialog and search for the specific UDFs, which in turn will launch the Function Argument Dialog for that specific UDF.

I'm now trying to create a Ribbon with MSVS2017/C#/Office-Interop/VSTO which show Gallery Controls with drop down menus containing all UDFs as items. When a user clicks on any of the UDF items in the drop down, the Function Arguments Dialog should be displayed for that UDF.

How do you show the Function Arguments Dialog for a specific UDF using Office-Interop/C#/VSTO, or even for any built in function?

I have found very little on the topic after doing some googling. This question is spot on, but the solution code doesn't work.

I found this documentation on the Office Interop namespaces, and more precisely this on different built-in dialogs. But I can't find anything about the Function Arguments Dialog there.

It seems to be quite easy to launch the Function Wizard Dialog. It can be done with this:

Globals.ThisAddIn.Application.Dialogs[Excel.XlBuiltInDialog.xlDialogFunctionWizard].Show();

I hope you can open the Function Arguments Dialog for any UDF with a similar call. Do I have to give up trying Office-Interop and use a Excel C Api call? Or should I try some 3rd party tool like Excel-DNA or Add-in Express?

Upvotes: 1

Views: 560

Answers (1)

Johan Ahlqvist
Johan Ahlqvist

Reputation: 346

Finally found the solution! You have to populate the active cell with a valid UDF formula before you call the Function Dialog Wizard, then it shows the Function Arguments Dialog!

This VBA code gave me a hint.

So the following C# code works:

Globals.ThisAddIn.Application.ActiveCell.Formula = "=zAirAtmTPFC()";
Globals.ThisAddIn.Application.Dialogs[Excel.XlBuiltInDialog.xlDialogFunctionWizard].Show();

The zAirAtmTPFC() is an UDF function in an XLL.

Upvotes: 1

Related Questions