This tutorial will demonstrate how to use the Is Nothing statement in VBA
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.
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
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!