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:

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.