Using the undocumented `Application.Trim` and understand VBA intellisense suggestions

Question

When I googled about Trim, I found that it is an Excel function and also a VBA function.

In VBA with both cases it can be used only with one cell and need looping with a range of more than one cell. But, I found some web pages stating use of Application.Trim on a range without loop and indeed it works and very fast.

My question, how Trim used with Application, even undocumented and vba intellisense does not show TRIM after I type Application. And does this mean that WorksheetFunction. can be replaced by Application. to produce a different behavior?

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

• My little investigation has led me to believe that Application.Trim actually works with arrays and returns a one-based array of the trimmed values.
• If rng is a contiguous range with more than one cell, the expression rng.Value (on the right side of an equation) is actually a 2D one-based array containing the values in the range.

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