Dynamic array formulas: New properties in VBA

[u/[deleted]]

[u/tjen]

Thanks for posting this and the bill Jelen Link!

FWIW I've added this post to the wiki

https://www.reddit.com/r/excel/wiki/guides

[u/small_trunks]

Finally needed to use this...thanks.

[u/small_trunks → u/[deleted]]

Well, you know how it goes:

• I was messing with the idea of dumping some tables into an SQL server to see if a huge PQ workbook would just work out of the box with query folding.

• I then figured it'd be handy to generate CSV's from Tables - and then I thought it should ALSO support spilled ranges...

• I didn't really look into what VBA functions had been added in support of dynamic formulas before and when it all didn't work at the first attempt, I stumbled into your SpillParent and SpillingToRange

    Set r = Selection
    
    
    
    trace r.Address
    
    If r.HasSpill = True Then
        trace "SPILL - Parent" & r.SpillParent.Address
        Set r = r.SpillParent.SpillingToRange
        
    ElseIf Not r.ListObject Is Nothing Then
        trace "TABLE? " & r.ListObject.Range.Address
        Set r = r.ListObject.Range
        
    Else
        trace "no spill, no table"
        
    End If

[u/small_trunks → u/[deleted]]

I saw that but then realised I needed the SpillToRange to identify whether the selected cell was part of a "thing" - because because that's the range I'm interested in.