Excel’s Worksheet_Change Intersect Event

As most of you are aware, if you want something to happen in excel when there’s a change in a specified range, you’re going to use –

If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then

But you’ve also thought why can’t I just use –

If Intersect(Target, Range(“A1:A10”)) Then

This is because failures aren’t handled. To understand what’s going on here, let’s run through a couple of examples in a standard module.


Let’s first look at how Nothing behaves –

Sub TestIntsct()
  Dim rngNew As Range
  Set rngNew = Nothing

    If rngNew Is Nothing Then
        MsgBox (rngNew)
    End If
End Sub

This results in Run-time error ‘91’: Object variable or With block variable not set.

So, we can’t do anything with a variable that’s set to Nothing, roger. Let’s try adding a Not to this –

Sub TestIntsct()
  Dim rngNew As Range
  Set rngNew = Nothing

    If Not rngNew Is Nothing Then
        MsgBox (rngNew)
    End If
End Sub

All right, no errors, but it also doesn’t do anything. Hmm. Let’s try making our variable not nothing-

Sub TestIntsct()
  Dim rngNew As Range
  Set rngNew = Range("A1")

    If Not rngNew Is Nothing Then
        MsgBox (rngNew)
    End If
End Sub

Okay, now we’re getting somewhere. If we try to do something with Nothing – we get errors. What about instead of checking that not something Is Not Nothing?

Sub TestIntsct() 
  Dim rngNew As Range 
  Set rngNew = Range("A1")

    If rngNew Is Not Nothing Then
        MsgBox (rngNew)
    End If
End Sub

Compile error: Invalid use of object (Nothing). We can’t check to see if something exists this way. We’re pretty much stuck checking If Not something Is Nothing.


Are you with me so far? Now, let’s apply these concepts using the Intersect method-

We know we can’t use the Object if it’s Nothing, so let’s check for Nothing and use a String in place of the Object –

Sub TestIntsct() 
  Dim rngNew As Range 
  Set rngNew = Intersect(Range("A1"), Range("A2"))

    If rngNew Is Nothing Then
        MsgBox ("Nothing!")
    Else: MsgBox (rngNew.Address)
    End If 
End Sub

It works! So we’ll just check for Nothing and use the Else: to do what we want when the Object isn’t Nothing

Sub TestIntsct() 
  Dim rngNew As Range 
  Set rngNew = Intersect(Range("A2"), Range("A1:A10"))

    If rngNew Is Nothing Then
        MsgBox ("Nothing!")
    Else: MsgBox (rngNew.Address)
    End If
End Sub

This works, but it seems like a pretty round-about way. Why not check if the Object is empty?

Sub TestIntsct() 
  Dim rngNew As Range 
  Set rngNew = Intersect(Range("A2"), Range("A1:A10"))

    If IsEmpty(rngNew) Then
        MsgBox ("Nothing!")
    Else: MsgBox (rngNew.Address)
    End If
End Sub

This works for an intersection! What about a Nothing?

Sub TestIntsct() 
  Dim rngNew As Range 
  Set rngNew = Intersect(Range("A1"), Range("A2"))

    If IsEmpty(rngNew) Then
        MsgBox ("Nothing!")
    Else: MsgBox (rngNew.Address)
    End If
End Sub

Run-time error ‘91’ again! IsEmpty can’t handle the error either.


Let’s apply these concepts to the Worksheet_Change event. First let’s see what happens when we exclude our Not argument –

That’s not right..

Neither is that! This is backwards! Let’s use the Not argument to see it work –

Now we’re cooking!

Internal Controls

As I touched on in Segregation of Duties, controls are the foundation for any risk or governance framework. Internal controls reside in every facet of the organization, visible and invisible, but what exactly are they? They are any action taken by management, the board, and other parties to provide reasonable assurance that business objectives will be achieved and that undesired events will be prevented or detected and corrected. Essentially, they are insurance policies for stakeholders against foreseeable risk.

The first type of internal control is a preventative control – something that prevents the occurrence of errors (both intentional and unintentional).  Most programs have a back-end database that is critical. Most of these databases are (ior should be) restricted to access by only authorized individuals. These access controls prevent unauthorized users from entering the environment and making changes. Another example is (usually) built into the off-boarding process: when a user is no longer an employee of the organization, all credentials for the individual are to be removed. This prevents the individual from using the credentials to access information that they would not need to access due to their status as a non-employee. In these examples, there is something in place (a system or process) that prevents objectives from being missed due to problems that may be caused by incorrect access level restrictions.

The second type of internal control is a detective control – something that detects the occurrence of errors. One common example of a detective control is the review process. If an error is made by a data entry clerk (intentionally or unintentionally), when the reviewer for the process reviews the data (prior to approval), the error will be detected. These can be detected by comparing the data to what is expected, historical data, or the information that is returned from the data. If these errors can be detected prior to being approved and used in other processes within the organization, the risk that objectives will be missed is reduced. It should also be noted that in this case, segregation of duties was paramount and may be considered to be a preventative control.

The third type of internal control is a corrective control – something that can correct an error that has been detected. An easy to understand example of a corrective control would be the restoration of a database after an error is detected. Perhaps a user made an error or some data became corrupted – this would (hopefully) be detected by a detective control. Once detected, one corrective action could be to restore the database with an earlier version prior to the error. A more complicated version might be data validation – this may include both detective and corrective controls. If a batch is received by a system, the system can check to see if the batch is what is expected, either by the type or by reconciliation. If the data cannot be reconciled to the source data, an error is detected and the transaction is denied or returned (corrective).

By understanding these three control types, we can better evaluate processes or systems for risk.