Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14

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

    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')

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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.

    Code:
        Dim var As Variant
        
        var = Cells(1).CurrentRegion.Value
        Range("E1").Resize(UBound(var), UBound(var, 2)) = var
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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.

    Code:
        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.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Quote Originally Posted by LalitPandey87 View Post
    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')

    Code:
    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.

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by LalitPandey87 View Post
    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.

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    PFA.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Quote Originally Posted by Admin View Post
    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:-

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

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Thanks for the feedback
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 05-14-2013, 01:02 AM
  2. Replies: 1
    Last Post: 12-04-2012, 05:30 PM
  3. Concatenate array string
    By tushar.tarafdar in forum Excel Help
    Replies: 2
    Last Post: 09-20-2012, 12:00 PM
  4. Replies: 4
    Last Post: 04-07-2011, 07:09 AM
  5. Replies: 6
    Last Post: 04-03-2011, 09:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •