What's the difference between Trim() and Trim$() in VBA?

Question

What is the difference between trim and trim$ in vba? Accidentally today when I used left and trim functions in vba, The compiler said cant find project or library

When I googled it ,On one of the forum I found the user using like these

 vba.trim("string")

He answered to prefix with vba for the functions. and surprisingly it worked on my pc too.But I found these functions

 trim and trim$ 
 left and left$ 
 leftb and leftb$

I was wondering what is trim and trim$. I wanted to find the difference So I started to google it but the results are for trim ignoring the $ alphabet.

I'm just curious to know about it.I was suspecting that trim is vba function and trim$ is excel sheet function. But we have Application.worksheetfunction to use excel functions right?Could anyone differentiate trim and trim$.

Answer 1

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
   

Answer 2

Trim is the variant version. If you use it on a string, it will need to do an unnecessary conversion.

Trim$ is the string version. Use this if you are using it on a string.

Fun side note: The Application.WorksheetFunction version of Trim does something different than VBA's Trim function.

It's worth noting that Left/Left$, Mid/Mid$, Right/Right$ have variant/string versions as well. In functions that loop or use these often, you will notice a performance hit if you use the variant version, although with modern PCs it's not huge, but it's still arguably good practice to not do something when you know it'll cause an extra step you don't need.