Array resize throws "formula might be too long when converted to R1C1 format"

Sep 5, 2014 at 3:25 PM
Edited Sep 5, 2014 at 8:00 PM
I have a simple excel function with 25 argument and I am referencing all value from cell, I am also calling Array.Resize function.

Resize function is throwing "formula might be too long when converted to R1C1 format".
Any idea why am I getting this error.

I have created small example with this error. You can download example from here. https://hl.box.com/s/weezm5oyhgpu86gz8nyg
This file contains c# project with example sheet to call function.

Below is my excel function.
 [ExcelFunction(Description = "Soemthing", Category = "ExcelDNA Test")]
        public static object TestFunc
        (
        [ExcelArgument(Name = "Int1")] int OutputType,
        [ExcelArgument(Name = "Date2")] DateTime Dte2,
        [ExcelArgument(Name = "Int3")] int int3,
        [ExcelArgument(Name = "int4")] int int4,
        [ExcelArgument(Name = "int5")] int int5,
        [ExcelArgument(Name = "int6")] int int6,
        [ExcelArgument(Name = "string7")] string string7,
        [ExcelArgument(Name = "string8")]string string8,
        [ExcelArgument(Name = "double9")] double double9,
        [ExcelArgument(Name = "double10")]double double10,
        [ExcelArgument(Name = "double11")]double double11,
        [ExcelArgument(Name = "double12")]double double12,
        [ExcelArgument(Name = "double13")]double double13,
        [ExcelArgument(Name = "string14")]string string14,
        [ExcelArgument(Name = "string15")]string string15,
        [ExcelArgument(Name = "int16")]int int16,
        [ExcelArgument(Name = "int17")] int int17,
        [ExcelArgument(Name = "int18")] int int18,
        [ExcelArgument(Name = "int19")] int int19,
        [ExcelArgument(Name = "int20")]int int20,
        [ExcelArgument(Name = "int21")]int int21,
        [ExcelArgument(Name = "string22")]string string22,
        [ExcelArgument(Name = "string 23")]string string23,
        [ExcelArgument(Name = "string24")]string string24,
          [ExcelArgument(Name = "string25")] string string25)
        {
            if (ExcelDnaUtil.IsInFunctionWizard())
                return "In Function Wizard";

            string returnValue = "06/21/2014, Value1, Value2, Value3, Value4, Value5";
            var a = returnValue.ToString().Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries);

            var retArray = new object[a.Length, 2];
            for (var i = 0; i < a.Length; i++)
            {
                if (i == 0)
                    retArray[i, 0] = "AsOfDate";
                else
                    retArray[i, 0] = string.Empty;
                retArray[i, 1] = a[i];
            }
            var ab = ArrayResizer.Resize(retArray);
            return ab;
        }
Excel Call
=TestFunc(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,B22,B23,B24)

Excel inputs:
2
7/1/2014
2
500
1
5
SomeText
Text2
8
6
3.12
28.18
57.15
ABC
DCD
10
10
10
10
10
10
AC
FX
DCs
Coordinator
Sep 5, 2014 at 8:35 PM
The resize example uses Excel functions that are limited to formula strings of length 255 characters.

When your formula is converted to R1C1 format (as Excel requires for setting the array formula) then it looks like this:
=TestFunc(RC[1],R[1]C[1],R[2]C[1],R[3]C[1],R[4]C[1],R[5]C[1],R[6]C[1],R[7]C[1],R[8]C[1],R[9]C[1],R[10]C[1],R[11]C[1],R[12]C[1],R[13]C[1],R[14]C[1],R[15]C[1],R[16]C[1],R[17]C[1],R[18]C[1],R[19]C[1],R[20]C[1],R[21]C[1],R[22]C[1],R[23]C[1])

The length is 237 characters, which might still be OK, but is really close to the Excel limit of 255 characters. Perhaps in your real usage the addresses or function name is a bit different, pushing it over the edge.

Unfortunately I know of no workaround for this Excel limitation.

If you change your function to take a one (or a few) object[] arrays as parameters, you can select and pass in a whole bunch of values in one parameter, which would reduce your formula length.

You might like to indicate to Microsoft that this is an issue you'd like to see addressed in a future Excel version, either through your Microsoft representative, or by posting to this discussion: http://social.msdn.microsoft.com/Forums/office/en-US/54d259bd-a2ae-4118-87b0-a59035e6ef75/please-remove-remaining-255-character-api-limitations?forum=exceldev

-Govert
Marked as answer by kunjan09 on 9/9/2014 at 9:31 AM