Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
We noticed that if Automatic Calculation is enabled and data is
entered into a cell followed by the "Enter" key, the sequence is that the SheetCalculate event is fired first and then the SheetChange event. Wheres if the user clicks a different cell after entering the data (instead of hitting "Enter") the order is reversed with the SheetCalc event only firing after the SheetChange event. We need to handle the input to write back to some storage, but when the user is hitting "Enter" the data is retrieved from the storage first since the CalcHandler fires first and hence we lose the value the user entered in that cell. Is there some way inside the SheetCalculate handler for us to differentiate whether there is a SheetChange event that is about to be called? Is there some other suggestion as to how we can handle this case? thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Hmm, not straightforward!
First thought was to compare target & selection addresses, if not same suggests the user clicked another cell whilst in edit mode, with the implication the event order is Change Calc. But respective addresses are also not same if user presses say an arrow key (& various others) whilst in edit mode. But the event order is the same as Enter key, ie Calc Change, ie your "SheetChange event is about to be called" after Calc. Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! Regards, Peter T wrote in message ups.com... We noticed that if Automatic Calculation is enabled and data is entered into a cell followed by the "Enter" key, the sequence is that the SheetCalculate event is fired first and then the SheetChange event. Wheres if the user clicks a different cell after entering the data (instead of hitting "Enter") the order is reversed with the SheetCalc event only firing after the SheetChange event. We need to handle the input to write back to some storage, but when the user is hitting "Enter" the data is retrieved from the storage first since the CalcHandler fires first and hence we lose the value the user entered in that cell. Is there some way inside the SheetCalculate handler for us to differentiate whether there is a SheetChange event that is about to be called? Is there some other suggestion as to how we can handle this case? thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Peter - first off, thanks for acknowledging and attempting to solve
this challenging Excel issue. If we can just solve it so it handles the Enter key and the case wher eyou click of the sale after data entry, I'll worry about any other cases. But I can't even solve those! The idea of comparing Target and selection doesn't work because the value of Target is already changed when the SheetChange event gets fired and the new value entered is lost. We have to keep the behavior since that is how it used to work when we didn't support AutoCalc and now that we are going to support AutoCalc and ManualCalc, we have to handle this case. If anyone else is up to the challenge please help! thanks. On Oct 9, 7:44 pm, "Peter T" <peter_t@discussions wrote: Hmm, not straightforward! First thought was to compare target & selection addresses, if not same suggests the user clicked another cell whilst in edit mode, with the implication the event order is Change Calc. But respective addresses are also not same if user presses say an arrow key (& various others) whilst in edit mode. But the event order is the same as Enter key, ie Calc Change, ie your "SheetChange event is about to be called" after Calc. Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! Regards, Peter T wrote in message ups.com... We noticed that if Automatic Calculation is enabled and data is entered into a cell followed by the "Enter" key, the sequence is that the SheetCalculate event is fired first and then the SheetChange event. Wheres if the user clicks a different cell after entering the data (instead of hitting "Enter") the order is reversed with the SheetCalc event only firing after the SheetChange event. We need to handle the input to write back to some storage, but when the user is hitting "Enter" the data is retrieved from the storage first since the CalcHandler fires first and hence we lose the value the user entered in that cell. Is there some way inside the SheetCalculate handler for us to differentiate whether there is a SheetChange event that is about to be called? Is there some other suggestion as to how we can handle this case? thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Peter - first off, thanks for acknowledging and attempting to solve
this challenging Excel issue. If we can just solve it so it handles the Enter key and the case where you click off the cell after data entry, I'll worry about any other cases. But I can't even solve those! The idea of comparing Target and Selection doesn't work because the value of Target is already changed when the SheetChange event gets fired and the new value entered is lost. We have to keep the behavior since that is how it used to work when we didn't support AutoCalc and now that we are going to support AutoCalc and ManualCalc, we have to handle this case. If anyone else is up to the challenge please help! thanks. On Oct 9, 7:44 pm, "Peter T" <peter_t@discussions wrote: Hmm, not straightforward! First thought was to compare target & selection addresses, if not same suggests the user clicked another cell whilst in edit mode, with the implication the event order is Change Calc. But respective addresses are also not same if user presses say an arrow key (& various others) whilst in edit mode. But the event order is the same as Enter key, ie Calc Change, ie your "SheetChange event is about to be called" after Calc. Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! Regards, Peter T wrote in message ups.com... We noticed that if Automatic Calculation is enabled and data is entered into a cell followed by the "Enter" key, the sequence is that the SheetCalculate event is fired first and then the SheetChange event. Wheres if the user clicks a different cell after entering the data (instead of hitting "Enter") the order is reversed with the SheetCalc event only firing after the SheetChange event. We need to handle the input to write back to some storage, but when the user is hitting "Enter" the data is retrieved from the storage first since the CalcHandler fires first and hence we lose the value the user entered in that cell. Is there some way inside the SheetCalculate handler for us to differentiate whether there is a SheetChange event that is about to be called? Is there some other suggestion as to how we can handle this case? thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
How strange, seems I forgot to post the event routines in my previous post!
Annoyingly I didn't keep a copy of the code, will try and recreate later. The idea of comparing Target and selection doesn't work because the value of Target is already changed I had in mind to compare Target & selection addresses, which may or may not be the same depending on how the edit was completed. Whilst it the code I worked out previously went a long way to establishing what you wanted to know, it was far from foolproof. Regards, Peter T wrote in message ups.com... Peter - first off, thanks for acknowledging and attempting to solve this challenging Excel issue. If we can just solve it so it handles the Enter key and the case wher eyou click of the sale after data entry, I'll worry about any other cases. But I can't even solve those! The idea of comparing Target and selection doesn't work because the value of Target is already changed when the SheetChange event gets fired and the new value entered is lost. We have to keep the behavior since that is how it used to work when we didn't support AutoCalc and now that we are going to support AutoCalc and ManualCalc, we have to handle this case. If anyone else is up to the challenge please help! thanks. On Oct 9, 7:44 pm, "Peter T" <peter_t@discussions wrote: Hmm, not straightforward! First thought was to compare target & selection addresses, if not same suggests the user clicked another cell whilst in edit mode, with the implication the event order is Change Calc. But respective addresses are also not same if user presses say an arrow key (& various others) whilst in edit mode. But the event order is the same as Enter key, ie Calc Change, ie your "SheetChange event is about to be called" after Calc. Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! Regards, Peter T wrote in message ups.com... We noticed that if Automatic Calculation is enabled and data is entered into a cell followed by the "Enter" key, the sequence is that the SheetCalculate event is fired first and then the SheetChange event. Wheres if the user clicks a different cell after entering the data (instead of hitting "Enter") the order is reversed with the SheetCalc event only firing after the SheetChange event. We need to handle the input to write back to some storage, but when the user is hitting "Enter" the data is retrieved from the storage first since the CalcHandler fires first and hence we lose the value the user entered in that cell. Is there some way inside the SheetCalculate handler for us to differentiate whether there is a SheetChange event that is about to be called? Is there some other suggestion as to how we can handle this case? thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. Would you mind walking me through your idea? I'll code it up once I understand it. thanks! On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote: OK, I've tried to recreate the code I forgot to post last time. As the previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Just try the code as-is in the ThisWorkbook module of a new workbook.
Put the cursor in SetUpTest and run F5 Change cells in A1:A10 to trigger a calculation due to changing formula result in B1 My debug results: for most edit changes, including complete with Enter, or a press of any of the 'move' keys gives debug pair- Calculate, Change event will follow = True Change, Calculate will follow = False type new value, exit edit mode by clicking another cell gives debug pair- Change, Calculate will follow = True Calculate, Change event will follow = False IOW seems predicate the order of the subsequent event as you asked. But as I mentioned, the caveats for a few scenarios need to be sorted out. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. In light testing I almost always found the API correctly returns the 'move' keypress (if there was one), I assume as the change event fires before the key is released. I'm not sure it would if the key is released very fast or if the Calculate event will take a long time to process your other code. But even if it does that shouldn't matter, I think. If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow. I'll leave that for you to test. Anyway, for me it seems to be working reasonably well, but sadly 'reasonably well' does not of course mean reliably in all scenarios. Whilst I've no doubt it can be improved I'm not confident it can be made bullet-proof! Regards, Peter T wrote in message oups.com... Peter - thanks for working on sample code, but I'm not sure I understand your solution. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. Would you mind walking me through your idea? I'll code it up once I understand it. thanks! On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote: OK, I've tried to recreate the code I forgot to post last time. As the previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Thanks for explaining! Here's what is not clear. You state:
"If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow." If I simply hit F9, then the sheetcalc event gets fired and mbFlag is false, but no change event is going to follow since there was no data entry. At this point don't worry about all the different scenarios - I just need to differentiate between the following 3 cases which cause a calc: 1) F9 2) data entry is done followed by enter 3) data entry is done followed by a mouse clicking away On Oct 11, 7:35 pm, "Peter T" <peter_t@discussions wrote: Just try the code as-is in the ThisWorkbook module of a new workbook. Put the cursor in SetUpTest and run F5 Change cells in A1:A10 to trigger a calculation due to changing formula result in B1 My debug results: for most edit changes, including complete with Enter, or a press of any of the 'move' keys gives debug pair- Calculate, Change event will follow = True Change, Calculate will follow = False type new value, exit edit mode by clicking another cell gives debug pair- Change, Calculate will follow = True Calculate, Change event will follow = False IOW seems predicate the order of the subsequent event as you asked. But as I mentioned, the caveats for a few scenarios need to be sorted out. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. In light testing I almost always found the API correctly returns the 'move' keypress (if there was one), I assume as the change event fires before the key is released. I'm not sure it would if the key is released very fast or if the Calculate event will take a long time to process your other code. But even if it does that shouldn't matter, I think. If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow. I'll leave that for you to test. Anyway, for me it seems to be working reasonably well, but sadly 'reasonably well' does not of course mean reliably in all scenarios. Whilst I've no doubt it can be improved I'm not confident it can be made bullet-proof! Regards, Peter T wrote in message oups.com... Peter - thanks for working on sample code, but I'm not sure I understand your solution. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. Would you mind walking me through your idea? I'll code it up once I understand it. thanks! On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote: OK, I've tried to recreate the code I forgot to post last time. As the previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
I didn't think about F9, or Ctrl-Alt-F9
OK, with a manual calc the change event will not occur at all, either before or after the Calc event. Replace the calc event code with the following - Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim bF9 As Boolean Dim bChangeEventNext As Boolean bF9 = GetKeyState32(vbKeyF9) < 0 bChangeEventNext = (Not mbFlag And (Not bF9)) Debug.Print "Calculate, Change event will follow = " & bChangeEventNext If mbFlag Then Debug.Print mbFlag = False End Sub For me, the above in tandem with the other code now correctly differentiates between the 3 cases you indicated, ie calc-change or change-calc, though there still remain some scenarios that will falsely indicate a change event is to follow the calc event. You say you "only" need to differentiate between those three cases, but surely you also need to distinguish data entry completed with the various 'move' keys, which the code aims to do. Regards, Peter T wrote in message oups.com... Thanks for explaining! Here's what is not clear. You state: "If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow." If I simply hit F9, then the sheetcalc event gets fired and mbFlag is false, but no change event is going to follow since there was no data entry. At this point don't worry about all the different scenarios - I just need to differentiate between the following 3 cases which cause a calc: 1) F9 2) data entry is done followed by enter 3) data entry is done followed by a mouse clicking away On Oct 11, 7:35 pm, "Peter T" <peter_t@discussions wrote: Just try the code as-is in the ThisWorkbook module of a new workbook. Put the cursor in SetUpTest and run F5 Change cells in A1:A10 to trigger a calculation due to changing formula result in B1 My debug results: for most edit changes, including complete with Enter, or a press of any of the 'move' keys gives debug pair- Calculate, Change event will follow = True Change, Calculate will follow = False type new value, exit edit mode by clicking another cell gives debug pair- Change, Calculate will follow = True Calculate, Change event will follow = False IOW seems predicate the order of the subsequent event as you asked. But as I mentioned, the caveats for a few scenarios need to be sorted out. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. In light testing I almost always found the API correctly returns the 'move' keypress (if there was one), I assume as the change event fires before the key is released. I'm not sure it would if the key is released very fast or if the Calculate event will take a long time to process your other code. But even if it does that shouldn't matter, I think. If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow. I'll leave that for you to test. Anyway, for me it seems to be working reasonably well, but sadly 'reasonably well' does not of course mean reliably in all scenarios. Whilst I've no doubt it can be improved I'm not confident it can be made bullet-proof! Regards, Peter T wrote in message oups.com... Peter - thanks for working on sample code, but I'm not sure I understand your solution. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. Would you mind walking me through your idea? I'll code it up once I understand it. thanks! On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote: OK, I've tried to recreate the code I forgot to post last time. As the previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Peter - thank you very much for your help. I have implemented your
solution on our code (modified, but the basic idea is what you suggested) which is actually a C# Add-In and it is working well. Of course it may not be fool-proof, but I think it's the best we can accompish to combat this bizzare Excel behavior. On Oct 12, 11:03 am, "Peter T" <peter_t@discussions wrote: I didn't think about F9, or Ctrl-Alt-F9 OK, with a manual calc the change event will not occur at all, either before or after the Calc event. Replace the calc event code with the following - Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim bF9 As Boolean Dim bChangeEventNext As Boolean bF9 = GetKeyState32(vbKeyF9) < 0 bChangeEventNext = (Not mbFlag And (Not bF9)) Debug.Print "Calculate, Change event will follow = " & bChangeEventNext If mbFlag Then Debug.Print mbFlag = False End Sub For me, the above in tandem with the other code now correctly differentiates between the 3 cases you indicated, ie calc-change or change-calc, though there still remain some scenarios that will falsely indicate a change event is to follow the calc event. You say you "only" need to differentiate between those three cases, but surely you also need to distinguish data entry completed with the various 'move' keys, which the code aims to do. Regards, Peter T wrote in message oups.com... Thanks for explaining! Here's what is not clear. You state: "If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow." If I simply hit F9, then the sheetcalc event gets fired and mbFlag is false, but no change event is going to follow since there was no data entry. At this point don't worry about all the different scenarios - I just need to differentiate between the following 3 cases which cause a calc: 1) F9 2) data entry is done followed by enter 3) data entry is done followed by a mouse clicking away On Oct 11, 7:35 pm, "Peter T" <peter_t@discussions wrote: Just try the code as-is in the ThisWorkbook module of a new workbook. Put the cursor in SetUpTest and run F5 Change cells in A1:A10 to trigger a calculation due to changing formula result in B1 My debug results: for most edit changes, including complete with Enter, or a press of any of the 'move' keys gives debug pair- Calculate, Change event will follow = True Change, Calculate will follow = False type new value, exit edit mode by clicking another cell gives debug pair- Change, Calculate will follow = True Calculate, Change event will follow = False IOW seems predicate the order of the subsequent event as you asked. But as I mentioned, the caveats for a few scenarios need to be sorted out. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. In light testing I almost always found the API correctly returns the 'move' keypress (if there was one), I assume as the change event fires before the key is released. I'm not sure it would if the key is released very fast or if the Calculate event will take a long time to process your other code. But even if it does that shouldn't matter, I think. If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow. I'll leave that for you to test. Anyway, for me it seems to be working reasonably well, but sadly 'reasonably well' does not of course mean reliably in all scenarios. Whilst I've no doubt it can be improved I'm not confident it can be made bullet-proof! Regards, Peter T wrote in message roups.com... Peter - thanks for working on sample code, but I'm not sure I understand your solution. I see you are handling the Windows API GetKeyState in the SheetChange event, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. Would you mind walking me through your idea? I'll code it up once I understand it. thanks! On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote: OK, I've tried to recreate the code I forgot to post last time. As the previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Event Firing with Automatic Calculation
Peter - thank you very much for your help. I have implemented your
solution on our code (modified, but the basic idea is what you suggested) which is actually a C# Add-In and it is working well. Of course it may not be fool-proof, but I think it's the best we can accomplish to combat this bizzare Excel behavior. On Oct 12, 11:03 am, "Peter T" <peter_t@discussions wrote: I didn't think about F9, or Ctrl-Alt-F9 OK, with a manual calc the change event will not occur at all, either before or after the Calc event. Replace the calc event code with the following - Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim bF9 As Boolean Dim bChangeEventNext As Boolean bF9 = GetKeyState32(vbKeyF9) < 0 bChangeEventNext = (Not mbFlag And (Not bF9)) Debug.Print "Calculate, Change event will follow = " & bChangeEventNext If mbFlag Then Debug.Print mbFlag = False End Sub For me, the above in tandem with the other code now correctly differentiates between the 3 cases you indicated, ie calc-change or change-calc, though there still remain some scenarios that will falsely indicate a change event is to follow the calc event. You say you "only" need to differentiate between those three cases, but surely you also need to distinguish data entry completed with the various 'move' keys, which the code aims to do. Regards, Peter T wrote in message oups.com... Thanks for explaining! Here's what is not clear. You state: "If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow." If I simply hit F9, then the sheetcalc event gets fired and mbFlag is false, but no change event is going to follow since there was no data entry. At this point don't worry about all the different scenarios - I just need to differentiate between the following 3 cases which cause a calc: 1) F9 2) data entry is done followed by enter 3) data entry is done followed by a mouse clicking away On Oct 11, 7:35 pm, "Peter T" <peter_t@discussions wrote: Just try the code as-is in the ThisWorkbook module of a new workbook. Put the cursor in SetUpTest and run F5 Change cells in A1:A10 to trigger a calculation due to changing formula result in B1 My debug results: for most edit changes, including complete with Enter, or a press of any of the 'move' keys gives debug pair- Calculate, Change event will follow = True Change, Calculate will follow = False type new value, exit edit mode by clicking another cell gives debug pair- Change, Calculate will follow = True Calculate, Change event will follow = False IOW seems predicate the order of the subsequent event as you asked. But as I mentioned, the caveats for a few scenarios need to be sorted out. I see you are handling the Windows API GetKeyState in theSheetChangeevent, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. In light testing I almost always found the API correctly returns the 'move' keypress (if there was one), I assume as the change event fires before the key is released. I'm not sure it would if the key is released very fast or if the Calculate event will take a long time to process your other code. But even if it does that shouldn't matter, I think. If the calc event fires before the change event, mbFlag will be false which signifies the change event is about to follow. I'll leave that for you to test. Anyway, for me it seems to be working reasonably well, but sadly 'reasonably well' does not of course mean reliably in all scenarios. Whilst I've no doubt it can be improved I'm not confident it can be made bullet-proof! Regards, Peter T wrote in message roups.com... Peter - thanks for working on sample code, but I'm not sure I understand your solution. I see you are handling the Windows API GetKeyState in theSheetChangeevent, but in my experience that event fires after you have released the key and the KeyState would not indicate any key as being pressed. Would you mind walking me through your idea? I'll code it up once I understand it. thanks! On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote: OK, I've tried to recreate the code I forgot to post last time. As the previously posted comments/caveats are important I'll re-post them - [from first post] Just for ideas, have a go with this in the Worksheet module (later almost certainly will want to adapt to sheet module events). It's very important to define the range of changing precedent cells that will trigger a calc, otherwise the flag will be made a false positive for some future calc event. In a new wb run the setup routine, then edit/exit-edit A1:A10 in various ways. This needs a lot of testing, I've haven't, but already I see problems that needs sorting, eg: - If values are pasted, the event order becomes Change Calc, code as written wrongly sets the flag - edit a precedent cell but don't change its value, exit edit mode by clicking another cell, will set the flag true in advance of a non-existent calculate event ('cos the change event fired even though the value didn't change). Could get messy if need to trap previous values to determine 'real' changes. - Undo ? Thinking about it, even if the all above can be fixed I wouldn't be at all surprised if there are yet more problems to cater for, are you sure you can't re-think your overall method ! ' worksheet module code Private Declare Function GetKeyState32 Lib "user32" _ Alias "GetKeyState" (ByVal vKey As Integer) As Integer Dim mbFlag As Boolean ' in a new wb, run SetupTest, edit A1:10, complete entry with Enter ' or leave cell in edit mode with keys like arrow, PageDown etc ' and by clicking anbother cell Sub SetUpTest() Names.Add "Precedents", Range("A1:A10") Range("B1").Formula = "=SUM(A1:A10)" End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Debug.Print "Calculate, Change event will follow = " & Not mbFlag If mbFlag Then Debug.Print mbFlag = False End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim bKeyPress As Boolean Dim k As Long, i As Long Dim ky(0 To 10) As Long On Error GoTo errExit mbFlag = False If Not Intersect(Target, Range("Precedents")) Is Nothing Then If Selection.Address < Target.Address Then ky(0) = GetKeyState32(vbKeyTab) ky(1) = GetKeyState32(vbKeyLeft) ky(2) = GetKeyState32(vbKeyRight) ky(3) = GetKeyState32(vbKeyUp) ky(4) = GetKeyState32(vbKeyDown) ky(5) = GetKeyState32(vbKeyHome) ky(7) = GetKeyState32(vbKeyEnd) ky(8) = GetKeyState32(vbKeyPageUp) ky(9) = GetKeyState32(vbKeyPageDown) If Application.MoveAfterReturn Then ky(10) = GetKeyState32(vbKeyReturn) End If For i = 0 To 10 If ky(i) < 0 Then bKeyPress = True k = i ' indicates the move key in the array if required Exit For End If Next mbFlag = Not bKeyPress End If End If errExit: Debug.Print "Change, Calculate will follow = " & mbFlag If Not mbFlag Then Debug.Print End Sub 'Undo' is setting the flag the 'wrong' way but I think can work around to correct, but if code is doing stuff to clear the undo stack that won't be required. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Event Firing with Automatic Calculation | Excel Programming | |||
Click event on menu item is lost after first time firing of the event | Excel Programming | |||
Change Event not firing | Excel Programming | |||
Event sometimes stops firing? | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |