VBA Is Operator
In this Article
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
