Excel ComType of Application.Evaluate() on a concatenation function

Question

Inside a c# UDF for excel 2007, I need to evaluate the value of Excel function parameters (don't ask why, long story).

Let's say we have this Excel function to evaluate :

=afunctionname("2009-01-01" ; "B4" ; "foo" ; concatenate("a";"b") )

My goal is to get a string with :

=afunctionname("2009-01-01" ; "value of B4 cell" ; "foo" ; "ab" )

I evaluate the value of the params thank to this snippet :

Object comObject = app.Evaluate(param); //app = Microsoft.Office.Interop.Excel.Application
String value = getValueFromComObject(comObject);

getValueFromComObject detail :

private static String getValueFromComObject(Object comObject)        {
    if ((comObject as Range) != null)
                {
                    Range rge = ((Range)comObject);

                    switch (Type.GetTypeCode(rge.Value.GetType()))
                    {
                        case TypeCode.DateTime:
                            return ((DateTime)rge.Value).ToShortDateString();
                        default:
                            return rge.Value.ToString().Trim();
                    }
                }
                else
                {
                    return comObject.ToString();
                }
}

The fist and third params of our example directly return as String by application.Evaluate . The second param is return as a range and correctly managed thank to the type casting.

The problem is for the fourth param, I dont know witch cast type I have to apply on the evaluation of the concatenation function, it's obviously not a range and the toString() gives me the reference : -2146826273

Any idea?

Not sure about C# but in VB you can use TypeName or .GetType.ToString (or something like that) to determine what type of object it is. — Jon49, Nov 09 '11 at 18:08
It is error code 2015, "type mismatch". Might have something to do with trying to convert a function to a string, not sure. — Hans Passant, Nov 09 '11 at 18:40
In VBA `typename(application.Evaluate("concatenate(""a"",""b"")"))` gives "String". I don't know why your app.evaluate wouldn't give a similar result... — Tim Williams, Nov 09 '11 at 18:53
There are lots of quirks with Evaluate: you might find this blog post useful http://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/ — Charles Williams, Nov 09 '11 at 18:58

score 0 · Answer 1 · answered Mar 15 '13 at 15:44

In C Sharp, to evaluate a formula we set in string "," to separate the arguments. In the native excel application we put ";". To evaluate static values (e.g.: CONCATENATE("a","b") ), we use "Application.Evaluate" method. To evaluate values in cells (e.g.: CONCATENATE(A1,B1) ) we use Application._Evaluate.

score 0 · Accepted Answer · edited May 23 '17 at 10:24

Ok I found the problem :) !

First of all the return type is an Int32 and the return value correspond to the error code #value according to this page : How to know if a cell has an error in the formula in C#

Actually, the lengh of the Strings were too long for the concatenate function in eval mode, I have try in Excel it works but with eval it gives me an error. So I just had to reduce the lengh of each string in concatenate method (add more params) and it works.

Thank you all for your help.

Excel ComType of Application.Evaluate() on a concatenation function

2 Answers2