How to determine the number of cells needed for a dynamic array formula

I want to determine how many rows are needed to hold the result of a dynamic array formula. Here's what I tried:

• Counting the rows of the range returned by CurrentArray: Generates Error 1004 No cells were found.
• Using Evaluate on the formula and checking LBound and UBound: Generates Error 438 Object doesn't support this property or method.

Example:

Cell A1 has formula "=SEQUENCE(10)"

Using this code;

Sub check4array()
    Debug.Print Range("A1").HasArray
    Debug.Print Range("A1").CurrentArray.Rows.Count
    Debug.Print UBound(Evaluate(Range("A1").arrayformula))
End Sub

I get this output:

False

Error 1004

There is something about arrays I'm not getting. Please help me understand!

Using .HasSpill and .SpillingToRange:

Sub check4array()
    If Range("A1").HasSpill Then
         Debug.Print Range("A1").SpillingToRange.Rows.Count
    End If
End Sub

Dynamic array formulas are different from the older array formulas.

Alternately, you can use the spilled-range operator:

Debug.Print Range("A1#").Rows.Count

If A1 contains a #SPILL! error (i.e. something is blocking it from spilling), then Evaluate its .Formula2 and then use UBound:

With ActiveSheet
    Debug.Print UBound(.Evaluate(.Range("A1").Formula2), 1)
End With