$ Or No $


Last Updated 6 Mar 2022                                                Difficulty level :   Moderate


This is the eleventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding.

This article was written in response to a number of forum posts asking what the difference was between the many pairs of VBA functions with or without a $ suffix and whether one was more efficient than the other.

For example
•   Left$/Left ; Right$/Right ; Mid$/Mid
•   Chr$/Chr ; Error$/Error ; Format$/Format
•   Hex$/Hex; Oct$/Oct; InputBox$/InputBox
•   LCase$/LCase; UCase$/UCase; Space$/Space
•   Str$/Str; String$/String; Time$/Time
•   LTrim$/LTrim; RTrim$/RTrim; Trim$/Trim

In each case, the function with a trailing $ suffix returns a string output whereas the no $ function returns a variant

Variants can handle nulls natively whereas strings cannot do so.
If the data includes null values, you will get error 94 (invalid use of null) using the $ version of each function unless it is used with the Nz function (or similar method).


In this thread at Stackoverflow, vba - Difference between Left() and Left$() function in Access - Stack Overflow, one respondent (Adarsh Madrecha) suggested:
If you are dealing with string values, in VBA code, Left$() will be slightly more efficient, as it avoids the overhead/inefficiency associated with the Variant. However, if there is any chance that Nulls may be involved, use Left(), or else explicitly handle the Null with something such as Nz().

However another member (Gustav Brock) replied:
They both run at the same speed, about 12 million iterations per second, thus of no importance. Actually, if many samples are recorded, Left in average is a fraction faster than Left$. Use Left$ if you wish to raise an error on a parameter value of Null, otherwise save your typing.

I decided to test the contrasting claims about relative efficiency

I ran two sets of tests to compare the times needed to run a select query using two different versions of the same 4 functions: Left$/Left; Mid$/Mid; LCase$/LCase; Format$/Format. Each query produces the same results.

The tests were run on a very large table of over 2.6 million UK postcodes with indexed fields, so each test is fast to run.
The tests were looped 100 times for each time measurement then repeated 20 times to calculate average times.


In this example, there were no null values therefore the results could be compared directly In each case, the results were very conistent so the stanard devation was small.


Here are the average times displayed as a report:


As the results indicate, the differences in times are negligible.
In terms of speed, there really is no advantage using the $ versions of each function

However, a third respondent in the same thread (Mathieu Guindon) wrote:
Left$ forces you to treat a string as a string, which forces you to deal with NULL values early on, before you pass them to something that wants a String and blows up with a type mismatch or invalid use of null. That "save your typing" advice also extends to "rely on implicit type conversions everywhere and use Variant instead of String when you're passing values to functions and procedures. Can't agree with that

These points are also clearly valid.
In the end, it is probably personal preference as to which approach works better for each individual developer



NOTE:
No download file is available for this test as the linked Postcodes table is huge - about 1.6 GB!



Feedback

Please use the contact form below to let me know whether you found this article useful or if you have any questions/comments.

Do let me know if there are any errors

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 9 Mar 2022



Return to Speed Test List Page 11 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Return to Top