Excel function throws exception when set by code behind. Works when used in excel

Question

I've defined my own Excel function (called ADXExcelFunctionDeescriptor). The method stub looks like following:

public static object ExecuteMyFunction(object values, object tagName)
{ // Some code here }

The method receives an array of double values and a string, called name.
In the design view my ADXExcelFunctionDeescriptor looks like following:

enter image description here

I call and set the function by the following lines of code:

var formula = string.Format(@"={0}({1};{2})", Temp.FORMULA_NAME, this.DataRangeTextBox.Text, tagCaption);
resultRange.set_Value(Type.Missing, formula);
resultRange.Formula = resultRange.Value;

This will result in an exception! The exception looks like the following:

System.Runtime.InteropServices.COMException occurred
  HResult=-2146827284
  Message=Ausnahme von HRESULT: 0x800A03EC
  Source=""
  ErrorCode=-2146827284
  StackTrace:
       bei System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       bei Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object )
       bei bb.ExcelToolbar.Controls.bbControl.ApplyFormula(Object sender, EventArgs e) in c:\xx\yy\zz\bb\bb.ExcelToolbar\Controls\bbControlcs:Zeile 88.
  InnerException:

Further, if I don't pass the tagName parameter the function returns a result without any exception or error.

var formula = string.Format(@"={0}({1})", Temp.FORMULA_NAME, this.DataRangeTextBox.Text, tagCaption);
resultRange.set_Value(Type.Missing, formula);
resultRange.Formula = resultRange.Value;

So I think it has something to do with the string parameter. I also tried to surround the string parameter with " or ' characters but no change so far.

Further if I type the function directly into Excel it works without any problems. So, for example, if I type in the following formula in Excel:

=Temp.DoSomething(B2:B13;"Flow")

Maybe I miss out something or doing something wrong?

Have you tried using a comma ={0}({1},{2}) or colon in your formula? — Shak Ham, Jul 29 '15 at 12:50
Your UDF appears to be set up to accept 2 `object` arguments, would it not be better to use `Excel.Range` and `string` as the types? — SierraOscar, Jul 29 '15 at 13:20

score 5 · Accepted Answer · edited May 23 '17 at 12:13

Doesn't look like you're adding quotes around that second parameter tagCaption in your constructed UDF string formula. There should be quotes around that value.

var formula = string.Format(@"={0}({1};""{2}"")",Temp.FORMULA_NAME, 
                              this.DataRangeTextBox.Text, tagCaption);

Also: see Chris Neilsen's comment here: Excel - Inserting formula with VBA

in VBA .Formula, .FormulaArray and .FormulaR1C1 use international seperator (ie ,) and .FormulaLocal, and .FormulaR1C1Local use the language of the user (so can use ; if that is your language setting). So for this OP assigning to .FormulaArray it is correct to say always use ,

Excel function throws exception when set by code behind. Works when used in excel

1 Answers1