How to Efficiently Check for Empty Strings in VBA

Mike Wolfe -

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.

Memory Implications

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

I use 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