I’ve been writing some code to capture the event when a particular cell is changed in one of my Excel Worksheets and, using the power of the interpipes found a simple solution to do this. It looks something like this in code:-
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A10")) Is Nothing Then MsgBox "Hello" End If End Sub
Whilst this works perfectly and does what I want it to do I didn’t understand why I should use ‘If Not Intersect….Is Nothing’ when it would be simpler to use ‘If Intersect(Target, Range(“A1:A10”) > 0’.
I soon found out when I changed something outside of the target range (ie A1:A10) and Excel threw an error at me. Wondering why I had a bit of a browse on the internet and discovered the reason. The Intersect method returns a range object, but if there is no range found it will simply return an answer of nothing, which causes the error. Using this code ensures that, if nothing is returned, the IF statement can just end with no errors returned to Excel. Nice.
It’s probably not the most interesting post, but I thought I’d document it in case you or I ever come across it again. A full (and much more detailed) examination of the Intersect method in VBA can be found here.