Here's an example. You will need a command (not a pure function) to open a form. This example makes a menu item which does it.
You have a few options. You can evaluate the function in the command. Or you can put the function into your sheet, along with the params.
[ExcelFunction(Name="MyFunc")]
public static string MyFunc(int x, double y) {
return x+" "+y;
}
[ExcelCommand(Name="Test.Form", MenuName="Test", MenuText="Form")]
public static void TestForm() {
// show the form
var response=MessageBox.Show("Want to proceed?", "", MessageBoxButtons.YesNo);
if(response!=DialogResult.Yes) {
return;
}
// pretend we pull these out of the form
var x=5;
var y=6.7;
var result=XlCall.Excel(XlCall.xlUDF, "MyFunc", x, y);
MessageBox.Show(string.Format("Got [{0}]", result), "", MessageBoxButtons.OK);
dynamic app=ExcelDnaUtil.Application;
app.Worksheets["Sheet1"].Cells[1, 1].Value2=result;
app.Worksheets["Sheet1"].Cells[2, 1].FormulaR1C1=string.Format("=MyFunc({0},{1})", x, y);
app.Worksheets["Sheet1"].Cells[3, 1].Value2=x;
app.Worksheets["Sheet1"].Cells[3, 2].Value2=y;
app.Worksheets["Sheet1"].Cells[4, 1].FormulaR1C1="=MyFunc(R3C1,R3C2)";
}