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