Detect if Dynamic Arrays is enabled in Excel version with VBA

Question

I am attempting to implement what Microsoft is calling out as a best practice, but with no avail. This is due to the newly support Dynamic Arrays that is now supported within excel. This is their article and below is the specific section. HERE

Best Practice

If targeting DA version of Excel, you should use Range.Formula2 in preference to Range.Formula.

If targeting Pre and Post DA version of Excel, you should continue to use Range.Formula. If however you want tight control over the appearance of the formula the users formula bar, you should detect whether .Formula2 is supported and, if so, use .Formula2 otherwise use .Formula

Within VBA how is it possible to detect the version (Pre DA or Post DA)?

I have have created macros in excel that all work great in the older version of excel but once the new versions were introduced the formulas were changing because it was relying on what was the previous default "Implicitly Intersection Evaluation (IIE)". Due to the superseded method in the newer versions of excel all of the VBA implementations rely on the old method and the new excel adds the Implicit Intersection operator @ to the formulas. Because there is a risk that this will break the complicated sheets I want to be able to detect IF the current version of excel supports Dynamic Arrays, and if so I want to be able to have all of the implementations of range.formula replaced with range.formula2.

‘Detect Pre or Post DA version
Dim ExcelVersion As Variant
ExcelVersion = blabla bla test     ‘Some test function HERE, return vbTrue if Post DA Version Enabled

If ExcelVersion = vbTure Then
    Range.Formula2 = "=CustomFunction("& variable & ")"
Else
    Range.Formula = "=CustomFunction("& variable & ")"
End If

*vbTure is used above as an example it can be anything, same with "variable"

Answer 1

you should detect whether .Formula2 is supported and, if so, use .Formula2 otherwise use .Formula

That's how you find out whether the version of Excel supports DA functions or not. What it doesn't say, is that you can catch error 1004 on a system that doesn't support DA functions, by trying to assign to that property.

So we could conceivably encapsulate a check to see if Formula2 is supported, as a property of the ThisWorkbook module:

Private SupportsDA As Boolean

Public Property Get SupportsDynamicArrays() As Boolean
    Static BeenThere As Boolean
    If Not BeenThere Then ' only do this once

        Dim LateBoundCell As Object
        Set LateBoundCell = Application.ActiveCell
        If LateBoundCell Is Nothing Then 

            'if there is no active sheet/cell, we cannot tell
            SupportsDA = False ' err on the safer side
            BeenThere = False ' better luck next time, maybe

        Else

            BeenThere = True
            On Error Resume Next

            LateBoundCell.Formula2 = LateBoundCell.Formula2

            If Err.Number = 438 Then
                'Range.Formula2 is inexistent, return false.
                SupportsDA  = False
            ElseIf Err.Number = 1004 Then
                'DA not supported
                SupportsDA = False
            Else
                SupportsDA = True
            End If
        
            On Error GoTo 0

        End If

    End If
    SupportsDynamicArrays = SupportsDA
End Property

I think I would wrap the call with a Sub procedure that takes an Object parameter to late-bind a Range, along with the formula string - like this:

Public Sub SetFormula(ByVal Target As Object, ByVal Formula As String)
    If Not TypeOf Target Is Range Then Err.Raise 5 ' invalid argument
    If ThisWorkbook.SupportsDynamicArrays Then
        Target.Formula2 = Formula ' late-bound call will still compile in older hosts
    Else
        Target.Formula = Formula
    End If
Else

End If

That way the rest of the code can do SetFormula someCell, someFormula without needing to worry about whether that's going to be Formula2 or Formula, but they can still check if ThisWorkbook.SupportsDynamicArrays to conditionally determine what formula to pass... and that leaves exactly 1 place to tweak afterwards if a better way comes across!