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

---

1. Statement

Using Len(text) = 0 is the fastest way to test for an empty string.

2. Description

VBA shows the Function IsEmpty but cannot be used to test empty strings.

The “Empty” value is the state of a Variant variable when nothing has been assigned to it yet.

When declaring a variable as String, it is already allocated.

Example:

‘This code will not work
Sub CheckVarForEmpty()
    Dim stringVar As String
    If IsEmpty(stringVar) = True Then
        MsgBox "Variable Is Empty!"
    End If
End Sub
‘The following code, works:
Sub CheckVarForEmpty()
    Dim stringVar As String
    If stringVar = "" Then
        MsgBox "Variable Is Empty"
    End If
End Sub
‘This code works as well:
Sub CheckVarForEmpty()
    Dim stringVar As String
    If Len(stringVar) = 0 Then
        MsgBox "Variable Is Empty"
    End If
End Sub
‘Also the following code works (because we are testing a Variant):
Sub CheckVarForEmpty()
    Dim myVar
    If IsEmpty(myVar) = True Then
        MsgBox "MyVar is Empty!"
    End If
End Sub

3. Performance test

Sub Test_EmptyString()
'------------------------------------------------------------------------------
'Declare
    Dim i               As Long                     'Loop counter
    Dim MyString        As String                   'Test string
    Dim bIsEmpty        As Boolean
    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
    MyString = vbNullString
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        If MyString = "" Then bIsEmpty = True Else bIsEmpty = False
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - Testing """" "
'Test2
    MyString = vbNullString
    cPM.StartTimer (iMethod)
    For i = 1 To MaxIter
        If Len(MyString) = 0 Then bIsEmpty = True Else bIsEmpty = False
    Next i
    Debug.Print cPM.ElapsedTime(iMethod) & " - Testing Len(MyString)=0"
'------------------------------------------------------------------------------
'Exit
    Set cPM = Nothing
End Sub

4. Results

Looping 100,000 times:

• 00:00:00 - 003 ms - 201 µs - 300 ns - Testing ""
• 00:00:00 - 000 ms - 747 µs - 600 ns - Testing Len(MyString)=0

5. Conclusions

Improvement: about 4 times faster.

Impact: between 2 and 3 milliseconds.

The test shows a very poor performance increase.

---

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