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

---

1. Statement

Conventional strings are usually faster than fixed-length strings.

2. Description

Visual Basic manages two different types of strings:

• conventional variable-length strings;
• fixed-length strings.

Example:

Dim MyStringFix as String * 30

Dim MyStringVar as String

• A variable-length string takes only the memory it needs for its characters (it takes 10 additional bytes to hold other information about the string, including its length).
• A fixed-length string always takes a fixed amount of memory (60 bytes, in the preceding example since Unicode uses two bytes for each character).

All VBA native string functions can deal only with variable-length strings (fixed-length strings are something VBA isn’t aware of). When passing a fixed-length string to a VBA function, the compiler generates hidden statements that convert the argument into a temporary variable-length string.

3. Performance test

Sub Test_FixedString()
'------------------------------------------------------------------------------
'Declare
    Dim i               As Long                     'Loop counter
    Dim MyStringFix     As String * 50              'Fixed string
    Dim MyStringVar     As String                   'Variable string
    Dim MaxIter         As Long                     'Max # of iterations
    Dim iMethod         As Integer                  'Performance method
    Dim cPM             As cPerformanceMonitor      'Performance tracker
'------------------------------------------------------------------------------
'Initialize
    MaxIter = 100000000
    iMethod = 5
    Set cPM = New cPerformanceMonitor
'------------------------------------------------------------------------------
'Test1
    MyStringFix = "qwertyuiopasdfghjklzxcvbnm"
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        MyStringFix = vbNullString
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - fixed-lenght string"
'Test2
    MyStringVar = "qwertyuiopasdfghjklzxcvbnm"
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        MyStringVar = vbNullString
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - variable-lenght string"
'------------------------------------------------------------------------------
'Exit
    Set cPM = Nothing
End Sub

4. Results

The results are the following (looping 100,000,000 times):

• 00:00:02 - 566 ms - 522 µs - 400 ns - fixed-length string
• 00:00:00 - 808 ms - 758 µs - 800 ns - variable-length string

Using variable-length strings is about three times faster than fixed-length strings.

If we change the variable MaxIter to 1, we can notice that the difference is only about a few microseconds (fixed-length strings seem to be even faster):

• 00:00:00 - 000 ms - 004 µs - 000 ns - fixed-length string
• 00:00:00 - 000 ms - 006 µs - 500 ns - variable-length string

Even with all the overhead, fixed-length strings aren’t always going to make your program slower.

5. Conclusions

Impact: negligible

Unless we have many strings manipulation the performance improvement is negligible.

---

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