vba - 以编程方式控制/拦截数据表刷新

Question

背景

我有一个非常大的data table在高端 64 位机器上运行大约 100 万个输入场景需要长达 12 小时。 这些场景基于许多离散的 Excel 模型，然后将其输入到财务模型中进行详细计算

为了改进该过程，我希望测试并比较以下速度:

1. 当前的手动流程
2. 使用 VBA 刷新数据表(关闭计算、ScreenUpdating等)
3. 运行 VBS 以刷新不可见 Excel 实例中的数据表

因此，我正在寻找以编程方式管理数据表的最佳方法

更新:使用 (2) 和 (3) 中的代码并没有为使用具有单个大型数据表的工作簿测试简单示例带来好处

令人惊讶的是，VBA 对数据表的直接支持似乎很少(可能没有)

我目前的知识和文献检索

• QueryTable BeforeRefresh 和 AfterRefresh 事件可以通过此 class module code 添加。 Intellisense 不提供此选项作为数据表
• 可以像 ActiveWorkbookk.Sheets(1).QueryTables(1) 那样访问各个数据透视表和 QuertyTable。数据表则不然
• this 中建议消除所有其他数据表，然后运行RefreshAll MrExcel 线程作为解决方法。

解决方法当然是可行的，因为我只有一个数据表，但我更喜欢一种直接方法(如果存在)。

是的，我坚持使用 Excel :)

请不要建议使用此方法的其他工具，输入模型和使用数据表的总体模型都是

• 作为完善的持续流程的一部分，该流程将继续基于 Excel，
• 经过专业审核，
• 由一些经验丰富的 Excel 设计师进行了简化和优化

我只是好奇是否有一种方法可以通过使用代码刷新特定数据表来调整流程，但我上面的初步测试结果得出的结论是没有。

Answer 1

因此，您正在寻找以编程方式管理数据表的最佳方法。

嗯，当我手动创建数据表时，Excel 2013 确实为我录制了一个宏，它就这样

Selection.Table ColumnInput:=Range("G4")

签名是

Range.Table(RowInput as Range, ColumnInput as Range) as Boolean

记录在Range.Table Method中。 Range.Table() 函数似乎总是返回 true。

这是使用 VBA 创建数据表的唯一方法。但这就是数据表的全部内容。

据我所知，没有数据表的类或对象，因此没有 dt.refresh() 或类似的方法。并且没有可供查询的数据表集合。您必须刷新工作表或使用 Range.Table() 重新创建表格。

有一个DataTable Interface ，但与图表有关，与Range.Table()无关。

正如您提到的，您应该关闭通常的嫌疑人，即

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

工作簿中尽量少用公式。删除与数据表所基于的单元格不相关的所有公式。删除任何中间结果。最好在一个单元格中包含一个(可能很大)公式。

示例:G4 是您的 ColumnInput，它包含 =2*G3，其中 G3 包含 =G1+G2，那么最好将 =2*(G1+G2) 代入 G4。

您的高端机器可能有 6 个核心。将您的场景分为 6 个部分有 6 个 Excel 实例并行计算它们。