How often are dynamic named ranges/formulae recalculated?

Question

I have a formula assigned to a name in excel which is slow to calculate.

Let's say it's this:

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

I have some VBA macros which run a simulation that modifies A1:A10000 on every iteration, however the simulation only needs to access the ImportantItems array every 100 iterations. If

=FILTER(A1:A10000, complexCondition(A1:A10000))

... was a normal formula in a cell, I know that Excel would observe its precedent (A1:A10000) had changed and trigger a recalc every iteration. However I'm hoping named ranges not referred to in the spreadsheet anywhere - only via VBA - will be calculated on demand. FWIW my VBA code is just

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

My alternative is to refactor and move the ImportantItems code into VBA so I can control when it is calculated. Application.Calculations = xlManual is not an option without sprinkling my code with ...

Anyway this isn't meant to be an A/B question, I'm just wondering how the calculation engine works as I can't find documentation on it, and it will influence future design decisions.

user_8488913 · Answer

Your hope/assumption is correct. As long as the named range is not referred to in the workbook in any range (other named ranges don't count) then the formula in the named range is not recalculated when the source range is changed.

The reason it doesn't recalculate is because the named range is storing a formula string (see Name object .RefersTo in VBA). Excel propagates the change (in source) only if the named range is referenced from a range at which point it runs an Evaluate on the formula string. When the source is resized (cut/insert/delete cells), the named range formula string is updated but in essence it's still a string.

To test, create the following named range:
Name: TestCalc
Scope: Sheet1
Refers to: =NamedRangeWatch(Sheet1!$A$1:$A$5)

Then add the following code in a standard .bas VBA module:

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

and then run the Test method. You should see something like:
enter image description here

which shows that the function inside the named range only gets called twice although the source range was changed 10 times.

How often are dynamic named ranges/formulae recalculated?

1 Answers