In Advanced Visual Basic 6 (pp. 358-359), author Matthew Curland provides four different ways to check for empty strings:
Dim strTest As String 'Worst test If strTest = "" Then Beep 'Not much better If strTest = vbNullString Then Beep 'Very good If Len(strTest) = 0 Then Beep 'Best If LenB(strTest) = 0 Then Beep
Let's explore his reasoning...
How VBA Stores Strings
To understand why Curland rated those four options the way he did, we must first learn how VBA handles strings at the lowest level.
Here's how a VBA string is stored in memory:
- 4-byte length prefix: stores the length of the string
- Variable number of two-byte Unicode characters: the string's actual value
- 2-byte NULL termination character: two 0 bytes
The VBA string variable is a pointer to the first Unicode character of the string's actual value.
The first two string tests in the sample code require VBA to use a string-comparison call. This is much less efficient than simply reading the four bytes of the length prefix, especially if
strTest contains a very large string.
Here's more from Matthew Curland:
The Len function, which returns the number of characters in a string, occupies a whopping total of six assembly instructions in the VB runtime. LenB is even smaller, coming in at five instructions (LenB doesn't need to divide the byte count by two to get the character count).
The comparison with "" is considered worse than the comparison with vbNullString because "" occupies six bytes of memory; vbNullString is simply a NULL string pointer. The two types are functionally equivalent in VB, but vbNullString is preferred because it doesn't require any memory.
My Personal Preference
Len(strTest) = 0 in my own code.
For high-performance string-handling code, use
LenB(strTest) = 0. Short of that, though, you'll see no noticeable performance boost by shaving one assembly instruction from your VBA code.
Cover image created with Microsoft Designer