如何在每个特定时间间隔内手动执行计算查询（自动更新功能） [英] How to execute the calculation query manually in every particular time interval (Auto Update functionality)

问题描述

我有一个要求，例如，当用户在excel中选择公式时，他想从UI设置一个时间间隔，他想要那个公式（计算查询）为每一组时间间隔执行（假设每5秒）。

I have a requirement like, when user selects the formula in excel, he would like to set a time interval from the UI and he wants that formula (Calculate query) to be executed for every set of time interval (let say every 5secs).

一般在Excel中，在构建公式（计算查询）后，每当用户想要查看更新数据，他需要按F9。但在我的情况下，用户希望 刷新 及时发生。他喜欢为要执行的公式（计算查询）设置时间
。

Generally in Excel, after building the formula (Calculated query), whenever User wants to see the updated data, he needs to press F9. but in my case, user wants the refresh  to happen in timely manner. As he like to set the time for that formula (Calculate Query) to be executed.

我的问题是，我们可以控制计算手动执行吗？ ？我们可以有自动更新功能吗？

My question is, Can we have the control over Calculation to be executed manually? can we have a Auto update functionality?

我知道我们有"Application.Calculation = xlCalculationManual"，但我如何在每个时间间隔调用公式？

I am aware that we have "Application.Calculation = xlCalculationManual", but how I can call the formula in every time interval?

我的公式将看起来像"ihData（"$ A $$ 6"，"$ B $ 1"，"$ B $ 2"，"field1"，"field2" ;，"field3"，"field4"，"field5"）"。

my formula will look like "ihData("$A$$6", "$B$1","$B$2", "field1", "field2", "field3", "field4", "field5")".

我想在每个特定的时间间隔内调用它...我该怎么做？

I want to call this for every particular time interval...how can I do this?

这个问题的任何解决方案都会被大量注释。

Any solution on this problem would be greatly appricated.

推荐答案

You can run a macro that forces a calculation periodically by using code like this.

运行CalcOnIntervals一次，然后回答问题，它将每隔X秒运行一次，直到你运行StopIt

Run CalcOnIntervals once, and answer the question, and it will run every X seconds after until you run StopIt

Public NextTime As Date
Public TimeInterval As Date

Sub CalcOnIntervals（）
   如果TimeInterval = 0则TimeInterval = Application.InputBox（"计算之间有多少秒？"，类型：= 1）/ 86400
    NextTime = Now + TimeInterval
    'MsgBox'计算！" '取消注释确认代码运行
    Application.CalculateFull
&NBSP; &NBSP; Application.OnTime NextTime，"CalcOnIntervals"<< End Sub

Sub SubIt（）
  &NBSP; Application.OnTime NextTime，"CalcOnIntervals"，Schedule：= False
End Sub

Public NextTime As Date
Public TimeInterval As Date

Sub CalcOnIntervals()
    If TimeInterval = 0 Then TimeInterval = Application.InputBox("How many seconds between calculations?", Type:=1) / 86400
    NextTime = Now + TimeInterval
    'MsgBox "Calculating!" 'Uncomment for confirmation of the code running
    Application.CalculateFull
    Application.OnTime NextTime, "CalcOnIntervals"
End Sub

Sub StopIt()
    Application.OnTime NextTime, "CalcOnIntervals", Schedule:=False
End Sub

这篇关于如何在每个特定时间间隔内手动执行计算查询（自动更新功能）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！