How to worksheetfunction.Trim a very long string in VBA?

Question

I know it's weird asking this question. But what I am facing is not less weird.

I have some long strings (About 1000 chars or more -- Update: Sorry, not 1000, but around 39000, my bad) . They contain spaces which I want to trim.

Acting on common sense, I used Worksheetfunction.Trim to do the job. It worked with some short string (around 500 chars). However, as the string got larger (over 39000 chars), it kept returning the error '1004' - unable to get trim property of the worksheetfunction class

In doubt, I performed some tests with long string in a worksheet. I entered a dummy string like "aaaaaabbbbbbcccc..." in a cell and =TRIM(string) it in another cell. It works. How does it work in worksheet but not in VBA. I am kinda confused.

To do the work, I made my own TRIM function as a workaround. But I still want to know what was happening with worksheetfunction.Trim. What is the limit of Worksheetfunction.Trim ?

Any help is appreciated. :)

Here is my code:

I use the following functions: get_address(wks as worksheet) as string : to get address of all ranges containing data in form of constant and formula.

EXNUM(TextIn as string, optional separator as string = " ") as string : to remove all non-numeric character from a string

First I will get the range address with get_address then EXNUM the address. Then I will run worksheetfunction.trim on EXNUM's result

Function get_address(wks As Worksheet) As String

    '***Find the range***
    Dim rs1 As range, rs2 As range

    On Error Resume Next
    Set rs1 = wks.Cells.SpecialCells(xlCellTypeConstants)

        If Err.Number <> 0 Then
           Set rs1 = Nothing
        End If

    Set rs2 = wks.Cells.SpecialCells(xlCellTypeFormulas)

        If Err.Number <> 0 Then
            Set rs2 = Nothing
        End If

    '***Extract range address***
    Dim ad1 As String, ad2 As String
    Dim result As String

    ad1 = area_address(rs1)
    ad2 = area_address(rs2)

    result = ad1 & "," & ad2

    If Right(result, 1) = "," Then
        result = Left(result, Len(result) - 1)
    End If

    get_address = result
End Function



Function EXNUM(TextIn As String, _
                Optional separator As String = " ") As String

Dim x As Double
Dim result As String

    For x = 1 To Len(TextIn)
            If Not IsNumeric(Mid(TextIn, x, 1)) Then
                result = result + separator
            Else
                result = result + Mid(TextIn, x, 1)
            End If
    Next x

    If Len(result) >= 1 And Right(result, 1) = separator Then
        result = Left(result, Len(result) - 1)
    End If

EXNUM = result
End Function

'**********Supporting function only************

Public Function area_address(r As range) As String

    Dim x As Double
    Dim result As String

    For x = 1 To r.Areas.count

    result = result + r.Areas.Item(x).address(rowabsolute:=False, columnabsolute:=False) + ","
    Next x

    If Right(result, 1) = "," Then
        result = Left(result, Len(result) - 1)
    End If
    'Debug.Print r.Areas.count
    area_address = result
End Function

Here is the screen shot of the error and len of the string

enter image description here

UPDATE: @brettdj: here is what I am working on. It is a fairly simple idea. I want to create a function called DetectSizeX. I input a worksheet or a range, the DetectSizeX will return address of a smaller range contain all the data in the larger range/worksheet.

For instance: DetectSizeX(Activesheet) ==> return "A3:T3568" My function works like this:

Step 1: detect the fragmented range contain all the data by using:

Cells.SpecialCells(xlCellTypeConstants)
Cells.SpecialCells(xlCellTypeConstants)

Step 2: get address of all the fragmented range in the big range gotten from above. join all the address into one string. Call it r_address.

r_address looks like "A1, B33:C88, T6:Z90, K7:Z100..." Step 3: get the top-left and bot-right cells' address

The largest number in r_address string represents the last row. The smallest number in r_address string represents the first row.

Also

The "largest" col name (like A, B, AA, AZ) in r_address represent the last column The "smallest" col name in r_address represent the first column.

Concatenate(smallest col name, smallest number) and Concatenate(largest col name, largest number)

give me the address of two cells which I can use to determine the range as the result of DetectSizeX

Here is my full code for anyone who is interested in, it is pretty long: Any suggestion and improvement is welcomed and appreciated :)

'====================================
'**********Detectsize V6*************
'====================================

Public Function DetectSizeX_v6(WorkSheetIn As Worksheet, Optional r_ad As String = vbNullString) As String
'**Note: if DetectSizeX_v5 return a string "0", it means an error, should skip that worksheet
    Dim address As String
    Dim top_left As String
    Dim bot_right As String

    Dim max_row As Double
    Dim min_num As Double
    Dim max_col As String
    Dim min_col As String

    If r_ad = vbNullString Then
        address = get_address(WorkSheetIn)
    Else
        address = get_address_range(WorkSheetIn, r_ad)
    End If

    If Len(address) > 0 Then
        max_row = get_row(address, True)
        min_num = get_row(address, False)

        max_col = get_col_name(address, True)
        min_col = get_col_name(address, False)

        top_left = min_col & min_num
        bot_right = max_col & max_row

        DetectSizeX_v6 = top_left & ":" & bot_right
    Else
        DetectSizeX_v6 = "0"
    End If
End Function

'*************GET_ADDRESS HERE*********************

Public Function get_address(wks As Worksheet) As String

    '***Find the range***
    Dim rs1 As range, rs2 As range

    On Error Resume Next
    Set rs1 = wks.Cells.SpecialCells(xlCellTypeConstants)

        If Err.Number <> 0 Then
           Set rs1 = Nothing
        End If

    Set rs2 = wks.Cells.SpecialCells(xlCellTypeFormulas)

        If Err.Number <> 0 Then
            Set rs2 = Nothing
        End If

    '***Extract range address***
    Dim ad1 As String, ad2 As String
    Dim result As String

    ad1 = area_address(rs1)
    ad2 = area_address(rs2)

    result = ad1 & "," & ad2

    If Right(result, 1) = "," Then
        result = Left(result, Len(result) - 1)
    End If

    get_address = result

End Function

Public Function area_address(r As range) As String

    Dim x As Double
    Dim result As String

    For x = 1 To r.Areas.count
        result = result + r.Areas.Item(x).address(rowabsolute:=False, columnabsolute:=False) + ","
    Next x

    If Right(result, 1) = "," Then
        result = Left(result, Len(result) - 1)
    End If
    area_address = result
End Function

Public Function get_address_range(wks As Worksheet, r_ad As String) As String

'***Find the range***
Dim rs1 As range, rs2 As range

On Error Resume Next
Set rs1 = wks.range(r_ad).SpecialCells(xlCellTypeConstants)

    If Err.Number <> 0 Then
       Set rs1 = Nothing
    End If

Set rs2 = wks.range(r_ad).SpecialCells(xlCellTypeFormulas)

    If Err.Number <> 0 Then
        Set rs2 = Nothing
    End If

'***Extract range address***
Dim ad1 As String, ad2 As String
Dim result As String

ad1 = rs1.address(rowabsolute:=False, columnabsolute:=False)
ad2 = rs2.address(rowabsolute:=False, columnabsolute:=False)

result = ad1 + "," + ad2

If Right(result, 1) = "," Then
    result = Left(result, Len(result) - 1)
End If

get_address_range = result

End Function

'******SUPPORTING FUNCTION*******
'*********For DetectSizeX_v6*****
Public Function get_col_name(ByVal address As String, max_min As Boolean)

'****Extract column name from address + cleaning address****
'address = "D2: D7 , G8, B2: B9 , F7: F9 , C2: C10 , E2: E13 , B13: D13"
'Note: if get_col_name return string "0", it means an error
address = EXTEXT(address)
address = Replace(address, ",", " ")
address = Replace(address, ":", " ")
address = EXNONBLANK(address)

'***Split address into individual string***
    Dim arr() As String
    arr = Split(address, " ")

'***Convert column names into index***
    Dim x As Double
    Dim arr_size As Double
    Dim arr_num() As Double

    arr_size = UBound(arr)
    ReDim arr_num(0 To arr_size)

    For x = 0 To arr_size
        arr_num(x) = col_num(arr(x))
    Next x

'***Extract the max and the min col name/char***
    Dim temp_num As Double
    Dim max_char As String
    Dim min_char As String

        '***Max:
        temp_num = Application.WorksheetFunction.Max(arr_num)
        For x = 0 To arr_size
            If arr_num(x) = temp_num Then
                Exit For
            End If
        Next x
        max_char = arr(x)

        '***Min:
        temp_num = Application.WorksheetFunction.Min(arr_num)
        For x = 0 To arr_size
            If arr_num(x) = temp_num Then
                Exit For
            End If
        Next x
        min_char = arr(x)

'***Return value***
If max_min Then
    get_col_name = max_char
Else
    get_col_name = min_char
End If

End Function

Public Function get_row(ByRef address As String, max_min As Boolean)

Dim x As Double
Dim max_ad As String, min_ad As String
Dim max_row As Double, min_row As Double

For x = Len(address) To 1 Step -1
    If Mid(address, x, 1) = "," Then
        max_ad = Right(address, Len(address) - x)
        Exit For
    End If
Next x

For x = 1 To Len(address)
    If Mid(address, x, 1) = "," Then
        min_ad = Left(address, x - 1)
        Exit For
    End If
Next x

max_ad = EXNONBLANK(EXNUM(max_ad))
min_ad = EXNONBLANK(EXNUM(min_ad))

'***get_max_min

Dim arr() As String
Dim arr_val() As Double
Dim arr_size As Double

arr = Split(max_ad + " " + min_ad, " ")
arr_size = UBound(arr, 1)
ReDim arr_val(0 To arr_size)

For x = 0 To UBound(arr, 1)
    arr_val(x) = Val(arr(x))
Next x

max_row = Application.WorksheetFunction.Max(arr_val)
min_row = Application.WorksheetFunction.Min(arr_val)

If max_min Then
    get_row = max_row
Else
    get_row = min_row
End If

End Function
Public Function EXTEXT(TextIn As String, _
                Optional separator As String = " ") As String

Dim x As Double 'for long text
Dim result As String

    For x = 1 To Len(TextIn)
            If IsNumeric(Mid(TextIn, x, 1)) Then
                result = result + separator
            Else
                result = result + Mid(TextIn, x, 1) + separator
            End If
    Next x

If Len(result) >= 1 And Right(result, 1) = separator Then
    result = Left(result, Len(result) - 1)
End If

EXTEXT = result
End Function
Public Function EXNUM(TextIn As String, _
                Optional separator As String = " ") As String

Dim x As Double
Dim result As String

    For x = 1 To Len(TextIn)
            If Not IsNumeric(Mid(TextIn, x, 1)) Then
                result = result + separator
            Else
                result = result + Mid(TextIn, x, 1)
            End If
    Next x

    If Len(result) >= 1 And Right(result, 1) = separator Then
        result = Left(result, Len(result) - 1)
    End If

EXNUM = result
End Function

'***Convert col_name to col_number
Public Function col_num(col_name As String)
    col_num = range(col_name & 1).Column
End Function
'***End Convert col_name to col_number


Function EXNONBLANK(str As String) As String
Do While InStr(str, "  ") > 0
    str = Replace$(str, "  ", " ")
Loop
EXNONBLANK = trim$(str)
End Function
'====================================
'**********End Detectsize V6*********
'====================================

instead of Excel function available as `worksheetfunction.trim()` use simple vba function `Trim()`. if not working you should show us your code... — Kazimierz Jawor, Feb 13 '15 at 07:01
btw, `worksheetfunction.trim()` working ok. form me even for a string with 2k characters in it — Kazimierz Jawor, Feb 13 '15 at 07:04
Hi @KazJaw, if I am not wrong, vba function `trim` only remove leading and trailing space, not the in-between space like `worksheetfuction.trim()` does. I'll post my code right away. — phong, Feb 13 '15 at 07:05
yes, you are right with this... so, my first idea is then wrong but still `worksheetfunction.trim()` doesn't have limitation on my computer with Excel 2010. — Kazimierz Jawor, Feb 13 '15 at 07:08
I also use Excel 2010. If that the case, then there could be mistake in my code. — phong, Feb 13 '15 at 07:14
can you short exaple of string before `EXNUM function`, after it and what you expect in fact? — Kazimierz Jawor, Feb 13 '15 at 07:19
before EXNUM, the string is like : "A1, B2:T3, D15:Z27,....". What I want to get is the row number of the first and the last range. I know I can get this through Areas collection but I also want to get the left-most and right-most column so I need this string. — phong, Feb 13 '15 at 07:24
one note, the length was not 1000 but 39000, my bad. It returned error when the string was around 39000 chars. — phong, Feb 13 '15 at 07:38
Excel cell length is limited to 32767 chars, so this is probably the cause of the error you received — BrakNicku, Feb 13 '15 at 07:45
So you are gathering the used range of the sheet (by area), stripping out non-numerics, then trimming the result? Why are you doing this, and what is your full code - I think it could be improved from what you are working with. — brettdj, Feb 13 '15 at 08:30

chris neilsen · Accepted Answer · 2015-02-13T08:45:42.807

3

WorksheetFunction's are limited by the same constraints as a worksheet. For Strings in Cells that's 32767 characters (as commented by user3964075).

Your best option is to roll your own Trim function, something like this

Function MyTrim(s As String) As String
    Do While InStr(s, "  ") > 0
        s = Replace$(s, "  ", " ")
    Loop
    MyTrim = Trim$(s)
End Function

Performance wise this is actually slightly faster (10%) than WorksheetFunction.Trim (tested on strings of 30,000 char)

edited Feb 13 '15 at 08:45

answered Feb 13 '15 at 08:00

chris neilsen

52,446
10
84
123

32kb limit? *Lame.* I guess that explains why I'm getting ***`Unable to get the Trim property of the WorksheetFunction class`*** on my **42,000,000** character string. :-( – ashleedawg Feb 10 '18 at 11:31

How to worksheetfunction.Trim a very long string in VBA?

1 Answers1

Linked