The VBA “Is” operator is used to compare two object references.
If the object references are the same, Is will return TRUE. If the object references are different, Is will return FALSE.
The most common use of the Is operator is to check if an object has been assigned to an object variable.
You can see how this works in the procedure below:
Dim ws As Worksheet
'Set ws = ActiveSheet
If ws Is Nothing Then
MsgBox "Not Assigned"
This is useful to prevent errors caused by an object not being assigned to the object variable.
Intersect – Is Nothing
Commonly, this is used with Worksheet Change Events to identify if the target range falls within a specified range.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1:a10")) Is Nothing Then
Is – Compare Objects
The Is operator can also be used to compare objects.
Let’s look at an example. Here we will assign two worksheets to worksheet objects rng1 and rng2, testing if the two worksheet objects store the same worksheet:
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
If ws1 Is ws2 Then
MsgBox "Same WS"
MsgBox "Different WSs"
Of course the worksheet objects are not the same, so “Different WSs” is returned.
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!