Excel Dynamic Arrays

Dermot

New Member
Joined
Aug 11, 2006
Messages
25
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,427
Office Version
365, 2010
Platform
Windows, Mobile
(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 :ROFLMAO:
 

Dermot

New Member
Joined
Aug 11, 2006
Messages
25
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

New Member
Joined
Aug 11, 2006
Messages
25
But I love array macros, so much I can do with them now every macro can spill its results!
 

lomist1

New Member
Joined
Apr 18, 2020
Messages
1
Office Version
365
Platform
Windows, Mobile
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

New Member
Joined
Aug 11, 2006
Messages
25
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

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?
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,096,187
Messages
5,448,861
Members
405,535
Latest member
KLFT

This Week's Hot Topics

Top