Is Your Cell Blank Or Empty?

Two pet peeves of mine are the way the terms [unique and distinct] and [empty and blank] are interchangeably used in Excel. They have different meanings so it’s really confusing when the name of a function or utility suggests it will do one thing when, in fact, it does the other.

I’ve defined unique and distinct on previous posts such as this one, so I’ll put those to one side and concentrate today’s rant post on empty and blank. Before I highlight the inconsistencies between ISBLANK() , COUNTBLANK() and VBA.IsEmpty() , here are my definitions of empty and blank cells:

• An empty cell can be defined as a cell that contains absolutely nothing: no constant, no formula and no prefix character.
• A blank cell can be defined as a cell which may be empty, or may contain a prefix character or a null string (formula result or constant).

Note that the formatting is ignored. These definitions are just my own – so please don’t take them as gospel – but they’re based on the behaviour of VBA.IsEmpty() and COUNTBLANK().

Is A Cell Empty?

In a worksheet, the best way to check is a cell is empty is to use the ISBLANK() worksheet function:

Row 3 indicates what is in the corresponding column in row 4.

The Range.Value and Range.Value2 properties return a Variant/Empty when the given cell is empty, so the best way to check if a cell is empty in VBA is to use the VBA.Information.IsEmpty() function on their output:

Sub foo()

    Debug.Print IsEmpty(Sheet1.Range("A4").Value2)   'False
    Debug.Print IsEmpty(Sheet1.Range("B4").Value2)   'False
    Debug.Print IsEmpty(Sheet1.Range("C4").Value2)   'False
    Debug.Print IsEmpty(Sheet1.Range("D4").Value2)   'True

End Sub

The ISBLANK() worksheet function and VBA.IsEmpty() give exactly the same results so, unless one considers blank and empty to mean the same thing, they’re obviously inconsistently named. By my own definition, ISBLANK() should’ve been called ISEMPTY(). It’s worth mentioning that the ISBLANK() worksheet function isn’t available in VBA via the Application.WorksheetFunction class: presumably because Microsoft recognised that its functionality was already covered by VBA.IsEmpty() which is faster to call from VBA.

Is A Cell Blank?

The COUNTBLANK() worksheet function does not behave in a consistent manner with its ISBLANK() counterpart. COUNTBLANK() will count empty cells, cells with a null string and cells which contain a prefix character. Yeah, I know, it’s pretty confusing.  The upside is we can use COUNTBLANK() to check if a cell is actually blank:

An easy way to check if a cell is blank from VBA is to call the Range.Value (or Range.Value2) property and compare the result to the VBA.Constants.vbNullString constant:

Sub foo()

    Debug.Print IsBlank(Sheet1.Range("A4"))  'False
    Debug.Print IsBlank(Sheet1.Range("B4"))  'True
    Debug.Print IsBlank(Sheet1.Range("C4"))  'True
    Debug.Print IsBlank(Sheet1.Range("D4"))  'True

End Sub

Public Function IsBlank(ByRef rngToCheck As Range) As Boolean
    IsBlank = (CStr(rngToCheck.Cells(1).Value2) = vbNullString)
End Function

It’s more efficient within VBA to do it this way than to call the Application.WorksheetFunction.CountBlank() method.

Does A Cell Contain A Null String?

I’ve mentioned null strings a few times so I better explain what they are. A null string is a zero-length string that could be a constant or the result of a formula. For example, this formula returns a null string:

=""

If you copy that formula and paste special values then the cell will contain a constant null string. You can also sometimes get null strings when you import data from an external source, so they’re worth knowing about.

The HasNullString() function below will return True if a cell contains a null string. If you want formula results to be ignored (ie. check for constants only) then pass True into the blnConstantsOnly parameter . If the cell has a prefix character then the function will return False.

Public Function HasNullString( _
                ByRef rngToCheck As Range, _
                Optional ByVal blnConstantsOnly As Boolean = False _
                            ) As Boolean

    Dim rngFirstCell As Range
    Dim strToCheck As String
    Dim varToCheck As Variant

    Set rngFirstCell = rngToCheck.Cells(1)
    varToCheck = rngFirstCell.Value2

    If Not IsEmpty(varToCheck) Then

        If blnConstantsOnly Then
            strToCheck = rngFirstCell.Formula
        Else
            strToCheck = CStr(varToCheck)
        End If

        If strToCheck = vbNullString Then
            HasNullString = (LenB(rngFirstCell.PrefixCharacter) = 0)
        End If

    End If

End Function

So, am I just being an old fuddy-duddy or do these inconsistencies annoy you too?

Advertisement