CODEWITHSUNDEEP
vbaexceltrim
niko
niko

Reputation: 9393

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

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$.

Upvotes: 17

Views: 24240

Answers (4)

Gaijinhunter
Gaijinhunter

Reputation: 14685

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.

Upvotes: 11

brettdj
brettdj

Reputation: 55672

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

Upvotes: 24

Justin Self
Justin Self

Reputation: 6265

trim accepts a variant as its parameter while trim$ accepts a string.

There are some performance benefits for using trim$ (since you aren't converting from a variant), but chances are you wouldn't notice them.

EDIT:

I figured this out by checking the object browser. When you are in the VBA editor, press F2 and you can search for functions and be come more familiar with the framework.

Upvotes: 5

Widor
Widor

Reputation: 13275

I believe the notation is a legacy of VB days, where $ was used to denote objects/functions that were (or operated on) strings.

Functionally, there's no difference although I understand that the $ version is alleged to be faster, as it eliminates the need to convert from a variant to a string.

Upvotes: 3

Related Questions