VBA is Nothing

This tutorial will demonstrate how to use the Is Nothing statement in VBA

The VBA Is Nothing statement uses the VBA “Is” Operator and checks to see an object has been assigned to an object variable.

Sub CheckObject
Dim rng as Range
If rng Is Nothing then
  Msgbox "Range not assigned"
End If
End Sub

We can also use Not with Is Nothing with an If statement to make sure that a Range has been assigned to the range variable we declared and then run the code that we wish to run if that variable has been assigned.

Sub CheckAssignedObject
  Dim rng as Range
  Set rng = Range("A1:A6")
  If Not rng Is Nothing then
    '' do some code here
  End If
End Sub

We can use the Is Nothing statement for any type of object.  It can be extremely useful in preventing errors in our code where an object might not be assigned to an object variable.

For example, we can use a worksheet variable, and assign it to the Active Sheet.  If we do this successfully, then we can select A2 in that sheet.

Sub CheckWorksheetObject 
Dim ws as Worksheet
Set ws = ActiveSheet
If Not ws Is Nothing then 
   ws.Range("A2").Select
End If
End Sub

In the code above, the cell A2 will be selected.  If we were to remove the line  “Set ws=ActiveSheet“, then the If statement would bypass that line of code and cell A2 would not be selected.

Is Nothing can also be used in other Microsoft Office applications such as PowerPoint, Outlook, Access and Word.  The following code checks to see if the Document Object has been assigned to the Active Word Document.

Sub CheckDocumentObject
Dim wdDoc as Document
Set wdDoc = ActiveDocument
If wdDoc Is Nothing then
  MsgBox "Document not assigned"
Else
  MsgBox "Document assigned"
End If
End Sub