excel - 动态命名范围/公式多久重新计算一次？

Question

我有一个公式分配给 excel 中的名称，计算速度很慢。
假设是这样的:

Name: ImportantItems
Scope: Workbook
RefersTo: =FILTER(A1:A10000, complexCondition(A1:A10000))

我有一些 VBA 宏运行模拟，每次迭代都会修改 A1:A10000，但是模拟只需要访问 ImportantItems每 100 次迭代数组。如果 =FILTER(A1:A10000, complexCondition(A1:A10000))...是单元格中的普通公式，我知道 Excel 会观察其先例 (A1:A10000) 已更改并在每次迭代时触发重新计算。但是，我希望电子表格中未提及的命名范围(仅通过 VBA)将按需计算。 FWIW 我的 VBA 代码只是

Dim items As Variant 'read fancy filtered array of stuff into 1D array
items = Application.Transpose(Sheet1.Range("ImportantItems").Value)

我的替代方法是重构 ImportItems 代码并将其移动到 VBA 中，以便我可以控制它的计算时间。 Application.Calculations = xlManual如果不将我的代码洒在......

无论如何，这并不是一个 A/B 问题，我只是想知道计算引擎是如何工作的，因为我找不到关于它的文档，它会影响 future 的设计决策。

Answer 1

你的希望/假设是正确的。只要命名范围在工作簿中的任何范围内都没有被引用(其他命名范围不计算在内)，那么在更改源范围时不会重新计算命名范围中的公式。
它不重新计算的原因是因为命名范围存储了一个公式字符串(参见 VBA 中的 Name 对象 .RefersTo)。仅当从某个范围引用命名范围时，Excel 才会传播更改(在源中)，在该范围内它在公式字符串上运行 Evaluate。当源被调整大小(剪切/插入/删除单元格)时，命名范围公式字符串会更新，但本质上它仍然是一个字符串。
要进行测试，请创建以下命名范围:
姓名:TestCalc范围:Sheet1引用:=NamedRangeWatch(Sheet1!$A$1:$A$5)然后在标准 .bas VBA 模块中添加以下代码:

Option Explicit

Public Function NamedRangeWatch(ByVal rng As Range) As Range
    Application.Volatile False
    
    Debug.Print "NamedRangeWatch was called at " & Now
    
    Set NamedRangeWatch = rng
End Function

Sub Test()
    Dim i As Long
    
    For i = 1 To 10
        Sheet1.Range("A1:A5").Value2 = i
        If i Mod 5 = 0 Then
            Debug.Print Application.Sum(Sheet1.Names("TestCalc").RefersToRange)
            'Or
            'Debug.Print Application.Sum(Sheet1.Range("TestCalc").Value)
        End If
    Next i
End Sub

然后运行 ​​ Test方法。您应该会看到如下内容:

这表明尽管源范围更改了 10 次，但命名范围内的函数仅被调用两次。