VBA – Difference between Trim, VBA.Trim, =Trim() and WorksheetFunction.Trim

Trimming is a function, that reduces spaces. There are at least 4 ways to Trim a string in Excel/VBA:

  • Trim
  • VBA.Trim
  • =Trim()
  • WorksheetFunction.Trim

Thus, is there a difference between these 4 actually? This is what MSDN says about String.Trim:

Returns a new string in which all leading and trailing occurrences of a set of specified characters from the current String object are removed.

However, if we take a look at Excel and the Trim() formula, it seems that it is removing the front and back spaces and reducing the multiple spaces in each cell to one cell only:

 

 

 

So there is one small difference between the =Trim() and the VBA.Trim() function, mentioned in the MSDN article. The VBA.Trim() should not be “touching” the inner space. Is it really so? Let’s see a small example:

And the printed console looks like this:

Thus, the difference is that the VBA.Trim and Trim do not touch the internal spaces. WorksheetFunction.Trim and =Trim reduce the numbers of internal spaces, if they are more than one. And in general, VBA.Trim and Trim are the same. 🙂 WorksheetFunction.Trim and =Trim in the Worksheet as well. 🙂

About

A VBA Developer.

Tagged with: , , , ,