Create a form to take user input and insert into Excel

[Charlie]

I've been using Excel DNA and it's been working super for me in creating an XLL add-in. Everything I've done up to now has been either with the ribbon or in a UDF and been done in C#.

I figure this must have been asked already, but I just can't find an example (or don't recognize it when I see it). Oh, and I want this to remain an application-level addin and not be a document-level.

I want to next create a dialog box or form to collect some user input. With that, I want to call one of my UDFs and take that result and put it in a cell in Excel. I'm just at a loss at this point on how to connect the dots.

I have a basic form (Form1.cs) and am just not sure what to do next. 

Is there a simple sample out there that can help get me over the hump?

If this can be done in C# I'd prefer that since I've never used VB/VBA.

Many thanks!

Charlie

[Naju Mancheril]

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)";
    }

[Charlie]

Thanks for the ideas. I'm able to get the form to pop when a ribbon button is clicked. I put a text box and a button on them form, and am stuck on calling my UDF.

The form's button function is super simple and looks like:

        private void Button_Click(object sender, EventArgs e)

        {

            var allcaps = XlCall.Excel(XlCall.xlUDF, "MakeAllcaps", textBox1.Text);

            MessageBox.Show(allcaps.ToString());

        }

The error I get is an "AccessViolationException was unhandled by user code" with "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." as the message when I click the button.

My button function is in my Form1.cs file. And my UDF is in my Class1.cs file.

I'm sure I'm missing something basic here.

[Charlie]

I've found something that seems to work, but I don't know why I need to do this. I added a function that calls the function I want. So I wind up with something like the following.

private string MakeAllcaps2(string str1)

{

     return MakeAllcaps(str1);

} 

 

  

private void Button_Click(object sender, EventArgs e)

{

    // var allcaps = XlCall.Excel(XlCall.xlUDF, "MakeAllcaps", textBox1.Text);

    MessageBox.Show(MakeAllcaps(textBox1.Text));

}

[Lamon]

Charlie, ngm,

 

The reason ngm's example works is because the UDF is called from within a DNA macro.  As you are showing in your first trial, when the call is made from a different "process/thread" (a form, a custom task pane), you cannot get access to the DNA "process/thread" and thus, getting an access violation exception.

 

I had the same issue a while back in a CTP(created with DNA code).  I was unable to connect to Excel using DNA code.  The solution was to use the COM object either directly or using dynamic variable.

 

That being said I am facing a new issue with respect to the above subject.  My DNA project need to handle events including the "delete" event.  The ON.ENTRY event monitors only cell entry.  The solution (see my latest thread) is to use COM events monitoring capabilities:

xlApp = ExcelDnaUtil.Application;

xlApp.SheetChange += new XL.AppEvents_SheetChangeEventHandler(FunctionAI.OnSheetChangeEvent);

 

Within my "OnSheetChangeEvent" I am unable to connect to Excel using DNS.  I am getting an access violation exception.  I found no solution yet, still working on It.  I hope I can shed some information on the issue.

 

Guy

[harit...@gmail.com]

Hi Charlie,

I am new to Excel DNA. 

I am working on similar feature. I have added a new tab (Reports) Ribbon button(Load Exposure Report). When User clicks on this, I need to open a Form.

Since this is Class Library Project, how and where did you create that Form1.

I posted my question here:

https://groups.google.com/forum/#!topic/exceldna/sSw3kNHUNBM 

Appreciate your help.

Thanks