Excel VBA 函数仅在更改时更新

Excel VBA Function update only on change

提问by Irate Pirate

I have a function that I have implemented as a VBA module which takes one cell as a parameter and queries a web services to return a result. As the source of the data is remote, this function can take a few seconds to return.

我有一个作为 VBA 模块实现的函数，该模块将一个单元格作为参数并查询 Web 服务以返回结果。由于数据源是远程的，此函数可能需要几秒钟才能返回。

The problem is that some actions, such as deleting any column, seem to cause every formula to recalculate, even if no change is needed. When my sheet has several hundred rows, this can take several minutes during which time Excel is unresponsive.

问题是某些操作（例如删除任何列）似乎会导致每个公式重新计算，即使不需要更改也是如此。当我的工作表有几百行时，这可能需要几分钟时间，在此期间 Excel 没有响应。

I would like to find some way to detect that no change is needed and skip this step. Some things I have tried:

我想找到某种方法来检测不需要更改并跳过此步骤。我尝试过的一些事情：

• Determine the current value of the cell to be changed, from which I could determine if an update is needed. But ActiveCell.Value is blank, and attempts to pass the cell itself as a second parameter are met with a circular reference warning.
• Created a second column to hold the old input value, which is then passed into the function and compared with the new value. However, functions are not able to update this old value on success, and attempts to update with only formulas are always met with circular reference warnings. Furthermore, it doesn't seem that a function can return a "no change", so I would need to store and use the old result.

• 确定要更改的单元格的当前值，我可以从中确定是否需要更新。但是 ActiveCell.Value 是空白的，并且尝试将单元格本身作为第二个参数传递会遇到循环引用警告。
• 创建第二列来保存旧的输入值，然后将其传递给函数并与新值进行比较。但是，函数无法在成功时更新此旧值，并且尝试仅使用公式进行更新总是会遇到循环引用警告。此外，函数似乎无法返回“无更改”，因此我需要存储和使用旧结果。

Is there any way to prevent some actions from updating every formula on the sheet?

有什么办法可以防止某些操作更新工作表上的每个公式？

Other requirements:

其他需求：

• There may be a way to disable all formula automatic calculation, but I definitely want each row to update as soon as the parameter cell is entered. I also don't want to force the user to push a button to update the function.
• I tried doing a custom spreadsheet in Visual Studio, but found the deployment too difficult to maintain for my user base. Solution must be entirely contained in a macro-enabled workbook.
• Excel versions to use the sheet will be 2007 and 2010.

• 可能有一种方法可以禁用所有公式自动计算，但我绝对希望在输入参数单元格后立即更新每一行。我也不想强迫用户按下按钮来更新功能。
• 我尝试在 Visual Studio 中制作自定义电子表格，但发现部署对于我的用户群来说太难维护了。解决方案必须完全包含在启用宏的工作簿中。
• 使用该工作表的 Excel 版本为 2007 和 2010。

采纳答案by MikeD

I would

我会

1. systematically block recalculation by setting File / Options / Formulas / Workbook Calculation to "Manual" and switch off "Recalculation on Save" as well

2. create a Private Sub Worksheet_Change(ByVal Target As Range)and determine, under which conditions a recalculation should take place (Target.Worksheet.Calculate) - for example determine the coord's of the target (using Target.Columnand Target.Row), and call the query from inside the Worksheet_Change trigger.

3. Eventually I would put the recalculation instruction in a Sub of its own and make a button into the sheet so the user can - on his/her wish - manually trigger recalculation (just in case the F9 key isn't known to the users)

4. It is also thinkable to use a global Boolean variable need_Querywhich is set by the Worksheet_Change trigger If Target.Row = X And Target.Column = Y(= the parameter) and have the query function examine (and reset) that Boolean, and go in the query only If need_Query Then, otherwise exit without action. Make sure that need_Query is set FALSE at worksheet load time, too.

5. Finally, you could convert the query function into a Suband formulate some more complex conditions in the Worksheet_Change trigger to fire it (e.g. if a certain column changes, etc.).

1. 通过将文件/选项/公式/工作簿计算设置为“手动”并关闭“保存时重新计算”来系统地阻止重新计算

2. 创建一个Private Sub Worksheet_Change(ByVal Target As Range)并确定在哪些条件下应该进行重新计算 ( Target.Worksheet.Calculate) - 例如确定目标的坐标（使用Target.Column和Target.Row），并从 Worksheet_Change 触发器内部调用查询。

3. 最终，我会将重新计算指令放在它自己的 Sub 中，并在工作表中制作一个按钮，以便用户可以 - 根据他/她的意愿 - 手动触发重新计算（以防用户不知道 F9 键）

4. 也可以考虑使用need_Query由 Worksheet_Change 触发器If Target.Row = X And Target.Column = Y（= 参数）设置的全局布尔变量，并让查询函数检查（和重置）该布尔值，然后只进入查询If need_Query Then，否则不执行任何操作。确保在工作表加载时也将 need_Query 设置为 FALSE。

5. 最后，您可以将查询函数转换为 aSub并在 Worksheet_Change 触发器中制定一些更复杂的条件来触发它（例如，如果某个列发生更改等）。