Dutch Gemini's Weblog Pages

March 21, 2012

Range from Array – Limits on length of Strings

Filed under: Excel,Programming,VBA — dutchgemini @ 11:05 am
Tags: , , , , , , , ,

In a recent post (click here) I discussed an error I ran into using CopyFromRecordSet. While trying to resolve the problem, I reverted to using the ADODB GetRows method but ended up the same situation: incomplete data set.

Lucky enough, I ran into a strictly related problem: if you write to ranges using arrays, the method will fail if the array contains a string element sized larger than 8203 characters.

Note: CopyFromRecordSet method fails for the same reason and on the same limit.

Paste the above code in a VBA module to see this happen:

Sub RangeFromArrayFails()
On Error Resume Next
    Dim i As Long, a As Variant

    ReDim a(1 To 2, 1 To 1) As Variant     ' Need an array with 2 rows

    For i = 1 To 32767 Step 2              ' 32767 is Excel's cell limit
        a(1, 1) = String(i, "*")           ' Create a string
        a(2, 1) = String(i + 1, "*")       ' As above
        ActiveSheet.Range("A1:A2").Clear   ' clear the area
        ActiveSheet.Range("A1:A2") = a     ' Assign array to range
        If (Err.Number <> 0) Then Exit For ' Error, quit.
    Next i

    Debug.Print IIf(i > 32767, "No limit", "Limit reached at: " & i)
    Debug.Print "Length A1", Len(ActiveSheet.Range("A1"))
    Debug.Print "Length A2", Len(ActiveSheet.Range("A2"))
End Sub

This should be your result:

Limit: 8203
Length A1      8203
Length A2      0

You can try the above code with only 1 array element and see it end without problems.

Have a nice day.

Blog at WordPress.com.