[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Tips-and-Tricks)

---

1. Statement

String function with trailing “$” character are faster than the corresponding “$-less”.

2. Description

The official Visual Basic documentation seems to encourage the use of the “$-less” string functions, such as Left, Right, or Mid, instead of their Left$, LTrim$, and UCase$ counterparts which extract a sub-string from the beginning, the end, or the middle of the source string.

If you follow this advice, however, be aware that the former returns a Variant value which, when used in string functions, must be converted to a string. This is a time-consuming process that gives you nothing in return.

Similar reasoning applies to other functions that exist in both forms, including LCase, UCase, LTrim, RTrim, Trim, Chr, Format, Space and String.

If processing time is a critical factor, you should opt for the "old-style" string functions that are faster. On the other hand, VB.NET does not accept string functions whose name ends with "$".

3. Performance test

Sub Test_FunctionsDollarLess()
'------------------------------------------------------------------------------
'Declare
    Dim i               As Long                     'Loop counter
    Dim MyString        As String                   'Test string
    Dim MaxIter         As Long                     'Max # of iterations
    Dim iMethod         As Integer                  'Performance method
    Dim cPM             As cPerformanceMonitor      'Performance tracker
'------------------------------------------------------------------------------
'Initialize
    MaxIter = 100000
    iMethod = 5
    Set cPM = New cPerformanceMonitor
'------------------------------------------------------------------------------
'Test1
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        MyString = "0123456789"
        MyString = Mid(MyString, 3, 4)
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - Testing ""MID"" "
'Test2
    MyString = "0123456789"
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        MyString = "0123456789"
        MyString = Mid$(MyString, 3, 4)
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - Testing ""MID$"" "
'------------------------------------------------------------------------------
'Exit
    Set cPM = Nothing
End Sub

4. Results

Looping 100,000 times:

• 00:00:00 - 010 ms - 883 µs - 500 ns - Testing "MID"
• 00:00:00 - 005 ms - 878 µs - 800 ns - Testing "MID$"

5. Conclusions

Improvement: about 2 times faster.

Impact: around 5 milliseconds.

The test shows a very poor performance increase.

Using string functions with trailing “$” could be considered a “best practice”.

On the other hand, using “$-less” functions could be more future-proof.

---

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Tips-and-Tricks)