Is That String Really Empty?

ball of string

While testing out some operations on a Variant, I ran across an interesting problem. One of the variables I used was a fixed-length string.

Turns out, this type of variable fails the Len test: it returns the predetermined length of the variable even if you don't set it to any value! I recognize, however, that this may be only a minor problem. Who uses fixed-length string variables that often anyway?

There's a method described here for figuring out if the string is variable- or fixed-length, but you need to do it inside the same function. What if you declared the variable at the module level? Or what if you want a generic function instead, so you can just call a function whenever you need to check it? Unfortunately, that page doesn't offer any solution. What follows is the best I could come up with.

Check if a String is Empty

Part of the problem is the definition of "empty". I define it as a string equal to "". This function determines if a string is empty (according to my criteria) and returns True or False accordingly.

Function IsStringEmpty(stringToCheck As String) As Boolean

  Dim i As Long
  Dim numberOfBlanks As Long

  ' if len is zero, must be empty
  If Len(stringToCheck) = 0 Then
    IsStringEmpty = True
    ' check if every character is Chr(0)
    ' if so, assume it is an uninitialized fixed-length string
    For i = 1 To Len(stringToCheck)
      If Mid$(stringToCheck, i, 1) = Chr(0) Then
        numberOfBlanks = numberOfBlanks + 1
      Else  ' a valid char, cannot be empty
        Exit Function
      End If
    Next i

    IsStringEmpty = (numberOfBlanks = Len(stringToCheck))

  End If

End Function

There are four possible conditions:

  1. A variable-length string that hasn't been set to any value: Len(string) = 0
  2. A variable-length string that has been set to some value, ex: "Hello World": Len(string) > 0
  3. A fixed-length string that hasn't been set to any value: Len(string) > 0!
  4. A fixed-length string that has been set to some value, ex: "Hello World": Len(string) > 0

In other words, variable-length strings behave according to (my) common sense, whereas fixed-length strings always return a length even if they haven't been set to anything.

What I found is that in condition #3, the value of string is actually a series of Chr(0) characters, equal to the fixed-length number.

You can see this behavior in the Locals Window: simply declare a fixed length string inside any procedure, then press F8 to start the procedure. You'll see the string variable automatically initialized with Chr(0) characters.

In only one case is the length of the string actually ever zero: a variable-length string that hasn't been set to any value. In that case, the length is zero and we can exit the function. This satisfies condition #1. Otherwise, we need to loop.

To speed things up, I tried to short circuit the function inside the For loop — if it runs into a character other than Chr(0), the function immediately exits and returns False because the string cannot possibly be empty. This handles condition #2 and #4.

The reason we total the number of Chr(0) values and compare it to the length of the string at the end is to avoid cases where Chr(0) is actually a valid part of the input string. Only cases where the entire string consists of Chr(0) will be caught, which meets condition #3. This of course is an assumption, because somewhere out there I'm sure a bunch of Chr(0) characters is a valid string to someone.


I hoped there was a better way to do this but IsEmpty returns false for both types of strings. I tried "string = Empty" but it works the same as Len: returns True for variable length strings and False for all other cases. The difference between Len and Empty is the subject of the next post.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Related Articles:

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 9 Comment(s) on Is That String Really Empty?:

  1. Theo Scribante writes:

    Use the following:

    If Len(Application.WorksheetFunction.Clean(stringToCheck)) = 0 Then

    Your whole routine can then become:

    Function IsStringEmpty(stringToCheck As String) As Boolean
     IsStringEmpty = (Len(Application.WorksheetFunction.Clean(stringToCheck)) = 0)
    End Function

    I prefer to use the following because I've had problems in the past with residual blanks in strings and cells:

    If Len(Application.WorksheetFunction.Clean(trim(stringToCheck))) = 0 Then

    Speaking of spaces, I've also encountered problems with higher order ASCII characters such as the non-breaking space character (ASCII 160), especially with data cut and pasted into Excel. Note that the worksheet CLEAN function only removes ASCII values 0-31.

    • Your solution is elegant, but Excel-specific. My function will work in Excel, Outlook, Access, Word, etc.

    • Theo Scribante writes:

      Then how about the following:

      Function IsStringEmpty(stringToCheck As String) As Boolean
          Dim numberOfBlanks As Long
          Dim stringLength As Long
          stringLength = Len(stringToCheck)
          If Len(stringToCheck) = 0 Then
              IsStringEmpty = True
              IsStringEmpty = (stringToCheck = String(stringLength, Chr(0)))
          End If
      End Function
      • I knew there was an easier way! Thanks for posting this.

        • Rick Rothstein (MVP - Excel) writes:

          What about this one-liner function?

          Function IsStringEmpty(stringToCheck As String) As Boolean
              IsStringEmpty = Len(Replace(stringToCheck, Chr(0), "")) = 0
          End Function
        • Theo Scribante writes:

          Short, sweet and elegant. Excellent.

        • Rick Rothstein (MVP - Excel) writes:

          Thanks for the nice words Theo. Although I think it is probably obvious, I just wanted to mention that, given how simple this code line is, a separate function to process it is not needed… it is trivial to perform the test directly within your code.

          If Len(Replace(StringToCheck, Chr(0), "")) Then
              ' StringToCheck is NOT empty, so do what you want to with its contents
              ' StringToCheck is really empty, so react accordingly
          End If
        • Rick Rothstein (MVP - Excel) writes:

          Here is another one-liner that is some two-and-a-half times faster executing than the one-liner I posted previously…

          Function IsStringEmpty(S As String) As Boolean
              IsStringEmpty = Not S Like "*[!" & Chr$(0) & "]*"
          End Function
  2. Rick, you just scored a hole in one in code golf.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel