VBA中Trim（）和Trim $（）有什么区别？ [英] What's the difference between Trim() and Trim$() in VBA?

问题描述

在vba中 trim 和 trim $ 之间有什么区别？意外的是，当我在vba中使用left和trim功能时，编译器说不能找到项目或库

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")

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

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$

我想知道什么是修剪和修剪$。我想找到差异所以我开始google了，但结果是忽略$字母表。

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.

我只是好奇地知道它。我是怀疑修剪是vba功能和修剪$是excel表功能。但是我们有 Application.worksheetfunction 可以使用excel函数吗？任何人都可以区分修剪和修剪$。

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

推荐答案

虽然Issun已经回答了你的问题，我有足够的细节，我想发表，提供一个进一步的答案，而不是

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.

字符串版本 显着更快〜约10-30％，具体取决于多年来我测试中的数据类型。虽然这通常不明显，但是在大型数据集上运行时性能差异很大。所以对于我来说，使用字符串而不是变体版本是一个没有意义的事情。

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

我已经将这些功能与我的 public addins ，因为这些程序通常用于整个工作表，甚至整个工作簿

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. 比较 vbBinaryCompare 而不是 vbTextCompare


2. 优化空字符串


3. 替换
之前测试

4. 使用内置常量， VbNullString 比更快，虽然都会错过包含'的单元格eas IsEmpty 选择这个


5. 优化循环（break AND 分成两个
   
6. 优化如果测试首先返回最常见的布尔值结果，而不是运行 Else 路径（即 False 测试可能比 True ）



7. 在作业的左侧使用 Mid $ 。从 VBA的隐藏功能

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

这篇关于VBA中Trim（）和Trim $（）有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！