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 :cheers:

[Rasm]

Also be aware that a Range cannot exceed 255 characters - or maybe it is 256 - so if you are getting data from all over the sheet be careful - this has caused me many headaches. Since we are on the topic of 255(256) - be aware that you can only have 255 seriescollections in a chart. This must be a left over from the days of 8 bit computers.