none
Why error assigning to range.Value in excel DNA add-in? RRS feed

  • Question

  • Running a simple function in an excel DNA add-in. Function gets the Range object of the current cell and assigns a value to the .Value property of the Range.  Problem is, assigning to the Value property of the Range object throws an error. Why the exception? 

    I am hoping to write an ExcelFunction to be able to insert the result set of an sql procedure call at the active cell of the spreadsheet.

    here is the code.  thanks,

        [ExcelFunction(Description = "excel DNA demo function")]
        public static string DemoFunc()
        {
          string rv = null;
          dynamic xlApp = ExcelDna.Integration.ExcelDnaUtil.Application;
    
          dynamic c1 = xlApp.ActiveCell;
          MessageBox.Show("Got active cell");
    
          dynamic c2 = c1.Offset(0, 1);
          MessageBox.Show("got offset from active cell");
    
          try
          {
            c2.Value = "abc";
            rv = "Function worked.";
          }
          catch (Exception excp)
          {
            rv = excp.ToString();
            MessageBox.Show("Value property. got error:" + excp.ToString());
          }
          return rv;
        }
    

    Friday, January 23, 2015 10:55 PM

Answers

  • Functions used in Excel worksheet formulas can only return values to the cell(s) that contain the formula, so you will get an error when you try to return a values to a different cell.

    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    • Marked as answer by Steve Richter Monday, January 26, 2015 2:05 PM
    Monday, January 26, 2015 8:49 AM

All replies

  • Hi Steve,

    As far as I know Excel-DNA is an opensource library, it's not a product of Microsoft, and we don't provide support for this opensource library. I recommend that you post your question in the discussion site of this opensource library:

    http://exceldna.codeplex.com/discussions

    or goto the support channel suggested in the official site here:

    http://excel-dna.net/support/

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 26, 2015 6:44 AM
    Moderator
  • Functions used in Excel worksheet formulas can only return values to the cell(s) that contain the formula, so you will get an error when you try to return a values to a different cell.

    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    • Marked as answer by Steve Richter Monday, January 26, 2015 2:05 PM
    Monday, January 26, 2015 8:49 AM