string - 如何在VBA中使用worksheetfunction.Trim很长的字符串？

Question

我知道问这个问题很奇怪。但我所面临的情况并不那么奇怪。

我有一些长字符串(大约 1000 个字符或更多 - 更新:抱歉，不是 1000，而是大约 39000，我的错)。它们包含我想要修剪的空格。

根据常识，我使用 Worksheetfunction.Trim 来完成这项工作。它适用于一些短字符串(大约 500 个字符)。但是，随着字符串变大(超过 39000 个字符)，它不断返回错误 '1004' - 无法获取工作表函数类的修剪属性

有疑问，我在工作表中使用长字符串执行了一些测试。我在一个单元格中输入了一个虚拟字符串，如“aaaaaabbbbbbcccc...”，并在另一个单元格中输入了 =TRIM(string) 。有用。它在工作表中如何工作，但在 VBA 中却不起作用？我有点困惑。

为了完成这项工作，我制作了自己的 TRIM 函数作为解决方法。但我仍然想知道 worksheetfunction.Trim 发生了什么。 Worksheetfunction.Trim 的限制是什么？

感谢任何帮助。 :)

这是我的代码:

我使用以下函数:get_address(wks as worksheet) as string :获取包含常量和公式形式数据的所有范围的地址。

EXNUM(TextIn as string,可选分隔符as string = "") as string:从字符串中删除所有非数字字符

首先，我将使用 get_address 获取范围地址，然后使用 EXNUM 地址。然后我将对 EXNUM 的结果运行 worksheetfunction.trim

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

这是错误和字符串长度的屏幕截图

更新: @brettdj:这就是我正在做的事情。这是一个相当简单的想法。我想创建一个名为 DetectSizeX 的函数。我输入一个工作表或一个范围，DetectSizeX 将返回较小范围的地址，其中包含较大范围/工作表中的所有数据。

例如:DetectSizeX(Activesheet) ==> 返回"A3:T3568"我的函数的工作原理如下:

第 1 步:使用以下方法检测包含所有数据的分段范围:

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

第2步:获取上面得到的大范围内所有分片范围的地址。将所有地址连接成一个字符串。称之为r_address。

r_address 看起来像“A1、B33:C88、T6:Z90、K7:Z100...”第3步:获取左上角和右下角单元格的地址

r_address 字符串中最大的数字代表最后一行。r_address 字符串中最小的数字代表第一行。

还有

r_address 中的“最大”列名称(如 A、B、AA、AZ)代表最后一列r_address 中的“最小”列名称代表第一列。

连接(最小的列名称，最小的数字)和连接(最大的列名称，最大的数字)

给我两个单元格的地址，我可以用它们来确定 DetectSizeX 结果的范围

这是我的完整代码，供感兴趣的人使用，它很长:欢迎并赞赏任何建议和改进:)

'====================================
'**********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*********
'====================================

Answer 1

WorksheetFunction 受到与工作表相同的约束的限制。对于单元格中的字符串，该长度为 32767 个字符(如 user3964075 所评论)。

你最好的选择是推出你自己的修剪功能，像这样

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

就性能而言，这实际上比 WorksheetFunction.Trim 稍快 (10%)(在 30,000 个字符的字符串上进行测试)