Strange number-of-character-in-a-cell limitation when programmaticaly (VBA, C#) set array string to a range

Question

I have run into an issue in Excel 2007 which is very similar to a known problem of Excel 2003 described by Microsoft Support here ("You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003"). The problem reproduces if run the following macro:

Sub newMacro()

Dim longStr As String
longStr = String(8204, "a")

Dim values(3)

For i = 0 To 2
    values(i) = longStr
Next i

Range("A1:C1").Value = values

End Sub

Excel gives the "Run-time error '1004'" error message when assigning an array value to a range. But when string length is 8203 characters everything works just fine.

The situation does look strange to me because the "Total number of characters that a cell can contain" limitation for Ecxel 2007 (mentioned in "Excel specifications and limits" here) is 32767.

Additional information:

1. The problem doesn't reproduce in Excel 2010.
2. The problem doesn't reproduce if set values cell-by-cell without use of an array. But in my case it will dramatically slow down my code.
3. Initially I noticed the problem when working with Excel through our Excel add-in that was written in C# using NetOffice libraries.

Did anyone run into this issue? Are there any workarounds for it? Are there any comments by Microsoft on the case? I didn't find anything.

Answer 1

It seems that Microsoft has written a KB Article about this as well: MS KB 832136.

From the article:

Cause: This problem may occur when one of the following conditions is true:

• In Excel 2007, the VBA array is longer than 8,203 characters in length.

Workaround:

Microsoft suggest that in stead of placing the entire array into you worksheet at once, you should populate the worksheet one at a time from your array. They provide the following sample code in their article as a suggestion on how to do this:

Sub PopulateRangeWithArray()
    Dim x
    ReDim x(1 To 2, 1 To 2)
    x(1, 1) = String(2000, "a"): x(1, 2) = String(5000, "b")
    x(2, 1) = String(17000, "c"): x(2, 2) = String(33000, "d")
    MsgBox Len(x(1, 1)) & "," & Len(x(1, 2)) _
           & "," & Len(x(2, 1)) & "," & Len(x(2, 2))
    Range("a1").Value = x(1, 1)
    Range("b1").Value = x(1, 2)
    Range("a2").Value = x(2, 1)
    Range("b2").Value = x(2, 2)
End Sub

Answer 2

Same limit applies to Excel 2010 and Excel 2013. In Excel 2016 there is virtually no limit though only the first 32767 characters are stored without generating any error, the rest is discarded. This is the case with arrays declared as String. When declaring the array as Variant the limit drops down to 8192, the actual "Formula Size Limit".