CheckDA: Compatibility of Office 365 Dynamic Array Formulas

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?

My free CheckDA tool allows you to check that the workbook 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 (as of May 2019 only available to Office Insiders) 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 good: implicit intersections will be silently converted to the new @ syntax.

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).

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

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 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.

CheckDARibbon

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

CheckDAForm

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

Please let me know of any bugs you find, and send me your improvement ideas and experiences with CheckDA.

Advertisements
This entry was posted in arrays, Dynamic Arrays, Excel, Uncategorized, VBA and tagged . Bookmark the permalink.

7 Responses to CheckDA: Compatibility of Office 365 Dynamic Array Formulas

  1. dougaj4 says:

    Thanks Charles, this looks like a great tool.

    Just to be picky:
    “(as of May 2016 only available to Office Insiders)”

    … and still only available to Office Insiders 3 years later!

  2. Jason says:

    Wonder if you have had this error or warning.
    I have the insider version with DA on my computer and send files with others who do not.
    I’ve stayed away from using new dynamic arrays on files shared with them, but they get an error on some non-DA formulas. When I go to edit the formula it says: “This style of formula is not supported by some older versions of Excel. Would you like to use this variation instead?” It shows the proposed formula, but it is the exact same as what it is replacing. If you accept it, the formula is the same, however others are able to view the results without the #Name error. It is a formula in a table so the “@” is included. I suppose something is changing, just not visually. The proposed still has the “@” included. I just had to go through and edit and accept each formula.

  3. fastexcel says:

    That message is supposed to be shown when you enter a formula that contains extra @ s. Don’t know why you could not see the formula difference (maybe you hit a bug that got resolved?) But that kind of problem is exactly what CheckDA should help with: those formulas should show flagged with an @ and you can select them in the CheckDA form and click “Restate” to resolve the problem

    • Jason says:

      I thought I was missing it too until I took a screen shot of the before and after and also did a FORMULATEXT and checked exact. Thanks for putting CheckDA together. I want to start using dynamic arrays more, but need to have it friendly for everyone.

      • fastexcel says:

        Also there is a suggested improvement for CheckDA: have a button that converts Dynamic Arrays and Spill Refs to static arrays and refs. Do you think that would be useful?

      • Jason says:

        Possibly. I haven’t played around enough with it yet to know what Excel already converts or converts correctly. I have seen that “_xlfnI” before though. I spent some time converting a lot of stuff on one of our models over to DA, but realized it would be difficult to maintain a DA version and a non-DA version with the almost daily changes I have to make to it. I will probably wait until DA goes wide before incorporating. I have enough problems with compatibility, backwards or otherwise. I have fearful recollections of the time I had to track down an error where I had used a single TEXTJOIN formula deep in the data before those were widely available.

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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