Reference a Named Dynamic Array Formula in Excel and VBA
Question details
Reference an Excel named formula that returns a dynamic array from a worksheet formula or VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A name defined in Name Manager refers to a formula that produces multiple values rather than to a traditional cell range.
- Observed behavior
- The named formula can be evaluated in a worksheet, where its results spill into cells, or evaluated from VBA for processing.
Use an Excel version that supports dynamic arrays, LET, and SEQUENCE, such as Excel for Microsoft 365 or Excel 2021. Check the name and its scope under Formulas > Name Manager, and make sure the intended spill area is empty.
Use the named formula in a worksheet
Enter the defined name as a formula to display its dynamic array, then reference the spill from its anchor cell.
Open Formulas > Name Manager > New. Enter myNewList as the name and =LET(n,10,i,SEQUENCE(n),i) in Refers to, then select OK.
Select cell A1 on a worksheet where the name is in scope, enter =myNewList, and press Enter. The values 1 through 10 should spill down the column.
In another formula, use =A1# to refer to the full spill anchored at A1. Use the anchor cell with # rather than assuming the named formula itself is a range reference.

Evaluate the named formula in VBA
Use Evaluate when the defined name refers to a formula rather than a conventional range.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel files and continue working with minimal disruption, while checking advanced formulas and macros for compatibility.

Frequently Asked Questions
Can I enter a named formula directly in a worksheet?
Yes. If myNewList is defined in Formulas > Name Manager and is in scope, enter =myNewList in a cell. A dynamic array result can spill into neighboring cells.
How do I refer to all the cells in the spill?
Use the spill anchor followed by #. For example, if A1 contains =myNewList, enter =A1# in another formula to reference its spilled result.
Why does the formula show #SPILL!?
The intended output area may be blocked or otherwise unavailable. Select the error indicator in Excel, inspect the reported cause, and clear any obstructing cells.
Why doesn't VBA Range("myNewList") return the array?
The name may refer to a formula rather than a range. Use the appropriate worksheet's Evaluate("myNewList") and check whether the result is an array or an error.
Does the name's scope matter?
Yes. Check the Scope column in Formulas > Name Manager. A worksheet-scoped name must be used or evaluated in the appropriate worksheet context.




