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
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
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.
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
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?
COUNTBLANK() worksheet function does not behave in a consistent manner with its
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.Value2) property and compare the result to the
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
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.
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
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?