使用未记录的"Application.Trim"并理解VBA intellisense建议

Question

当我在谷歌上搜索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

Answer 1

Application.Trim

• 我的一点调查让我相信Application.Trim实际上可以处理数组，并返回一个基于一的修剪值数组
• 如果rng是一个具有多个单元格的连续范围，则表达式rng.Value(在等式的右侧(实际上是一个包含该范围中的值的2D基于一的数组

范围

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

阵列

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

结果

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