Differentiate trim and trim$ in vba

Accepted answer +16 upvotes

While Issun has answered your question as asked I had enough detail that I wanted to post to provide a further answer as opposed to comment.

The string versions are significantly faster ~ approx 10-30% depending on the data type from my testing over the years. While this is not normally noticeable, it is a performance difference when running on large datasets. So for me it’s a no-brainer to use the string rather than variant version.

The sample below works on strings so it shows a speed advantage at the higher end of this range

I have used these functions in combination with variant arrays in both of my public addins as these programs are typically used on entire worksheets even entire workbooks

This link is an excellent reference, for your question, and well beyond

1. Comparing in vbBinaryCompare rather than vbTextCompare
2. Optimising empty strings
3. Testing before replacing
4. Using built in constants, VbNullString is faster than "", although both will miss a cell that contains ' whereas IsEmpty picks this up
5. Optimising Loops (break AND into two separate IFs to give an early escape)
6. Optimising If tests to return the most common Boolean result first rather than run through the Else path (ie a False test may be more appropriate than True)

7. Using Mid$ on the left hand side of an assignment. From hidden features of VBA

   Sub QuickTimer1()
   Dim lngRow As Long
   Dim dbTime As Double
   Dim strSample As String
   Dim strShort As String
   strSample = "random string"
   dbTime = Timer()
   For lngRow = 1 To 50000000
       strShort = Left$(strSample, 6)
   Next lngRow
   MsgBox Timer() - dbTime
   End Sub
   
   Sub QuickTimer2()
   Dim lngRow As Long
   Dim dbTime As Double
   Dim strSample As String
   Dim strShort As String
   strSample = "random string"
   dbTime = Timer()
   For lngRow = 1 To 50000000
       strShort = Left(strSample, 6)
   Next lngRow
   MsgBox Timer() - dbTime
   End Sub