Worksheet_Calculate

brianbb

New Member
Joined
Nov 19, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
803
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 19, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
803
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

brianbb

New Member
Joined
Nov 19, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 19, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
803
Office Version
  1. 365
Platform
  1. Windows
I have a couple of ideas to help diagnose a freeze. At the beginning of the Subs that you showed here, add the line

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top