Excel Dynamic Arrays

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
199
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
(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:
 
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.
 
But I love array macros, so much I can do with them now every macro can spill its results!
 
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.
 
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:

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top