Handling string in an array of length greater then 255 in excel

[LalitPandey87]

Hi All,


Today i found a situation when an array contains a string value which is greater then 255 char in length and when i use following code it gives me error (Run time error '1004')

Activesheet.range("A1").resize(ubound(arr), ubound(arr,2)) = arr

but if all values are less then 255 char in length it works fine.


so any suggestion to solve this kind of situation.


Thanks in Advance

[Excel Fox]

Is the array created by passing values directly from a range? Or is it created from the keys of a dictionary object?

I made a few cells of text having texts of length around 3000 to 4000 characters, and the following code work just fine each time.

    Dim var As Variant
    
    var = Cells(1).CurrentRegion.Value
    Range("E1").Resize(UBound(var), UBound(var, 2)) = var

[Rick Rothstein]

Is the array created by passing values directly from a range? Or is it created from the keys of a dictionary object?

I made a few cells of text having texts of length around 3000 to 4000 characters, and the following code work just fine each time.

    Dim var As Variant
    
    var = Cells(1).CurrentRegion.Value
    Range("E1").Resize(UBound(var), UBound(var, 2)) = var

I did roughly the same thing you did, but used shorter text in the cells that I created the array from and then assigned a longer piece of text to one of the array elements afterward. I too ran into size limit as to how long a text string I could assign to the array element before writing it back to the worksheet failed, but it was not 255 characters... strangely, it turned out to be 911 characters (I could assign 910 characaters successfully, but adding one more character to it crashed with the 1004 error). If it helps any in trying to figure out what is going on here, I am using XL2003 on a Win7 (SP1) system.

[Excel Fox]

Apparently, I tested the above code on a few other data, and yes, it is throwing an error when cells have text lengths greater than 256. I am using some special characters in these texts. That's the only difference that I can gather, as the same code works in my earlier experiment. It looks like you have to use a loop.

[Excel Fox]

Incomplete information is always dangerous. In the above post, I should have added that even though I used special characters, when I kept all texts at a maximum length of 255, it worked fine. By the way, I'm on Excel 2007 and Win 7.

[LalitPandey87]

Hi All,


Today i found a situation when an array contains a string value which is greater then 255 char in length and when i use following code it gives me error (Run time error '1004')

Activesheet.range("A1").resize(ubound(arr), ubound(arr,2)) = arr

but if all values are less then 255 char in length it works fine.


so any suggestion to solve this kind of situation.


Thanks in Advance

In my case there is a particular value :-

e.g.:- "adadfadfad&adadfafa adfasfaf adsfasfasf fdsa&adfa's&adfadfafadfadfadfadf..."


which cause an error.

[Rick Rothstein]

In my case there is a particular value :-

e.g.:- "adadfadfad&adadfafa adfasfaf adsfasfasf fdsa&adfa's&adfadfafadfadfadfadf..."


which cause an error.

It might help us to help you if you attached a copy of your workbook with its data and your existing code so that we could see exactly what you see.

[Admin]

Hi,

PFA.

[LalitPandey87]

Hi,

PFA.

Hi Admin,

There is an issuse when = comes at first then excel treat it as a formula and raise an error.
I modified code and replaced = with '=.
Here is the modified version:-

Sub kTest()
    
    Dim ka, k(), c() As String, i As Long
    Dim j As Long, n As Long, r As Range, s As String
    
    Application.ScreenUpdating = False
            
    Set r = Sheet1.Range("a1").CurrentRegion
    ka = r
    
    ReDim k(1 To UBound(ka, 1), 1 To UBound(ka, 2))
    
    For i = 1 To UBound(ka, 1)
        For j = 1 To UBound(ka, 2)
            If Len(ka(i, j)) > 255 Then
                n = n + 1
                ReDim Preserve c(1 To n)
                c(n) = Cells(i, j).Address(0, 0)
            Else
                If Left$(ka(i, j), 1) = Chr(61) Then
                    k(i, j) = Chr(39) & ka(i, j)
                Else
                    k(i, j) = ka(i, j)
                End If
            End If
        Next
    Next
    
    With Sheet2.Range("a1")
        .Parent.UsedRange.ClearContents
        .Resize(UBound(k, 1), UBound(k, 2)) = k
        If n Then
            For i = 1 To n
                s = r.Range(c(i)).Value
                If Left$(s, 1) = Chr(61) Then s = Chr(39) & s
                .Range(c(i)) = s
            Next
        End If
    End With
    Application.ScreenUpdating = True
End Sub

[Admin]

Thanks for the feedback