VBA Excel: How to test or check cell content

When you work with VBA (macros) in Excel, you often need to test or check cell content. Is the cell empty, is it a number, a text (string), a formula, an error, a date - or does it have a comment or conditional formatting?

Below are some examples. If you mark the code with the mouse, you can copy (CTRL+C) and paste (CTRL+V) it into a VBA module. The examples use cell A1 in the active sheet, but any cell or larger range can be used.

If you are viewing this page with a device with a small screen, some of the code lines may appear broken/wrapped, but they will be okay, once you paste them into a VBA module.

Sub CellCheck()
'Examples of cell checking

Dim rCell As Range
Dim sMyString As String

On Error GoTo ErrorHandle

'We set our range variable = cell A1 in the active sheet.
Set rCell = Range("A1")

'The following control tests if a cell is empty
'by testing the length of what may be in the cell.
'Therefore we use the property "Formula"
'instead of "Value". If you used "Value",
'it would say the cell is empty, even if
'it contains spaces/blanks.
If Len(rCell.Formula) = 0 Then
   MsgBox "Cell " & rCell.Address & " is empty."
End If

'You can make the same check using the VBA-function IsEmpty.
'IsEmpty returns True, if the cell is empty.
'IsEmpty is better than the control above, when it comes to
'the content of a cell, but not if it was a string variable.
'It is a matter of speed.
If IsEmpty(rCell) Then
   MsgBox "Cell " & rCell.Address & " is empty."
End If

'The next example checks if the cell contains a number,
'i.e. a numeric value.
If IsNumeric(rCell.Value) Then
   MsgBox "Cell " & rCell.Address & " is a numeric value."
End If

'This control checks if a cell contains an error, for instance 
'division by zero or a formula with a reference to a non-existing
'named cell.
If IsError(rCell.Value) Then
   MsgBox "Cell " & rCell.Address & " contains an error."
End If

'Here we check if a cell (or an expression) is a date.
If IsDate(rCell.Value) Then
   MsgBox "Cell " & rCell.Address & " is a date."
End If

'The following checks, if a cell contains text.
'If it isn't a numeric value or an error like division
'by zero, we assume it is text of the data type String.
'Date values can be mistaken for text, so if you want dates
'eliminated as well, you must add
'"IsDate(rCell.Value) = False" to your control.
If IsNumeric(rCell.Value) = False And _
IsError(rCell.Value) = False Then
'The VBA function Trim removes leading and trailing blanks.
'If the length after trimming is 0, the content was blanks only.
   sMyString = Trim(rCell.Value)
   If Len(sMyString) > 0 Then
      MsgBox "Cell " & rCell.Address & " is a text with " & _
      Len(sMyString) & " characters."
   Else
      MsgBox "The cell contains blanks only"
   End If
End If

'Checks if a cell has conditional formatting
If rCell.FormatConditions.Count > 0 Then
   MsgBox rCell.Address & " has conditional formatting."
Else
   MsgBox "No conditional formatting."
End If

'Checks if a cell contains a formula.
If rCell.HasFormula Then
   MsgBox "Cell " & rCell.Address & " contains a formula."
Else
   MsgBox "The cell has no formula."
End If

'Checks if a cell has a comment.
If rCell.Comment Is Nothing Then
   'If not, add a comment.
   With rCell.AddComment
      .Visible = False
      .Text "Comment added " & Date
   End With
Else
   MsgBox rCell.Address & " has a comment."
End If

BeforeExit:
Set rCell = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Error in procedure CellCheck."
Resume BeforeExit
End Sub

About cells

In the examples above I have written "rCell.Value" many times. Strictly speaking that is not necessary, because "Value" is a cell's default property, so I could have achieved the same just be writing "rCell".