Worksheet_SelectionChange triggered by cut+paste does weird thing to static variables

As the title suggests, I was experimenting with the worksheet change event and static variables when I encountered an error doing a cut and paste. Here is some code that can reproduce the error (placed in a worksheet module):


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call StoreRange(Target)
End Sub

Private Sub StoreRange(Data As Range)
Static Current As Range
Static Previous As Range

Debug.Print "-------------------------------"
If Current Is Nothing Then
Debug.Print "Initial: Current = Nothing"
Else
Debug.Print "Initial: Current.Address:" & Current.Address
End If

If Previous Is Nothing Then
Debug.Print "Initial: Previous = Nothing"
Else
Debug.Print "Initial: Previous.Address:" & Previous.Address
End If

Set Previous = Current
Set Current = Data

If Current Is Nothing Then
Debug.Print "Updated: Current = Nothing"
Else
Debug.Print "Updated: Current.Address:" & Current.Address
End If

If Previous Is Nothing Then
Debug.Print "Updated: Previous = Nothing"
Else
Debug.Print "Updated: Previous.Address:" & Previous.Address
End If
Debug.Print "-------------------------------"
End Sub


This code runs error-free when I select various cells in the worksheet; it will obediently print messages containing addresses for the currently selected and previously selected ranges to the immediate window, e.g.:

-------------------------------
Initial: Current = Nothing
Initial: Previous = Nothing
Updated: Current.Address:$C$5
Updated: Previous = Nothing
-------------------------------
-------------------------------
Initial: Current.Address:$C$5
Initial: Previous = Nothing
Updated: Current.Address:$E$5
Updated: Previous.Address:$C$5
-------------------------------


However, if I select two or more cells, choose cut, select a single cell and choose paste the following error is produced:

Run-time error '424':

Object required


This error always occurs on line 13 ("Debug.Print "Initial: Current.Address:" & Current.Address"). In debug mode, if I hold the mouse cursor over "Current.Address", the message that appears states "Current.Address = <Object required>"

So I have a couple of questions about this issue:

1. It appears that the act of cutting and pasting is different to other actions (e.g. copying and pasting, which produces no errors in the above code) and it alters the static variable "Current" in some way; can anyone think of reason why this would be the case?

2. At the point when the runtime error 424 occurs, what state could the Variable "Current" possibly be in? From what I can see, it can't be set to nothing otherwise if would be handled by the preceeding If statement, yet it appears to me that the error is occuring because "Current" doesn't refer to anything (i.e. it is nothing). Is there a state between nothing and something?

Hi,

Let's look at the sequence of events and examine what Current holds at each point.

1. You select range D10.
Current is nothing.
Current is assigned a reference to cell D10.

2. You cut D10.
This doesn't raise the Worksheet_SelectionChange event; no change to Current.

3. You select range D5.
Current references D10.
Current is assigned a reference to D5.

4.You paste into D5.
This doesn't raise the Worksheet_SelectionChange event. However, the act of pasting effectively causes the existing D5 range to be deleted and replaced by a new D5 range with similar attributes (format etc) to D10. Current therefore now holds a reference to a deleted range.

5. You select range F5 to raise the Worksheet_SelectionChange event
You get a RTE when you try to call the Current.Address property.




You can simulate this in a more direct manner to see the same result:
1. Select an entire row, say Row 14.
Current address is now 14:14

2. Right click on one of the cells in the row and delete.
Current now references a range that has been directly deleted.

3. Select another cell to raise the Worksheet_SelectionChange event.
You get a RTE.



Hope that makes sense?

Yep that makes sense; thanks for the reply Colin.

I guess the lesson learned here is: don't store ranges as static variables.