Cut&Paste breaks EnableEvents

[smc2911]

I am experiencing some very strange behaviour with EnableEvents and am coming to the conclusion that I may have discovered an Excel bug. However, I know that Events can lead to quite subtle behaviour and would appreciate some thoughts.

I have attached a spreadsheet that doesn't do anything very useful, but is designed to exhibit the strange behaviour. With events enabled, if you enter values in the grey-shaded "Scratch Zone" a Worksheet_Change Sub for Sheet1 will update the Results cell with the number of non-zero cells in the Scratch Zone. As expected, if you this behaviour ceases if you click on the "Events Off" button, which simply calls
[vba]Sub restoreEvents()
getResult
Application.EnableEvents = True
End Sub[/vba]

Clicking "Reset" reactives events and updates the Results count.

So far, so good. But now try a copy and paste of, say, B9:C10 to D15. Events are silently disabled, preventing the call of the Worksheet_Change Sub. Clicking "Reset" will fix things, until such time as you copy & paste again. Surely copy & paste can't break events, because so many people use Events to capture changes arising from copy & paste? Aha, here's where it really gets strange. Cells contain B5 to F5 have a silly little VBA function called "getlast" which returns the row number (as labelled in column A) of the last non-zero/non-empty cell. It's the existence of a VBA function call in the sheet that seems to cause the problem. If you clear the contents of B5:F5, click reset and try copy & paste, then events will no longer be broken? The problem doesn't seem to depend on the particular VBA function used.

This seems to suggest that using VBA functions and Events is a dangerous mix. Any theories?

Sean.

[malik641]

You know....I don't know why I tried this, but I had to see what would happen. The copy & paste will work (after entering another value into another cell) if you comment out the Application.EnableEvents = False in your sub:

[VBA]Sub getResult()
'Application.EnableEvents = False
n = 0
For Each c In Range("scratch")
If c.Value <> 0 Then n = n + 1
Next
Range("result").Value = n
End Sub[/VBA]
..... Don't know what to tell you, but here's something else to think about.

[smc2911]

Curiouser and curiouser. I tried stepping through after doing a cut and paste and this is what I found:
[vba]
Sub getResult()
'Application.EnableEvents = False
n = 0
For Each c In Range("scratch")
If c.Value <> 0 Then n = n + 1
Next
' never got past here!!!
Range("result").Value = n
End Sub
[/vba]

After getting to the point marked "never got past here!!!", execution jumped to getLast to recalculate cells in row 5 which were affected by the changes in the Scratch Zone, but then never returned to finished execution of getResult, let alone the original Worksheet_Change (which would subsequently should set Application.EnableEvents = True).

Try this:
[vba]
Sub getResult()
'Application.EnableEvents = False
n = 0
Debug.Print "Got Here (point A)"
For Each c In Range("scratch")
If c.Value <> 0 Then n = n + 1
Next
Range("result").Value = n
Debug.Print "Got Here (point B)"
End Sub
[/vba]

Then do a cut & paste: you'll never get a "Got Here (point B)" in your immediate window.

This means that the enable events issue is really a side effect: by commenting it out, the result is that it never gets turned off. The real problem is that cut&paste messes with the code execution stack somehow.

Sean.

[johnske]

Hi Sean,

I downloaded your workbook, copied your original post so I could duplicate the actions without any mistakes and - didn't get the result you say you did - everything works as it should after a copy and paste.

(Excel 2000)

Regards,
John

[smc2911]

I have been using Excel 2003. malik641, what version are you running (as you seem to be able to replicate my problem)?

Sean.

[smc2911]

I downloaded your workbook, copied your original post so I could duplicate the actions without any mistakes and - didn't get the result you say you did - everything works as it should after a copy and paste.

John, did you also try the modified version of getResult with the Debug.Print calls? If so, did a cut&paste in the Scratch Zone produce a "Got Here Point A" and "Got Here Point B" in the immediate window?

Sean.

[Bob Phillips]

The problem seems to be being cauysed by the fact that when you do a cut-and-paste, it goes through the Worksheet_Change event, calls getResult as expected and when it updates the result cell, this calls getLast (again unexpected), but oddly it seems to go into the second code line, and when it exits it doesn't return to getResult. As getResult switches events off, they stay off.

To my mind, it is not a VBA or Excel bug, just logic errors in handling events. If you use

[vba]
Private Sub Worksheet_Change(ByVal rng As Excel.Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(rng, Range("scratch")) Is Nothing Then
getResult
End If
ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

no problems

[smc2911]

Tried the amendment, and don't quite agree that there are now no problems, as although EnableEvents remained enabled, the getResult routine still does not finish correctly, so that after the cut&paste the result cell does not have the correct value. Admittedly I didn't build any error handling into this demo workbook, but the aim here is simply to illustrate the peculiar effect.

Anyway, I'm still not convinced that it's not an Excel/VBA problem because:

• If the contents of cells B5 to F5 are deleted, the behaviour of cut and paste is different.
• Even with the suggested fix, the getResults function fails to complete and I can see no valid reason for this.

I have also tried the following:

[vba]
Private Sub Worksheet_Change(ByVal rng As Excel.Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(rng, Range("scratch")) Is Nothing Then
getResult
End If
Exit Sub
ws_exit:
Application.EnableEvents = True
Debug.Print Err.Description & " (" & Err.Number & ")"
End Sub
[/vba] If I try a cut&paste now, I hit reach ws_exit (but not if I simply edit cells). This indicates that an error has been triggered. In the immediate window I then have "Application-defined or object-defined error (1004)". I don't understand why the cut&paste should trigger an error.

Sean.

[Bob Phillips]

You have to remove the disabling of events from getResult, otherwise there is no point.

[johnske]

John, did you also try the modified version of getResult with the Debug.Print calls? If so, did a cut&paste in the Scratch Zone produce a "Got Here Point A" and "Got Here Point B" in the immediate window?

Sean.

Yes it did.

I agree with xld, the logic used is awry, keep your 'EnableEvents' statements in the one procedure and look at adding error handling as a matter of course. Use this in conjunction to the event mod he gave (note: you should also be using Option Explicit) [vba]
Option Explicit

Sub getResult()
Dim N As Long, c As Range
N = 0
For Each c In Range("scratch")
If c.Value <> 0 Then N = N + 1
Next
Range("result").Value = N
End Sub
[/vba] You shouldn't have any problems.

HTH,
John

[malik641]

Sean,

Using 2003, but after running the code with the error handler Bob provided (xld) I had no problem.

I have to agree with John and Bob with it being a logic problem. And BTW, I never came up with an error whether or not I deleted the Disabling of Events from the getResult.

[smc2911]

The points about robust error trapping and using Option Explicit are well made. I am actually a believer in both and have learned that it's worth being thorough event when throwing together a quick spreadsheet to illustrate a problem.

However, I am still stuck. So, sorry to labour the point but I have attached another version of the spreadsheet that incorporates suggestions made and would be very interested in people's experiences when doing the following, checking the Immediate Window in the Visual Basic Editor along the way:

1. Enter values into a few cells the Scratch Zone
2. Copy the yellow range and paste it onto the white cell
3. Enter some more values
4. Delete the contents of the range C16 to 17 (the new yellow range)
5. Delete the contents of cells B5 to F5
6. Repeat steps 1 to 3 (although of course the white cell is now yellow!)

Here's what I observe when I follow the process:

1. Result cell updates with the count of entries in the Scratch Zone. Logs in immediate window appear normal:
   11/03/2006 8:21:35 AM: (getResult) Subroutine completed
   11/03/2006 8:21:37 AM: (getResult) Subroutine commenced
2. Strange error occurs in logs:
   11/03/2006 8:21:49 AM: (getResult) Subroutine commenced
   11/03/2006 8:21:49 AM: (getResult) Error trapped: Application-defined or object-defined error (1004)
   and of course the result cell is not correctly updated
3. Behaviour as expected: result cell updates, logs normal.
4. Behaviour as expected.
5. Everything as expected, error does not occur at step 2.

Do others experience the same behaviour? The key points of confusion I have are:

1. What is the 1004 error?
2. Why does deleting the VBA formulas in cells B5 to F5 stop the error occurring?

I would also note that if the error trapping is removed, I would have expected the 1004 to flash up an error message box.

I seem to have got very carried away with numbered lists--sorry about that!

Sean.

P.S. Thanks very much for the assistance so far.

[smc2911]

...it's worth being thorough event when throwing together...

should read even.

I have continued to play around to try and hone in on the "Application-defined or object error". The following version of getResult would suggest that it is the assignment Range("result").Value = N that triggers the error (this version traps the error, with the On Error any later, the error is no longer trapped by getResult).

[vba]Sub getResult()
Dim N As Long, c, d As Range

Debug.Print Now() & ": (getResult) Subroutine commenced"
N = 0
For Each c In Range("scratch")
Set d = c
If c.Value <> 0 Then N = N + 1
Next
On Error GoTo getResult_Err
Range("result").Value = N
Debug.Print Now() & ": (getResult) Subroutine completed"
Exit Sub

getResult_Err:
Debug.Print d.Address
Debug.Print Now() & ": (getResult) " & Err.Description & " (" & Err.Number & ")"
Application.EnableEvents = True
End Sub[/vba] Not sure that really helps me! I also tried error trapping in the getLast function, but never caught anything there.

Sean.

[TonyJollans]

I have played around with this and there is definitely something funny going on.

There may be something strange in your workbook because I can't explain - or reproduce in another workbook - the error on the Range("result") line.

It does appear, however, that the Worksheet_Change event isn't firing properly following a Paste which triggers the UDF. It's like the calculation is suppressing the event - but it'll take a bit more investigation to pin it down precisely.

[smc2911]

Thanks for having a look at this. I'm pretty sure that the problem is not specific to the BugTest workbook as I built BugTest from scratch to replicate a problem I was having in another workbook. For interest, the original spreadsheet is a corporate loan pricing model, which has to allow for a lot of flexibility (multiple tranche transactions, variable exposure profiles, etc) and it makes heavy use of Workbook_Change to ensure pricing parameters are correctly updated. It also used a function somewhat like getResult to calculate the term of each tranche. I used a VBA function to do this because I couldn't come up with a simple formula to do the calculation (I might post that query as a separate thread!). I then found the cut & paste problem arose. For the moment I have addressed the problem by getting rid of the VBA function and doing the calculations in a somewhat painful way on the worksheet. I would still very much like to know why the problem arose in the first place!

Sean.

[johnske]

Hi Sean,

Little point in me trying this as I'm not able to duplicate any errors on my machine - but, out of curiosity - try referring to the ranges directly (e.g. B7:F20) instead of using the range names and see what happens

[TonyJollans]

If it's any help to anybody looking at this, I put a break in the getResult procedure at the point of failure and I could not update any cells in the worksheet at that point by any method in code (in the immediate window) - until I stepped through and set EnableEvents back to True at whhich point everything returned to normal.

[TonyJollans]

Also, although I haven't investigated this thoroughly, if I set Calculation to Manual it suppressed refreshing of the getLast values (as expected) and the Event seemed to fire correctly - it may be possible to find a workaround by manipulating this.

[TonyJollans]

The order of events (with a small e) has me a little confused here but ...

When you enter data into the scratch area it seems the automatic recalculation of the getLast UDFs runs, then the Worksheet_Change event fires - and all is well with the world.

When you paste data somewhere outside the scratch area, there is no automatic recalculation of the getLast UDFs required and the Worksheet_Change event fires - and all is well with the world.

When you paste data into the scratch area it seems that automatic recalculation of the getLast UDFs runs, then (maybe) the paste operation is finalised (clearing the clipboard if relevant, etc.) and somehow that switches off the Worksheet_Change - it does not fire and all is not well.

Now if you set Calculation to Manual and have[vba]Me.Calculate ' or Application.Calculate if you want[/vba]somewhere in Worksheet_Change event so that it always runs, the effect should be the same as having Automatic calculation. However, doing this appears to make all the right code run every time.

I think this is a bug in Excel 2003.

[smc2911]

Good pickup! This could explain why johnske did not experience the Application error: johnske, do you have calculation on manual? If so, try switching to automatic and see whether the error occurs.

I also noticed that the following code change eliminates the error.
[vba]
Private Sub Worksheet_Change(ByVal rng As Excel.Range)
On Error GoTo ws_Err

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
If Not Intersect(rng, Range("scratch")) Is Nothing Then
getResult
End If
Application.Calculate
'Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
ws_Err:
Debug.Print Now() & ": (Worksheet_Change) Error trap: " & Err.Description & " (" & Err.Number & ")"
Application.EnableEvents = True
End Sub
[/vba] However, if the Application.Calculation = xlCalculationAutomatic is uncommented the error returns. Weird!

Also I noticed that the clipboard is always cleared after cutting and pasting and I don't know why.

Sean.

P.S. I'm glad someone else is starting to think this is an Excel 2003 error rather than a logic error. I was starting to doubt my sanity!