Using Excel Built-in Functions on top of the UDF

Question

I don't know why for some reason the Excel built-in function =SUM() can't really sum up the results created by the custom UDF. It appears to always end up with 0.

For example, I have got a following UDF, called myUDF, which will return the number of items.

For the cell A1:
Formula: =myUDF('ItemA') Result: 10

For the cell B1:
Formula: =myUDF('ItemB') Result: 15

So When I do =Sum(A1:B1) and put the formula in cell C1, it won't return 25 but 0 instead.

I have tried to use some data formatting stuff (converting to numeric) but still no luck there. Has anyone here had similar issue before? Any ideas on the cause of it?

Thanks.

EDIT: Code Sample

        public object MyUDF(string id, string pk, string param1 = "", string param2 = "", string param3 = "", string param4 = "")
        {
            object result = null;
            string strFormula;

            double n = 0;
            DateTime dt;

            try
            {
                strFormula = buildFormula(id, pk, param1, param2, param3, param4);

                result = ws.getServiceResultsDataString(id, objUser, pk, param1, param2, param3, param4);

                if (double.TryParse(result.ToString(), out n)) 
                {
                    result = n;
                }
                else if (DateTime.TryParse(result.ToString(), out dt))
                {
                    result = dt.Date;
                }
                ws.Dispose();
                objUser = null;

            }
            catch (Exception ex)
            {               
            }
            finally
            {   
            }
            return result;
        }

Chris Shain · Accepted Answer · 2012-01-09 05:23:22Z

2

Is your UDF returning a number or a string that happens to contain numeric characters?

If I go into Excel and type in '10 (10, formatted as a string) and '15 (15, formatted as a string), and SUM() them, I get 0.

If I type 10 and 15 (formatted as numbers) and SUM() them, I get 25.

Some example code that reproduces the problem would allow us to answer for sure.

answered Jan 9, 2012 at 5:23

Chris Shain

51.5k7 gold badges95 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

woodykiddy Over a year ago

Hmm, good point. Is there a way to convert string value to numeric value in Excel?

Chris Shain Over a year ago

If your UDF just returns an Int or a Double rather than the String, it shouldn't even need that though.

woodykiddy Over a year ago

Well, it will return the units along with numeric data in some cases as well. So it's not entirely considered as int type function.

Chris Shain Over a year ago

In that case I'd suggest either a) letting it return an Object (which might be a String or an Int or a Double), or b) having 2 (or more) different functions. Varying return types are generally considered confusing at best, so b would be my preference, but you know your use case and I don't.

woodykiddy Over a year ago

If we let UDF return value be object, will Excel automatically convert the number valued string into numeric value?

|

Collectives™ on Stack Overflow

Using Excel Built-in Functions on top of the UDF

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest