Search code examples
excelexcel-dna

Using multiple UDF functions in Application.Evaluate

I've built Excel-Dna Addin in C#. I have couple of UDF functions that I need to run in some cases. My cells contains the UDFs with different parameters, I am showing here the simplified version.

I am using Application.Evaluate function for this.

My UDF:

[ExcelFunction(Name = "Test.TestMe", Description = "Some test", IsHidden = false)]
public static int TestMe(int i)
{
    return i + 1;
}

If I call Application.Evaluate("=Test.TestMe(1)") I get the result 2.

If I try following: Application.Evaluate("=Test.TestMe(1)+Test.TestMe(2)") none of my UDF functions are not called.

The Evaluate function returns: -2146826259 = #Name?

I've tried following:

  1. Removed = before function name "Test.TestMe(1)+Test.TestMe(2)"
  2. Called with fully qualified name. "myexcel.xlsx!Test.TestMe(1)+Test.TestMe(2)"
  3. I am aware that Application.Evaluate is limited to 255 chars.

I would like to avoid parsing the formula and calling my UDFs one by one.

Any ideas?

Solution

  • Charles Williams (https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/) points to a (now-missing) KB article when he describes this limitation:

    If the string formula contains a reference to both a UDF and a name it will fail with an error 2029 if the name reference occurs AFTER the UDF reference:

    • If fred is a named range and xyz() is a user defined VBA function then this statement returns error 2029 [i.e. #NAME?]: Application.Evaluate("=xyz(b1)+fred")
    • This statement returns the correct value: Application.Evaluate("=fred+xyz(b1)")
    • Microsoft KB article 823604 [which I couldn't find] identifies this problem but does not correctly diagnose the circumstances that cause it.

    Since your UDF name is also a 'name' in Excel, it is presumably subject to the same quirk, giving you this #NAME? error.

    So I think you're running into a long-standing Excel quirk, and you might want to report this to Microsoft with the File -> Feedback -> 'Send a Frown' button.