Dollar Signs at the End of VBA Functions

I got a question today from Microsoft Business Applications MVP Paul Murana (shared with permission, lightly edited for formatting):

I don't know if it is worthy of an article on your blog, but I would be interested to know if you personally use the syntax in VBA that allows you to declare the output type of functions. e.g. Left$(something, 10). Or do you just use Left(something, 10). Would be interested to hear how someone with your level of VBA understanding interprets the benefits of doing this.

Great question, Paul!  

Like most VBA users, I learned about these functions only after stumbling upon their usage in some online code sample and thinking, "What the heck is that?!?!"  So let's start by answering that question first and then we'll see where things go from there.

What's the Difference Between Left() and Left$()?

Both functions–Left() and Left$()–are members of the VBA standard library.  As such, the authoritative answer to this question comes from the Microsoft VBA Language Specification.

In particular, Section 6.1.2.11.1.20 Left$:

This function is functionally identical to the Left function, with the exception that the return type of the function is String rather than Variant.

For an even clearer explanation, you can look at the specification page for the Chr() and Chr$() functions:

Function Declaration

Function Chr(CharCode As Long) As Variant  
Function Chr$(CharCode As Long) As String 

Chr$ has the same runtime semantics as Chr, however the declared type of its function result is String rather than Variant.

Almost all of the built-in string functions come in these two flavors–one that returns a Variant and another (with a trailing dollar sign) that returns a String.

What Functions are Available with the Trailing Dollar Sign?

Below is a list of all the functions with a trailing dollar sign.  Each one returns a String data type.  Each one also has an equivalent function of the same name (but without the trailing dollar sign) that returns a Variant data type.

In addition to the Mid$() and MidB$() functions, there are also VBA statements of the same name.  That's why Mid$ and MidB$ appear twice in the table above.

Why Does Any of this Matter?

The Variant data type is the brain child of Joel Spolsky.

It exists because the early Microsoft Excel development team needed a data type that could be anything.  Otherwise, programming the logic that underlies the cell formula calculation system in Excel would have been a nightmare.

The problem is all that flexibility comes at a cost.

An Integer is a 16-bit number.  That means at compile time VBA knows that an Integer variable will consume two bytes in memory.  

A Variant could be anything.  To handle all the possibilities, VBA sets aside memory at compile time to hold metadata about the variable.  Part of that metadata is a pointer to a different location in memory where the variable's data actually lives.

Therefore, storing a 16-bit number as a Variant instead of an Integer will eat up a lot more memory.  Plus, the computer has to jump around following pointers to retrieve the data associated with a Variant variable.

In other words, storing and retrieving Variant data is much more inefficient than working with native data types.

Wow, Inefficient, You Say? That Sounds Bad

Inefficiency is bad.  But let's inject some perspective into the conversation.

In practical terms, your users will probably never notice any difference whatsoever in the speed of your code if you use the strongly-typed versions of these functions (the ones with the dollar signs) versus the Variant versions (the ones without).

I considered running a performance benchmark to compare the two, but somebody else had already done it.  Their results matched what I would have expected to see myself, so I feel like any additional work at this point would be wasted effort.

It turns out that using the strongly-typed functions yields a 66% performance improvement over the Variant versions.  That's right, you can shave two seconds off of a three-second process (assuming that your process involves a loop with about 100 million iterations):

Interesting information, if somewhat academic.  In my tests (20 sets each with 100,000 iterations of

rslt = Left(“12345678901234567890”, 18)

and

rslt = Left$(“12345678901234567890”, 18)

the string version at 0.019 seconds was about 66% faster than the variant version at 0.056 seconds.

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!

So What's the Best Practice?

The strongly-typed functions are more efficient than the Variant alternatives.  In practical terms, though, your users will never see a difference.

The strongly-typed functions are also, um, strongly typed.  This means that they will raise compile errors in certain situations where their Variant counterparts will not error until runtime.  Compile errors are better than runtime errors.

Since the Variant versus String difference only affects the return types and not the input parameters, you don't sacrifice any flexibility by using the strongly-typed versions of the functions.  UPDATE [2022-11-11]: The input parameters are different for most of these functions.  I just so happened to show the function definitions for Chr and Chr$, one of the few examples where the input parameters happen to be the same.  As Preissl Johann notes in the comments below, most of these functions–including Left vs. Left$–do treat input parameters differently.  That is, the strongly-typed versions (those with the dollar sign) only accept a String as an input parameter, while the other versions accept a Variant as the main input parameter.  This added flexibility, in fact, is a good reason to avoid the strongly-typed functions.

In other words, there is no one good reason not to use the strongly-typed functions.  

So, yes, I would have to say that using these functions is best practice.  And the Rubberduck agrees.

OK, But What Do You Do, Mr. NoLongerSet?

I almost never use them.

As I said above, there is no one *good* reason not to use the strongly-typed functions.  However, there is one very important *bad* reason not to use them:

I'm lazy.

Honestly, it's a pain in the neck (or wrist, as it were) to manipulate my fingers on the keyboard to enter a dollar sign at the end of these function calls.  

If you use Rubberduck and you can automate the replacement of every instance of Left() with Left$() then go right ahead.  It won't hurt anything.  In some rare cases it might even be meaningfully helpful.

But if you just don't feel like typing those dollar signs in manually whenever you call one of these functions, don't worry about it.  

Your users will never know the difference.  

And I promise I won't tell.

It will be our little secret.

Image by Manish Dhawan from Pixabay

UPDATE [2022-11-11]: Mea culpa.  Most of the weakly-typed versions of these functions do accept different input parameter data types than their strongly-typed cousins (i.e., Variant vs. String).  This added flexibility is a potentially good reason to choose them over their strongly-typed alternatives.  

But the primary reason is still that I'm lazy.