How does Excel calculate named formulas and materialized named ranges?

When working with named formulas in Excel—especially those built using AFE functions like OFFSET, INDEX, or dynamic arrays—it’s important to understand how Excel handles their evaluation. Named formulas are not “materialized objects”; they behave more like reusable expressions that are re-executed whenever referenced.

1. Named formulas are recalculated every time they are used.
Excel does not cache or materialize named formulas in a reliable or persistent way. Each reference to a named formula triggers a fresh evaluation unless you explicitly store its result in a variable inside a LET.
In your first version, storing ManualAllocRng inside a LET means it is calculated once and reused.
In your second version, calling ManualAllocRng twice means Excel evaluates the named formula twice.

2. Spill ranges behave differently depending on where they come from.
A spilled formula placed directly on a worksheet (e.g., =FILTER(...)) is calculated once; all references to A1# simply read the already-computed spilled range.
But a named formula that produces a spill does not materialize anywhere—each reference recalculates it.

3. The mismatch between sheet-based spilling and Evaluate() is a known engine inconsistency.
Application.Evaluate does not use the same array-calculation engine as the worksheet grid, which is why you may see different results for the same formula. This is a real (and known) Excel bug/limitation.