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

Question

背景

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

为了改进这一过程，我希望测试和比较以下几个方面的速度：

1. 当前的手工处理
2. 使用VBA刷新数据表(使用Calculation、ScreenUpdating等关闭)
3. 运行VBS以刷新不可见的Excel实例中的数据表

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

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

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

我现在的知识和文献检索

• QueryTable BeforeRefresh和AfterRefresh事件可以与此类模块代码一起添加。Intellisense没有为数据表提供此选项
• 单独的PivotTables和QuertyTables可以像ActiveWorkbookk.Sheets(1).QueryTables(1)那样被访问。数据表不是这样的
• 在Data Tables MrExcel线程中建议删除所有其他的MrExcel，然后运行RefreshAll作为解决办法。

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

是的，我坚持用 :)

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

• 这是一个完善的持续过程的一部分，它将保持基于Excel的基础，
• 接受了专业审计，
• 已经被一些经验的Excel设计人员简化和优化了

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

Answer 1

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

当我手动创建数据表时，Excel 2013确实会为我记录一个宏，它说

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

签名是

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

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

这是使用VBA创建数据表的唯一方法。但不管怎么说，数据表就是这样的。

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

有一个DataTable接口，但它与图表相关，与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实例并行计算它们。