PDA

View Full Version : Cut&Paste breaks EnableEvents



smc2911
03-09-2006, 06:24 PM
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
Sub restoreEvents()
getResult
Application.EnableEvents = True
End Sub

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? :think:

Sean.

malik641
03-09-2006, 09:40 PM
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:

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
.....:think: Don't know what to tell you, but here's something else to think about.

smc2911
03-09-2006, 10:44 PM
Curiouser and curiouser. I tried stepping through after doing a cut and paste and this is what I found:

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


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:

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


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
03-09-2006, 11:02 PM
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
03-10-2006, 02:17 AM
I have been using Excel 2003. malik641, what version are you running (as you seem to be able to replicate my problem)?

Sean.

smc2911
03-10-2006, 02:20 AM
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
03-10-2006, 04:07 AM
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


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


no problems

smc2911
03-10-2006, 04:29 AM
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:


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
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
03-10-2006, 04:54 AM
You have to remove the disabling of events from getResult, otherwise there is no point.

johnske
03-10-2006, 06:14 AM
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)
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
You shouldn't have any problems.

HTH,
John :)

malik641
03-10-2006, 07:12 AM
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
03-10-2006, 02:32 PM
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. :mkay

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:
Enter values into a few cells the Scratch Zone
Copy the yellow range and paste it onto the white cell
Enter some more values
Delete the contents of the range C16 to 17 (the new yellow range)
Delete the contents of cells B5 to F5
Repeat steps 1 to 3 (although of course the white cell is now yellow!)Here's what I observe when I follow the process:
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
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
Behaviour as expected: result cell updates, logs normal.
Behaviour as expected.
Everything as expected, error does not occur at step 2.Do others experience the same behaviour? The key points of confusion I have are:
What is the 1004 error?
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
03-12-2006, 04:30 PM
...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).

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 Not sure that really helps me! I also tried error trapping in the getLast function, but never caught anything there.

Sean.

TonyJollans
03-13-2006, 06:58 AM
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
03-13-2006, 02:25 PM
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
03-13-2006, 02:36 PM
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
03-13-2006, 03:18 PM
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
03-13-2006, 03:23 PM
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
03-13-2006, 05:27 PM
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 haveMe.Calculate ' or Application.Calculate if you wantsomewhere 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
03-14-2006, 08:35 AM
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.

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
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!

TonyJollans
03-14-2006, 09:48 AM
Uncommenting the Application.Calculation = xlCalculationAutomatic line switches calculation to automatic (of course). The problem I see seems to be caused by calculation being automatic before the Worksheet Change event runs - actually stopping it running, so it does no good setting it to manual within the routine.

Pasting (by pressing Enter) in a worksheet always clears the Windows Clipboard - it's just the way Excel works.

malik641
03-14-2006, 10:53 AM
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!
Well then I stand corrected, sorry Sean :doh:

smc2911
03-14-2006, 02:20 PM
Don't worry malik641, the logic error suggestion was entirely reasonable: I had myself assumed for a long time that this is what the problem was (as Event handling often leads to somewhat subtle errors). In fact, in posting to the forum I very much hoped that the problem would be a logic error as then I could fix my pricing model. My "PS" was just expressing a reflection of how this issue has been driving me bananas. I greatly appreciate all the time people are putting into my problem, and I do how that letting my frustration slip from time to time doesn't annoy. Bear with me everyone!

Tony's right about the manual calculation: I thought that setting calculation to manual in the Worksheet_Change routine stopped the error, but this was in fact only because I had already set calculation to manual. So, as Tony says, the whether or not the error occurs depends on the state of Application.Calculation prior to triggering Worksheet_Change. Unfortunately, this doesn't help me with my pricing model as I cannot rely on my users having Calculation set to manual.

The clipboard clearing thing is actually more subtle than I thought. As Tony says, clearing the clipboard is normal Excel behaviour if you press enter to paste, but not if you use ctrl-v which was sometimes occuring for me. I then discovered a curious thing. A fresh spreadsheet with nothing more than the following code will clear the clipboard when pasting with ctrl-v.

Private Sub Worksheet_Change(ByVal rng As Excel.Range)
Application.Calculation = xlCalculationManual
End Sub
With no Worksheet_Change routine (or commenting out the xlCalculationManual line) the clipboard is not cleared when pasting with ctrl-v.

TonyJollans
03-15-2006, 03:48 AM
Well, it's actually nothing to do with the Paste per se.

Setting Application.Calculation in code (as well as various other actions - in code and via the UI - for example pasting by pressing Enter) fires an implicit Application.CutCopyMode = False. In this case it is triggered by code in the Change event which in turn is triggered by the paste.

smc2911
03-15-2006, 04:02 AM
Seems like an odd feature: what if you want to turn Calculation off but retain the contents of the clipboard. Is there any way to suppress ths implicit Application.CutCopyMode or at least save the clipboard contents?

TonyJollans
03-15-2006, 04:51 AM
IMHO everything about the 'marching ants' round the current copied selection is slightly odd, but the copied data are maintained in the Office Clipboard and can be 'recovered' via the UI. I would point you to my article on the clipboard but the articles haven't yet been restored since the upgrade to the site.