Setting a Workbook Calculation timing

[dikken20]

Hello,

My sheet contains links from a third application and it's set to Automatic calculation.
New data retrieved every few seconds so I should get the data every second or close to it (One of the fields the sheet retrieved is "time" so I can know how updated is that data).

Yet, I found that the data is updated every 3 minutes (and more..) rather than every second or few seconds.
I changed the Automatic calculation to Manual calculation, Results: Every click on the Calculate Now results with a "new updated" data which is indeed updated from a few seconds ago rather than 3 minutes, like it should be in the first place.

I suppose that the Automatic calculation has an algorithm to decide when to calculate and that algorithm doesn't go into my favor,
So how can I set the sheet calculation to be immediate or at least to be every second or so?

 

[Jeffrey Mahoney]

You can add a macro to run on any time interval you want. OnTime is the command. It's kinda tricky. The great thing about OnTime is that it doesn't take up CPU resources in the background. You do need to manage it. If the interval is too short, you won't be able to do much because it's always doing an update. You have to make sure you stop the OnTime before closing the workbook or it will try to run after you close the workbook. I suggest adding a cell that stores the status to run the automatic updates or not. Also add a button that turns it on and off.

 

[dikken20]

You can add a macro to run on any time interval you want. OnTime is the command. It's kinda tricky. The great thing about OnTime is that it doesn't take up CPU resources in the background. You do need to manage it. If the interval is too short, you won't be able to do much because it's always doing an update. You have to make sure you stop the OnTime before closing the workbook or it will try to run after you close the workbook. I suggest adding a cell that stores the status to run the automatic updates or not. Also add a button that turns it on and off.

Thanks Jeffrey,

Correct me if I'm wrong, but the Macro you suggest will going to run all the time so I could not work/change the sheet as long as it is running, right?
Plus, in some cases it can cause an infinite loop if the update will take some time while the interval will be too small (even if interval will be 1 minute and the update will take longer than expected.. ), right?

 

[Jeffrey Mahoney]

The OnTime function does NOT prevent you from doing your normal activities in the workbook. It runs in the background until the timer has expired and then it does the update.

Yeah, if the update takes 10 seconds and you ask OnTime to run the update every 3 seconds, well, you'll get nothing done!

 

[dikken20]

The OnTime function does NOT prevent you from doing your normal activities in the workbook. It runs in the background until the timer has expired and then it does the update.

Yeah, if the update takes 10 seconds and you ask OnTime to run the update every 3 seconds, well, you'll get nothing done!

Seems like the real deal, Have you got some code for instance by any chance?
As my coding not that good I'm afraid

 

[Jeffrey Mahoney]

I just created some simple code. You'll need to change this based on your sheet names and range names. I created a sheet called Setup and named a range called "TimerStatus". The values can be TRUE or FALSE. It's best if you use data validation to restrict the entry.

I know you'll have questions.


Put this code in a standard module

'These variables must be declared in a standard module
Public NextTimeToRun As Variant
Public TimerStatus As Boolean


Sub SetTimer()
  
  TimerStatus = Sheets("Setup").Range("TimerStatus").Value
  
  If TimerStatus <> True Then Exit Sub
  
  NextTimeToRun = Now() + TimeValue("00:00:10")
  'MsgBox Now() & "   :   " & NextTimeToRun
  Application.OnTime NextTimeToRun, "DoTheUpdate"
  
End Sub


Function DoTheUpdate()
  
  TimerStatus = Sheets("Setup").Range("TimerStatus").Value
  
  'Test to see if the timer was turned off
  If TimerStatus = False Then
    Exit Function
  End If
  
  'Run the update to get new values.  Either call your sub or put your code here
  
  MsgBox "Hey there: " & Format(Now(), "hh:mm:ss")
  
  'Restart the timer for next time
  NextTimeToRun = Now() + TimeValue("00:00:10")
  Application.OnTime NextTimeToRun, "DoTheUpdate"
  
End Function

This code must be put in the "ThisWorkbook" module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Application.OnTime NextTimeToRun, "DoTheUpdate", schedule:=False
  On Error GoTo 0
End Sub

This code must be placed on a SHEET module (I created a sheet called Setup and named a range called "TimerStatus". The values can be TRUE or FALSE

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("TimerStatus")) Is Nothing Then
    TimerStatus = Range("TimerStatus").Value
    If TimerStatus = True Then Call SetTimer
  End If
End Sub

 

[Jeffrey Mahoney]

You've just been pulled into a higher plain of the VBA world. How will take the challenge?

 

[dikken20]

I just created some simple code. You'll need to change this based on your sheet names and range names. I created a sheet called Setup and named a range called "TimerStatus". The values can be TRUE or FALSE. It's best if you use data validation to restrict the entry.

I know you'll have questions.


Put this code in a standard module

'These variables must be declared in a standard module
Public NextTimeToRun As Variant
Public TimerStatus As Boolean


Sub SetTimer()
 
  TimerStatus = Sheets("Setup").Range("TimerStatus").Value
 
  If TimerStatus <> True Then Exit Sub
 
  NextTimeToRun = Now() + TimeValue("00:00:10")
  'MsgBox Now() & "   :   " & NextTimeToRun
  Application.OnTime NextTimeToRun, "DoTheUpdate"
 
End Sub


Function DoTheUpdate()
 
  TimerStatus = Sheets("Setup").Range("TimerStatus").Value
 
  'Test to see if the timer was turned off
  If TimerStatus = False Then
    Exit Function
  End If
 
  'Run the update to get new values.  Either call your sub or put your code here
 
  MsgBox "Hey there: " & Format(Now(), "hh:mm:ss")
 
  'Restart the timer for next time
  NextTimeToRun = Now() + TimeValue("00:00:10")
  Application.OnTime NextTimeToRun, "DoTheUpdate"
 
End Function

This code must be put in the "ThisWorkbook" module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Application.OnTime NextTimeToRun, "DoTheUpdate", schedule:=False
  On Error GoTo 0
End Sub

This code must be placed on a SHEET module (I created a sheet called Setup and named a range called "TimerStatus". The values can be TRUE or FALSE

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("TimerStatus")) Is Nothing Then
    TimerStatus = Range("TimerStatus").Value
    If TimerStatus = True Then Call SetTimer
  End If
End Sub

YEP You were right I do have questions

I suppose that Setup is the variable where the sheet located while the TimerStatus is the cell where I input the True/False to set this running or not? am I correct?
Will that update the entire sheets or only the Setup sheet? What If I would need this to update more than one sheet?

Is there any other input I need to fill?

Thank you so much for your help!

 

[Jeffrey Mahoney]

Setup is the sheet name. In my workbooks, all my global variables can be changed by the end user to control the macros. You are correct, TimerStatus is the cell where you input true or false to control the update. The macro I gave you is an example of how to control the OnTime. If you need to control separate updates, then I suggest you add some more named ranges on the Setup sheet to control each one. In the "DoTheUpdate" function you can test each of those variables to see if they need to be run.

How did the Update macro you had previously created operate? Did it have code for running separate sheets? If that code is in one Subroutine, then you may want to split it out into different SUBS so you can test the variables you added to the setup sheet and run individually. Or test for each variable in the same SUB, up to you.

 

[dikken20]

Setup is the sheet name. In my workbooks, all my global variables can be changed by the end user to control the macros. You are correct, TimerStatus is the cell where you input true or false to control the update. The macro I gave you is an example of how to control the OnTime. If you need to control separate updates, then I suggest you add some more named ranges on the Setup sheet to control each one. In the "DoTheUpdate" function you can test each of those variables to see if they need to be run.

How did the Update macro you had previously created operate? Did it have code for running separate sheets? If that code is in one Subroutine, then you may want to split it out into different SUBS so you can test the variables you added to the setup sheet and run individually. Or test for each variable in the same SUB, up to you.

I use DDE links from a third party application, so I should get the data automatically. Thing is that DDE technology is ancient and not that fast nor always updated.
When if I try to retrieve "too" much data, it resulted in a pretty high delay (5-7 minutes and more).

So I had to reduce the amount data I retrieve so I could get the most important data to be updated fast, yet 3 minutes is way too much time of delay.
I can bare 10-20 seconds of delay max, hopefully to 10 or less..

I'll try this code tomorrow as I cannot implement it at the moment and will let you know how it goes, Thanks again Jeffrey!!!