VBA 中的 Trim() 和 Trim$() 有什么区别？

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

提问by niko

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

vbatrim和trim$在 vba 中有什么区别？今天无意中在vba中使用left和trim函数时，编译器说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

他回答以 vba 作为函数的前缀。令人惊讶的是它也适用于我的电脑。但我发现了这些功能

 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.worksheetfunctionto use excel functions right?Could anyone differentiate trim and trim$.

我只是想知道它。我怀疑trim 是vba 函数，trim$ 是excel 表函数。但是我们必须Application.worksheetfunction使用excel函数对吗？谁能区分trim和trim$。

回答by brettdj

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.

虽然 Issun 已按照您的要求回答了您的问题，但我有足够的详细信息，我想发布以提供进一步的答案，而不是发表评论。

The string versions aresignificantly 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.

该字符串的版本是显著快〜取决于从我的测试历年的数据类型约10-30％。虽然这通常不会引起注意，但在大型数据集上运行时会出现性能差异。所以对我来说，使用字符串而不是变体版本是显而易见的。

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 addinsas these programs are typically used on entire worksheets even entire workbooks

我已经在我的两个公共插件中将这些函数与变体数组结合使用，因为这些程序通常用于整个工作表甚至整个工作簿

This linkis an excellent reference, for your question, and well beyond

这个链接是一个很好的参考，对于你的问题，远远超出

1. Comparing in vbBinaryComparerather than vbTextCompare
2. Optimising empty strings
3. Testing before replacing
4. Using built in constants, VbNullStringis faster than "", although both will miss a cell that contains 'whereas IsEmptypicks this up
5. Optimising Loops (break ANDinto 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 Elsepath (ie a Falsetest 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
   

1. 比较vbBinaryCompare而不是vbTextCompare
2. 优化空字符串
3. 更换前测试
4. 使用内置常VbNullString量比 快""，尽管两者都会错过包含'而IsEmpty选择它的单元格
5. 优化循环（AND分成两个单独的IFs 以尽早逃脱）
6. 优化 If 测试首先返回最常见的布尔结果而不是通过Else路径运行（即False测试可能比 更合适True）

7. Mid$在作业的左侧使用。来自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
   

回答by aevanko

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

Trim是变体版本。如果在字符串上使用它，则需要进行不必要的转换。

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

Trim$是字符串版本。如果您在字符串上使用它，请使用它。

Fun side note: The Application.WorksheetFunctionversion of Trimdoes something different than VBA's Trim function.

有趣的旁注： 的Application.WorksheetFunction版本与TrimVBA 的 Trim 功能不同。

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

值得注意的是，Left/Left$、Mid/Mid$、Right/Right$也有变体/字符串版本。在经常循环或使用这些的函数中，如果您使用变体版本，您会注意到性能受到影响，尽管在现代 PC 中它不是很大，但是当您知道它会导致额外的步骤时不做某些事情仍然可以说是一种很好的做法你不需要。

回答by Justin Self

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

trim接受一个变体作为其参数，同时trim$接受一个字符串。

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

使用 trim$ 有一些性能优势（因为您不是从变体转换而来），但您可能不会注意到它们。

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.

我通过检查对象浏览器发现了这一点。在VBA编辑器中，按F2可以搜索函数，熟悉框架。

回答by Widor

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

我相信这个符号是 VB 时代的遗产，其中 $ 用于表示（或操作）字符串的对象/函数。

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.

在功能上，虽然我知道该$版本据称更快，但没有区别，因为它不需要从变体转换为字符串。