vba - Excel VBA 函数仅在更改时更新

Question

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

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

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

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

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

其他要求:

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

Answer 1

我愿意

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

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

3. 最终，我会将重新计算指令放入其自己的 Sub 中，并在工作表中创建一个按钮，以便用户可以根据自己的意愿手动触发重新计算(以防万一 F9 键不可用)用户已知)

4. 也可以考虑使用由 Worksheet_Change 触发器设置的全局 bool 变量 need_Query If Target.Row = X And​​ Target.Column = Y (= 参数)并让查询函数检查(并重置)该 bool 值，并且仅If need_Query then进入查询，否则退出而不执行任何操作。确保在工作表加载时 need_Query 也设置为 FALSE。

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