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?

 

[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'm afraid it didn't really changed the same situation

 

[Jeffrey Mahoney]

You may have to turn off automatic calculation for the sheets you need to control and then manually update them as needed. To turn off sheet calcs:
Developer - Properties - EnableCalculation=False

Then you can use VBA to manually calculate

Dim WS As Worksheet
  
  Set WS = Sheets("Setup")
  
  WS.EnableCalculation = True
  WS.Calculate
  WS.EnableCalculation = False

 

[dikken20]

You may have to turn off automatic calculation for the sheets you need to control and then manually update them as needed. To turn off sheet calcs:
Developer - Properties - EnableCalculation=False

Then you can use VBA to manually calculate

Dim WS As Worksheet
 
  Set WS = Sheets("Setup")
 
  WS.EnableCalculation = True
  WS.Calculate
  WS.EnableCalculation = False

OK I'll give it a try, where should I put that code? in what module?

 

[Jeffrey Mahoney]

If you create a macro button to do this then it would be in the SHEET level. Otherwise if you add the code to a standard module, you'll have to run it manually or add a keystroke command to the macro.