Thanks for opening this forum up to other MVP authors, Dick. As some of you may know by now, I tend to ramble, so I’ll do my best to keep this entry short and to the point.
VB(A) is notoriously inefficient when working with Strings, so anything we can do to speed up our code is helpful. One often overlooked way to improve efficiency when working with Strings in VBA is the use of the “$ version” of String Functions.
As you may have noticed, String Functions have 2 forms: one with and one without a $ at the end. For example:
Method 1:
Method 2:
While both of these methods will give you the desired result, Method 2 is much faster (over 40% faster than Method 1 in my limited testing). Why? The Left() function returns a Variant data type with subtype String, and the Left$() function returns a String data type. You may want to use the Variant version of the function when dealing with Null values (as the Variant version can return a Null where the String version will generate a runtime error), but I can’t think of too many reasons to use the Variant version.
Here are the functions that have $ versions:
Chr$ | ChrB$ | Command$ |
CurDir$ | Date$ | Dir$ |
Error$ | Format$ | Hex$ |
Input$ | InputB$ | LCase$ |
Left$ | LeftB$ | LTrim$ |
Mid$ | MidB$ | Oct$ |
Right$ | RightB$ | RTrim$ |
Space$ | Str$ | String$ |
Time$ | Trim$ | UCase$ |
You should find this list of functions under the topic “Returning Strings from Functions” in VBA help (XL2003).
Interestingly, functions such as Replace(), Join(), and StrReverse() have $ versions, but they seem to be equivalent to their non-$ versions (both return Strings). If you select a String Function in code and hit F1, you can tell if a true $ version of the function exists. If the help indicates that the function returns a “Variant (String)”, there should be a corresponding $ version that returns a String. If the help indicates that the function returns a String, then both versions return Strings.
Next time you’re using String Functions, think about whether you can utilize the $ version of the function to speed up your code.
It really seems like I should have know this already but I didn’t. Thanks so much for this. Hopefully I’ll put it to good use right away!
Interesting information, if somewhat academic. In my tests (20 sets each with 100,000 iterations of
and
the string version at 0.019 seconds was about 66% faster than the variant version at 0.056 seconds.
And, yet, how important is this performance issue in the grand scheme of things?
In the 20 sets each with 100,000 iterations, the Left function took an average of 5.59E-07 seconds — yes, that’s correct, 0.559 microseconds, i.e., one-half of one millionth of one second!
Finally, some compatibility notes: (1) Left$ is not recognized by VB Script. (2) The same test as above in a VB.Net Windows program yielded results for Left that matched those for Left$. 0.015625 for 100,000 iterations and between 0.09375 and 0.109375 for 1,000,000 iterations.
Ummmm…don’t I have anything better to do?
Contrarian – How much better would Windows be if it were 66% faster? I know we’re talking milliseconds at best, but performance issues add up over the long run. If you’re doing heavy file I/O in VB(A), it’s a good idea to use Left$ instead of Left. I use it all the time because it’s very little extra effort and makes my code more efficient.
And no, it won’t work in VBScript, as VBScript doesn’t support explicit data types – everything is a Variant.
You’re correct – using the $ makes no difference in VB.NET.
I can’t comment on whether you have anything better to do or not. :)
is there a way to name a range via formula? I am importing data into excel and formulas pass just fine menu commands do not. I have read in other places on the web that the name is really an undisplayed formula (http://www.j-walk.com/ss/excel/odd/odd10.htm). Does anyone know the syntax?
Very Useful information, Thank you very much.
By the same token it’s faster to test
If Len(somestring) = 0 Then
than to test
If somestring = “” Then