Excel Dynamic Arrays

Dermot

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

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,870
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

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

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

Board Regular
Joined
Aug 11, 2006
Messages
73
Office Version
365
Platform
Windows
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,102,141
Messages
5,484,982
Members
407,478
Latest member
wsupaul

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top