使用未记录的“Application.Trim”并了解 VBA 智能感知建议

当我用谷歌搜索时Trim，我发现它是一个Excel函数，也是一个VBA函数。

在这两种情况下的 VBA 中，它只能与一个单元格一起使用，并且需要对多个单元格的范围进行循环。但是，我发现一些网页说明了使用Application.Trim在没有循环的范围内，它确实可以工作并且非常快。

我的问题是，如何Trim与...一起使用Application，即使是未记录的和 vba intellisense 在我输入 Application 后也不显示 TRIM。这是否意味着WorksheetFunction.可以替换为Application.产生不同的行为？

Sub Trim_Issue()
 
   Dim rng As Range
   Set rng = ActiveSheet.Range("A2:A3")
 
   rng = Application.Trim(rng) 'This works although I do not know how
 
   rng = WorksheetFunction.Trim(rng) 'cause error as it need loop
 
End Sub

Application.Trim

• 我的小调查让我相信Application.Trim实际上适用于数组并返回修剪值的从一开始的数组。
• If rng是一个具有多个单元格的连续范围，表达式rng.Value（在等式的右侧）实际上是一个包含范围内的值的基于 1 的二维数组。

Range

Sub TrimShort()
 
    Dim rng As Range: Set rng = ActiveSheet.Range("A2:A3")
    ' The expression 'rng = Application.Trim(rng)' is short for:
    rng.Value = Application.Trim(rng.Value)
 
End Sub

Sub TrimLong()
    Dim rng As Range: Set rng = ActiveSheet.Range("A2:A3")
    ' Note that the following line is inaccurate
    ' i.e. it will fail if the range contains one cell only.
    Dim Data1() As Variant: Data1 = rng.Value
    Dim Data2() As Variant: Data2 = Application.Trim(Data1)
    rng.Value = Data2
 
End Sub

Arrays

Sub TrimOneD()
    
    Dim sArr() As String: sArr = Split(" A A ,  B  B", ",") ' 1D zero-based
    
    Dim dArr() As Variant: dArr = Application.Trim(sArr) ' 1D one-based
    
    Debug.Print "srIndex", "sArr", "dArr"
    
    Dim r As Long
    
    For r = 0 To UBound(sArr)
        Debug.Print r, sArr(r), dArr(r + 1)
    Next r

End Sub

Sub TrimTwoD()
    
    Dim sData() As Variant: ReDim sData(0 To 1, 0 To 1) ' 2D zero-based
    sData(0, 0) = " A A "
    sData(0, 1) = "  B  B"
    sData(1, 0) = " D   D "
    sData(1, 1) = CVErr(xlErrNA) ' it will not fail if error value
    
    Dim dData() As Variant: dData = Application.Trim(sData) ' 2D one-based
    
    Debug.Print "srIndex", "scIndex", "sData", "dData"
    
    Dim r As Long, c As Long
    
    For r = 0 To UBound(sData, 1)
        For c = 0 To UBound(sData, 2)
            Debug.Print r, c, sData(r, c), dData(r + 1, c + 1)
        Next c
    Next r

End Sub

Results

srIndex        sArr          dArr
 0             A A          A A
 1              B  B        B B

srIndex       scIndex       sData         dData
 0             0             A A          A A
 0             1              B  B        B B
 1             0             D   D        D D
 1             1            Error 2042    Error 2042