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”)
rngA.ClearContents
Ax = rngA
For i = 1 To 1016
str1 = str1 & “a”
Ax(1, 1) = str1
rngA.Value = Ax
Next i
End Sub
Dim str1 As String
Dim Ax As Variant
Dim rngA As Range
Dim i As Long
Set rngA = ActiveSheet.Range(“A1:B1”)
rngA.ClearContents
Ax = rngA
For i = 1 To 1016
str1 = str1 & “a”
Ax(1, 1) = str1
rngA.Value = Ax
Next i
End Sub
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?
Found this while searching: C#, Excel and Performance
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.
APJ
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.
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:
http://support.microsoft.com/?scid=kb;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.
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?
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.
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.
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
Rng.ClearContents
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
Oops! Truncation error.
The code continues…
If xc.Count XL2000 causes an error in the write process where XL2000 just truncates).
APJ
Microsoft described this issue in http://support.microsoft.com/kb/818808