All built-in VBA Functions that deal with String handling/manipulation actually come in 2 'Flavors' such as:
Left/Left$, Right/Right$, Format/Format$, Mid/Mid$, etc.
This is a throw back to the early days of Basic, when before you could specify a Variable of a specific Data Type, programmers had to use type-specification characters rather than explicit Data Type Declarations. Some of these typical Declaration characters were '$' for String, '%' for Integer, '&' for Long Integer, '#' for Double, etc. A typical Declaration would look like:
- Dim MyDouble#
-
MyDouble# = 4 * Atn(1) 'Pi
-
Debug.Print MyDouble
This same system was applied to Function Names in that Functions ending with a '$' would return a String while its counterpart returned a Variant as in Right$ and Right. We no longer use the characters to indicate the Data Type but the Functions still remain. Left() returns a Variant and Left$() returns a String. The bottom line is that if
you know you are placing the return value of a String Handling Function into a String, always use the '$' version of the Function. A simple test will demonstrate this point:
- For i = 1 to 2000000000
-
strValue = Left$(strValue, 3)
-
Next i
will take approximately 1/2 as much time as in the 2nd code snippet below because you are forcing VBA to do an extra data type conversion (Variant to String)
- For i = 1 to 2000000000
-
strValue = Left(strValue, 3)
-
Next i
In my own tests the String flavor of this particular Function ran 53% faster than the Variant flavor.
