Working with dynamic arrays in Excel

Many of the VBA functions available here return results as arrays, which must be entered by selecting the desired output range, then  pressing Ctrl-Shift-Enter (CSE).  This can be inconvenient (or at least untidy) when the size of the output array varies.  Some solutions to this, using VBA and/or Python were presented  here.  Microsoft have also been working on this, and for members of the Windows Insider  Program, dynamic arrays have been available for some time now.  Dynamic array formulas are entered as standard single cell formulas, but automatically resize to display the entire array (or “SPILL” if the output range contains any existing data).

The new dynamic arrays (currently only available in the Excel Insider version) have potential problems when spreadsheets created in an Insider version are opened in earlier versions.  Charles Williams at Fast Excel has been investigating these problems, and has recently published a detailed review of potential problems, including a free CheckDA tool that allows you to check that the workbook you create using Excel DA will not cause problems when opened in prior Excel versions.


This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.