从VBA -忽略易失性函数计算应用程序

Question

问题

是否可以有效地模拟VBA的application.calculate结果，但忽略易失性函数？

详细信息

定义:非易失性等效：对于任何易失性工作簿，将非易失性等效定义为工作簿，其中所有具有易失性引用的单元格都已被其原始值替换。

定义:非易失性计算差异：假设我们有任何工作簿。现在，假设我们取了它的非易失性等价物，并对其进行了全面的重新计算。取其值更改为非易失性等效值的单元格及其值。这是非易失性计算的差异。

问题是要问这个。对于任何带有潜在易失性引用的工作簿，是否有一种有效地应用非易失性差异的方法？在这里，效率是关键--这就是我们忽略易失性函数的全部原因--参见下面的用例。因此，任何解决方案，如果没有超过一个完整的重新计算，是无用的。

用例

我们有一本工作簿，里面充斥着INDIRECT的使用。INDIRECT是一个Excel本机易失性函数.在工作簿中大约有30万，其中包括大约150万已使用的单元格。我们没有能力改变这些情况。由于这些INDIRECT用法，我们的工作簿需要很长时间才能重新计算。大约10秒。因此，我们有自动计算关闭&用户定期点击手动重新计算，以刷新数据，因为他们走。

我们的业务用户对此没有意见，但是我们添加的一些新的VBA函数可能比10秒的等待更有效。

到目前为止我们尝试过的

• Sheet.Calculate --在某些情况下，这很方便。我们确实利用了它。但它将所有其他工作表中的数据视为值，而不是公式。因此，如果有任何zag引用，这并不能提供一个完全一致的结果。例如，假设一个来自于表格A的引用，->表B，->页A:然后需要计算表A，然后B，然后A，锯齿数是任意的。只有一个案子，有两张床单。要解决这个问题，需要从根本上重写Excel的整个计算过程。

Answer 1

有一件事出现在我的脑海里，那是不理想的。也许您可以使您自己的函数不易失性，并存储在电子表格中，这将执行间接的操作(或您使用的任何公式)。您可以将其称为INDIRECT2或其他什么的，只需在电子表格中替换它们。

不幸的是，这个函数不能用于Application.WorksheetFunction.functionName，但是有一些方法可以绕过它。这只是我的一般想法。

Answer 2

使用与INDIRECT相同的非易失性用户定义函数。

Public Function INDIRECT_NV(ByVal ref_text As String, Optional ByVal a1 As Boolean) As Variant
    'Non-volatile INDIRECT function
    'Does not accept R1C1 notation - the optional is purely for quick replacement
    INDIRECT_NV = CVErr(xlErrRef) 'Defaults to an  error message
    On Error Resume Next 'If the next line fails, just output the error
    Set INDIRECT_NV = Range(ref_text) 'Does the work of INDIRECT, but not for R1C1
End Function

或者，根据用例的不同，可以使用INDEX和MATCH (非易失性函数)代替INDIRECT查询。