Backward Compatibility of Office 365 Dynamic Arrays (Updated 23 Sep 2020)

Office 365 Excel Dynamic Arrays are great, but:

What happens when you create a workbook with Dynamic Array  Excel (Excel DA) and send it to someone who does not have Excel DA, and how do you write VBA code that works in both Excel DA and Excel Non-DA?

My free CheckDA tool allows you to check that the formulas you create using Excel DA will not cause problems when opened in prior Excel versions.

The introduction of Dynamic Array formulas in Office 365 Excel has made a fundamental change to Excel formula behavior and syntax:

• In previous versions such as Excel 2013, Excel automatically used implicit intersection to select a single value from a range in many circumstances.
• In Office 365 Dynamic Array Excel 2016 (Excel DA), Excel treats all formulas as array formulas unless the implicit intersection operator @ is used, and single-cell dynamic array formulas will spill their arrays to surrounding cells.

Forward Compatibility

For workbooks authored in previous versions of Excel compatibility is great: implicit intersections will be silently converted to the new @ syntax, and VBA code generally works fine.

Backward Compatibility

When workbooks authored in Dynamic Array Excel (Excel DA) are opened in previous Excel versions, in most cases the formulas will be silently converted to the old syntax (@ will be removed where possible, and spilling dynamic arrays converted to array (CSE) formulas).

Formulas created by VBA using Range.Formula will work correctly in both DA and non-DA Excel

But it is possible to create formulas in Excel DA which cause difficulties when opened in previous versions.

CheckDA is a free open-source VBA addin that can scan Excel DA formulas, highlight potential backward compatibility problems and optionally convert some problem formulas to more compatible versions.

Backward Compatibility Problem Areas

VBA code creating formulas

For forward compatibility reasons formulas created using Range.Formula are treated as old-style non-DA formulas. To create a DA formula using VBA you should use the new Range.Formula2.

Therefore if you want your VBA code to run in both DA and non-DA Excel use Range.Formula.
For more details see https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

VBA Object Model Extensions for Dynamic Arrays

In addition to .Formula2 the Excel team has added a few useful extensions to the VBA object model:

• Range.HasSpill returns True for both the parent DA formula and a spilled cell
• Range.SpillParent returns the Range object of the parent DA cell
• Range.SpillingToRange returns a range object covering the entire spill range
• Range.SavedAsArray returns true if the range will be converted to a CSE array formula in Non-DA Excel

Functions that do not exist in previous versions

Excel DA introduces many new functions that work with Dynamic Arrays (SORT, UNIQUE, LET …). When you open a workbook containing these functions in non-DA Excel:

• The cells will still show the values calculated in DA Excel, until they are recalculated.
• In Non-DA Excel the functions will be prefaced by xlfn. and as soon as you recalculate will show #Name.
• When the workbook is re-opened in DA Excel the xlfn. prefix is removed and the cells can be recalculated.

This means that limited DA<->NonDA round-tripping is possible even when these functions do not exist in NonDA!

Unexpected @ in a formula producing =_xlfn.SINGLE()

Excel will only remove @ from a formula where previous Excel versions would have used Implicit Intersection to return a single value from a Range or Named Range or function parameter.

You will be warned by Excel DA if you try to enter a formula with @ in an unexpected place, but it is still possible to create such a formula. An unexpected @ will create a _xlfn.SINGLE() in previous versions and will result in #Name when calculated.

For example =@A1 will be converted to =_xlfn.SINGLE(A1) because Excel cannot do Implicit Intersection on a single cell.

CheckDA will flag such formulas as type @

Formula containing a Spill Reference producing _xlfn_ANCHORARRAY()

A formula reference to a dynamic array can use the spill reference suffix #. For example =SUM($B$3#) will SUM the entire dynamic array that starts in $B$3. Spill references get converted to _xlfn_ANCHORARRAY(Reference) in previous versions and will result in #Name when calculated.

CheckDA will flag such formulas as type {#}

Unwanted Array Formulas (CSE) in previous versions

Any formula that Excel DA thinks could return an array (even if it is actually only returning a single value) is converted to a CSE array formula in previous versions.

This may or may not produce a backward compatibility problem – you have to inspect these formulas carefully.

CheckDA flags these formulas as types:

• {1} a single-cell non-spilling formula
• # An Excel DA Dynamic Array formula that is spilling or blocked (#Spill!)  will be converted to a fixed CSE formula in previous Non-DA Excel versions such as Excel 2013.
• {n} a formula entered in Excel DA as a multi-cell CSE formula

Using CheckDA

CheckDA is an open-source VBA XLAM addin.
CheckDA only runs in Windows Office 365 Dynamic Array Excel.

To protect against accidental change CheckDA is protected with the password dm.

You can download the CheckDA addin from here. The zip file contains the CheckDA.xlam and the CheckDa.docx Word document.

Install the CheckDA.XLAM file using Excel’s Addin Manager if you want it permanently installed, or just open the xlam workbook using Excel File Open if you only want to use it in the current Excel session. Once successfully installed you will see CheckDA available on the ribbon.

Control-Shift-J or clicking the Check DA Formulas button shows the Check DA form.

The form:

• Shows formulas in Non-DA Syntax
• Is Modeless (you can edit formulas while still showing the form)
• Is Resizeable
• At start-up scans the active worksheet for unique formulas
• At start-up filters out formulas with unexpected @ or Spill Ref or Spill or single-cell array formulas

Restate Selected Formulas

Clicking the Restate Selected Formulas button will change any @ or {1} formulas selected in the form to more compatible syntax:

• @ formulas will have their unexpected @s removed.
• {1} formulas will be re-entered as non-array formulas.
• # Spill formulas and {#} Spill Ref formulas cannot be automatically restated.

Undo Restate

Clicking the Undo Restate button will undo the restatement of any formulas restated in this session of CheckDA.

Show Office 365 DA Syntax

This button toggles between showing the formulas in Non-DA syntax and Office 365 Dynamic Array syntax.

Conclusion

The Microsoft Excel team have done an outstanding job of creating, as far as is possible, compatibility between Dynamic Array Excel and previous versions. With a little care you can easily create workbooks using Excel DA that work correctly in Non-DA Excel.

Further Reading

• the spilled range operator and how to use it
• the various dynamic array behaviors
• how dynamic arrays compare to legacy array formulas
• new #SPILL error and scenarios that can trigger it
• implicit intersection and dynamic arrays
• dynamic arrays in prior versions of Excel
• Range.Formula2 https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula2
• Range.Formula vs Range.Formula2: https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

Advertisement