VBA Is Operator

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.

Is Nothing

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:

Sub IsNothing()
Dim ws As Worksheet

'Set ws = ActiveSheet

If ws Is Nothing Then
    MsgBox "Not Assigned"
Else
    MsgBox "Assigned"
End If

End Sub

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
    MsgBox "Intersect"
End If

End Sub

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:

Sub CompareObjects()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

If ws1 Is ws2 Then
    MsgBox "Same WS"
Else
    MsgBox "Different WSs"
End If

End Sub

Of course the worksheet objects are not the same, so “Different WSs” is returned.

VBA Coding Made Easy

Stop 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!


Learn More!