Error while copying more than 8202 characters from one cell to another

Question

Problem - I have around more than 8202 characters in once cell say Range("A1").

Now I would like to copy the content of cell(A1) to cell(A2) using VBA. I'm using below Code

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1")

After the execution of the Code. It gives "Application Defined Or Object Defined Error !!"

Please help/assist with your expert comments.

Observation - If I reduce the length of "A1" cell to 8202 or less then about code works!

I'm Confused. Pls assist.

Answer 1

Change your code to

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1").Value

and it will work.

Not really sure why though, as .Value is the default property of a range.

Answer 2

I was able to duplicate your error with the following:

Sub Test8202Copy()
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")
    Dim x As String

    For i = 0 To 8202
        x = x + "a"
    Next i

    wks.Range("A1").Value = x
    wks.Range("A2") = wks.Range("A1")
End Sub

---

I was able to solve the error by adding .value to the copy.

Sub Test8202Copy()
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")
    Dim x As String

    For i = 0 To 8202
        x = x + "a"
    Next i

    wks.Range("A1").Value = x
    wks.Range("A2").Value = wks.Range("A1").Value
End Sub

---

Using an intermediate variable without the use of .Value seems to work:

Dim y As Variant
y = wks.Range("A1")
wks.Range("A2") = y

My guess so far is that 8202 exceed the character limit of the data type used when you don't define .Value. The in cell limit length is 32,767 (MS Excel 2010) which is almost 4x the 8201 value that clears.

Answer 3

@Chris Neilsen provided the most practical and elegant solution to the problem (his code snippet follows):

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1").Value

In order to investigate and understand the possible cause of this strange behavior (may be a bug) of the Range object, I've posted couple comments, which are summarized below:

There is a conceptual difference between the original expression (see below):

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1")

and solution proposed by @Chris Neilsen, namely: original expression is implicitly assigning the Range object var (essentially, a pointer) to another Range object, like demonstrated in the following code snippet with explicit assignment:

Set rng = Sheets("XYZ").Range("A1")
Sheets("XYZ").Range("A2") = rng

while proposed solution explicitly passes the value property. Still, the reason why assigning a Range object failed for a value with string.Length>8202 is currently unclear (it may be caused by some internal nuances of the Excel Range object implementation).

Many thanks for posting this interesting question and fruitful discussion. Regards,

Answer 4

This limit (see below re excel-2007) is covered in this MSDN article although interestingly it implies a vba array is invoved

Separately as per https://stackoverflow.com/a/13665363/641067 excel-2003 cant handle array strings longer than 911 characters, whereas the article below references 1823 character

SYMPTOMS

When you run a Microsoft Visual Basic for Applications (VBA) macro to transfer data from a VBA array that contains strings of data to a range of cells in a Microsoft Excel worksheet, the data may be truncated (cut off).

Note In Microsoft Office Excel 2003 and in later versions of Excel, you may receive the following error message when you run the VBA macro in the Visual Basic Editor: Run-time error '1004'

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.
• In Excel 2003 and in earlier versions of Excel, the VBA array is longer than 1,823 characters in length