Worksheet_Calculate

[brianbb]

I use a "Streaming" add-in that forces recalculation every 2 seconds.
I have a Macro that I want to run only when cell A1 changes.
My workbooks are very large and it would be best not to run extra code every 2 seconds
All suggestions are welcomed.

If the sheet recalculates every 2 seconds, is there really any difference between the two routines shown here?

CODE A

Private Sub Worksheet_Change(ByVal Target As Range)

If Workbooks("WB1.xlsb").Sheets("SHEET1").Range("A1") = "" Then Exit Sub
' Only execute if A1 <> ""

Call Macro1
End Sub

CODE B

Private Sub Worksheet_Calculate()

If Workbooks("WB1.xlsb").Sheets("SHEET1").Range("A1") = "" Then Exit Sub
' Only execute if A1 <> ""

Application.EnableEvents = False
Call Macro1
Application.EnableEvents = True
End Sub

 

[6StringJazzer]

Does A1 have a value, or a formula? If it has a formula, then Worksheet_Change will not detect that the result of the formula has changed. It will detect only if the content of the cell changes.

 

[brianbb]

I have 2 sheets in 2 different workbooks. Both workbooks are open at the same time.
I am using Worksheet_Calculate for the sheet/cell that has a formula.
I am using Worksheet_Change for the sheet/cell that is empty, awaiting a value.
The two are unrelated.

What I really need to know is... what is happening when the value of the first one (Worksheet_Calculate, with the formula) does not change? I think that I read that the Worksheet_Calculate routine kicks off after every sheet calculation, which is every 2 seconds.

I am worried that a collision might be occurring between one of these 2 routines, the thousands of calculations, and the add-in, because I get random times when excel freezes with no error codes and no ability to troubleshoot... everything just freezes.

Just in case, I was also wondering about the second one (Worksheet_Change, with the blank cell). I think I read that this routine does not kick off after every sheet calculation, so it would not be causing the collision, but of this I'm really not sure. It leaves me wondering what triggers the Worksheet_Change routine in the first place? ...or is it also simply checking after every sheet calculation?

 

[6StringJazzer]

what is happening when the value of the first one (Worksheet_Calculate, with the formula) does not change? I think that I read that the Worksheet_Calculate routine kicks off after every sheet calculation, which is every 2 seconds.

If your Streaming add-in forces a calculation then, yes, this Sub is going to run every 2 seconds, whether anything changes or not. I can't really help with that without knowing anything about your add-in.

I am worried that a collision might be occurring between one of these 2 routines, the thousands of calculations, and the add-in, because I get random times when excel freezes with no error codes and no ability to troubleshoot... everything just freezes.

If it's freezing you could be getting some kind of feedback loop where a change in one place causes an event in another place and they keep bouncing back and forth. Again, hard to tell without knowing anything about Streaming or Macro1. But you disable events before calling Macro1, which the correct thing to do there.

Just in case, I was also wondering about the second one (Worksheet_Change, with the blank cell). I think I read that this routine does not kick off after every sheet calculation, so it would not be causing the collision, but of this I'm really not sure. It leaves me wondering what triggers the Worksheet_Change routine in the first place? ...or is it also simply checking after every sheet calculation?

Worksheet_Change runs any time the content of any cell on that sheet changes. By "content," I mean the actual content of the cell; if a cell contains a formula, and the formula does not change but the value it returns does change, then it won't be considered a change event.

 

[brianbb]

By "content," I mean the actual content of the cell; if a cell contains a formula, and the formula does not change but the value it returns does change, then it won't be considered a change event.

This is exactly what I needed to know and could not find so clearly explained anywhere. Thank you. And thank you for the other responses as well. They were all informative and shows you are one of the few people out there that actually reads a thoughtful question before responding

 

[brianbb]

By the way, when I get a "freeze" one consistency is that if I click the "X" (upper right) to exit, I get a tiny box in the middle of the screen (too small to see anything except a cursor-type shape). If I hover over it, a red box with an "X" appears, but clicking on it does nothing. My only recovery is to close excel using task manager.

I say all this just in case it is indicative of the type of problem so I can continue to pursue a resolution.

 

[6StringJazzer]

I have a couple of ideas to help diagnose a freeze. At the beginning of the Subs that you showed here, add the line

DoEvents

This forces Excel to check and respond to any user input at that point. This would allow you to use CTRL+BREAK on your keyboard to interrupt code execution. If your code is in a runaway loop, this will interrupt it and give you a dialog box to open the code in the debugger. You can see where the code is, and you can step through it line by line to trace what is happening.

 

[brianbb]

Will do. Thanks again!