Excel Dynamic Arrays

[Dermot]

I thought that was a great book, at a fantastically low price, thank you

i was especially delighted to find that any VBA macro can spill its results. For example, I was able to run an SQL query from a worksheet formula and spill the record set on the sheet, and write a Split function to break up a test string into separate cells. It’s magic (copied straight from Google Sheets, it seems, but magic all the same).

Anyway, thank you again for your book and the hard work that went into it.

 

[MrExcel]

Thanks!

 

[MARK858]

(copied straight from Google Sheets, it seems, but magic all the same)

Makes a change as most of Google sheets was copied from/based on Excel syntax

 

[Dermot]

Yes, but Excel was copied directly from Lotus 1-2-3. That’s why TRUE = +1 in Excel and -1 in VBA, among other things.

you have no choice if you are taking on the market leader (as Excel was doing back then, and Google more recently) - you have to make it easy to switch by keeping the user interface and functions similar. It’s the same in video games.

But these “spill” functions were a long time overdue. As is decent automation, which is still severely lacking. You can use the ancient data tables to run multiple scenarios, but you can’t loop sheets within sheets. I had to write code to do it, and it proved exceptionally powerful, but there’s no reason why it couldn’t be built into Excel.

 

[Dermot]

But I love array macros, so much I can do with them now every macro can spill its results!

 

[lomist1]

Bill - I'm enjoying Dynamic Arrays, 2nd edition.

On page 28, chapter 7 ends with this sentence: "Virtually every Excel formula is now an array formula, with no need for Ctrl+Shift+Enter." Is this 1) because more or less any standard, non-array function or formula can include a dynamic array function, as you showed with the ROMAN example or 2) because the Excel team has added other array functionality to the legacy functions, in addition to adding dynamic arrays as new functionality?

Thank you.

 

[Dermot]

Any function that returns a table will spill, even = (A5:C8) will do so, or =OFFSET(D1,1,1,3,4)

I created a workbook with lots of examples of the new functions, and I wrote some VBA functions that spilled too, just for fun, see here

Spill Formulae.xlsb

1drv.ms

On page 28, chapter 7 ends with this sentence: "Virtually every Excel formula is now an array formula, with no need for Ctrl+Shift+Enter." Is this 1) because more or less any standard, non-array function or formula can include a dynamic array function, as you showed with the ROMAN example or 2) because the Excel team has added other array functionality to the legacy functions, in addition to adding dynamic arrays as new functionality?