Error Writing Long Strings

Why does this procedure run without error in 2000, but errors at i = 912 in 2003. Alex and I want to know.

Sub Longstring()
    Dim str1 As String
    Dim Ax As Variant
    Dim rngA As Range
    Dim i As Long
    Set rngA = ActiveSheet.Range(“A1:B1”)
    Ax = rngA
    For i = 1 To 1016
        str1 = str1 & “a”
        Ax(1, 1) = str1
        rngA.Value = Ax
    Next i
End Sub
Posted in Uncategorized

11 thoughts on “Error Writing Long Strings

  1. Same thing in Excel 2002 (Excel XP)

    This triggers the same:

    Sub Test()
        With Range(“A1:B1”)
            .Value = String(912, “a”)
            .Value = .Value
            End With
    End Sub

    Works if you give it just one cell – fails on two.

    Strange eh?

  2. Following up 912 at MS KB for xl2003 yields article 818808 – ‘You may receive a “Run-time error 1004? error message when you programmatically set a large array string to a range in Excel 2003’. (Could not find one for xl2002).

    Unfortunately, the advice from MS to avoid this error is “Don’t do that”…
    Can’t figure out why a very useful door has been closed.


  3. Though I don’t fully understand it, but I found a few threads that point to the way that Excel 2002 and 2003 internally handles memory management and coercion. Apparently this error happens when Excel tries to coerce an xloper that has a string. There are a few article on coercion; they seem related to this problem, but somehow different.

  4. Just ran into this same thing a few weeks ago. Interestingly, without having seen it, we adopted the same mixed-mode workaround described in Steffen Vorein’s blog linked above. Something Steffen doesn’t talk about, though, is how the behavior and limits changed between Excel 2000, 2002, and 2003. Up through Excel 2002 SP3 (10.5815.6735), the length limit was 1822 and instead of tossing an error it would silently trim longer strings to this limit. Starting with Excel 2002 SP3 (10.6501.6735), and continuing to Excel 2003, the limit was cut in half to 911 and longer strings would throw an error, leaving the range populated up to the point where the longer string was encountered.

    The following KB describes the truncation for Excel 2000 and 2002:;en-us;832136

    Note that it says in the title that the limit is 1835, but the text says “when the VBA array is longer than 1,823 characters in length”, which is wrong both in context (we’re talking about the length of strings in arrays, not arrays themselves) and in fact (it should say “longer than 1,822 characters”), but at least it addresses it.

  5. I just tested this in Excel 97 and it works ok till i=912, then on 913 it errors and closes down the entire excel instance. Did MS re-use a piece of code from the old xl97?

  6. Came accross the same problem with a program which displays tasks (including some extensive detail in an ‘Actions Taken’ column) on a spreadsheet.

    Microsoft’s answer (to write each cell individually) works but is very slow – indeed that was the point of using an array in the first place. If anyone knows a quicker answer I would be very interested to hear it.

  7. Ian,
    If I understand some of the earlier comments on this topic, use a “mixed-mode” write. That would be: find and trap all the long (>912) items (I will probably use a collection for that), delete them from your write array, write the (now with shortened entries) array to the worksheet, and then write the long entries to individual cells one by one. Hopefully they are the exception.

  8. Here’s some code I was playing with today – no warranty. Assume varrX is a Variant Array with Option Base 1 (which is the basis for the array write approach).

    Const xlLimit As Long = 1800
    Const uTag As String = “WriteTag”

    Function MixedModeWrite(Rng As Range, varrX As Variant, Optional msgFlag As Boolean = False) As Long

    Dim i As Long, j As Long, ErrCt As Long
    Dim xc As New Collection
    Dim arrA(1 To 3) As Variant
    Dim xcc As Variant

    On Error GoTo debugerr
    Set Rng = Rng.Resize(UBound(varrX, 1), UBound(varrX, 2))

    For i = 1 To UBound(varrX, 1)
    For j = 1 To UBound(varrX, 2)
    Erase arrA
    If Not IsError(varrX(i, j)) Then
    If Len(varrX(i, j)) > xlLimit Then
    arrA(1) = i
    arrA(2) = j
    arrA(3) = varrX(i, j)
    varrX(i, j) = uTag
    xc.Add arrA
    End If
    End If
    Next j
    Next i

    Rng.Value = varrX

    If xc.Count

  9. Oops! Truncation error.

    The code continues…

    If xc.Count XL2000 causes an error in the write process where XL2000 just truncates).


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.