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"

https://learn.microsoft.com/en-us/office/vba/api/excel.application.version — braX, Jan 25 '22 at 02:48
@braX What is the last pre DA version? or what is the first DA version? How to test if `.formula2` is supported? — Ben, Jan 25 '22 at 02:55
https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2 — braX, Jan 25 '22 at 03:09

Mathieu Guindon · Answer 1 · 2022-01-25T15:56:34.570

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!

Great. `= False` should be removed from `Static BeenThere As Boolean = False`. Could you share why `Target As Object` and not `Target As Range`? — VBasic2008, Jan 25 '22 at 07:06
@VBasic2008 Because early-binding causes a compile-time error if the property does not exist. But, weirdly my Office365 x64 version of Excel does not trigger a compile time error when I try something like ```Range.Formula5``` but I can assure you that on older versions (like Excel for Mac x64 version 16.16.27) a compile-time error is triggered if I use ```Range.Formula2``` because it does not exist. Late-binding obviously skips any compile-time checks as it cannot know what type of object will get assigned at runtime. — Cristian Buse, Jan 25 '22 at 12:33
@MathieuGuindon Thanks! It makes sense. I already have Rubberduck installed. I simply did not notice the change. It never occurred to me that I will ever lose the compile-time checks. Oh well, what's another drop of salt in the big wound that is VBA. At least, VBA for Excel/Office. — Cristian Buse, Jan 25 '22 at 16:10
@MathieuGuindon [Here](https://stackoverflow.com/questions/70598025/slow-property-get-and-function-procedures/70861990#70861990) is another stab at VBA which caused me pain for the last month. — Cristian Buse, Jan 26 '22 at 11:38

score 5 · Answer 2 · answered Jan 25 '22 at 13:28

5

We could use the implicit intersection operator (@) to check for dynamic array support:

Option Explicit

Public Function HasDynamicArrays() As Boolean
    Static isDynamic As Boolean
    Static ranCheck As Boolean
    
    If Not ranCheck Then
        isDynamic = Not IsError(Evaluate("=COUNT(@{1,2,3})"))
        ranCheck = True
    End If
    HasDynamicArrays = isDynamic
End Function

answered Jan 25 '22 at 13:28

Cristian Buse

4,020
1
13
34

2

That's an excellent approach too! – Mathieu Guindon Jan 25 '22 at 17:47
@MathieuGuindon & Cristian, I am trying to combine the approaches and I seem to be having an issue. When I run the Sub that would utilize the new code I get the Compile error: Method or Data member not found. `Private isDynamic As Boolean Public Property Get HasDynamicArrays() As Boolean Static isDynamic As Boolean Static ranCheck As Boolean If Not ranCheck Then isDynamic = Not IsError(Evaluate("=count(@{1,2,3})")) ranCheck = True End If HasDynamicArrays = isDynamic End Property` See below remainder – Ben Feb 09 '22 at 23:56
`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.HasDynamicArrays Then MsgBox ("Formula2") Target.Formula2 = Formula 'late-bound call.. Else MsgBox ("use formula") Target.Formula = Formula End If Else End If End Sub Sub test2() Dim Equation As Variant Dim strng As Variant strng = "A5:H5" Equation = "=countif(" & strng & ",""*Green*"")" Call SetFormula(Cells(1, 2 + 1), Equation) End Sub` – Ben Feb 10 '22 at 00:00
It is also likely a good place to add my question about if the following types of queries will still work with the new implementation or will it need to be reworked a bit? `If (Cells(1,5).Value <> Empty or Cells(1,5).Formula <> Empty) Then` Would I need to change it to the following: `If (Cells(1,5).Value <> Empty or Cells(1,5).Formula <> Empty or Cells(1,5).Formula2) Then` – Ben Feb 10 '22 at 00:15
1

@Ben The code ```ThisWorkbook.HasDynamicArrays``` expects the ```HasDynamicArrays``` to be in the ```ThisWorkbook``` module. If the ```HasDynamicArrays``` is in a standard module then replace ```ThisWorkbook.HasDynamicArrays``` with just ```HasDynamicArrays```. In your ```SetFormula``` method there is an ```Else``` and an ```End If``` that should not be there right before the ```End Sub```. Not sure why you have those loose. – Cristian Buse Feb 10 '22 at 09:26
@Ben Regarding the last question, I am not sure what you are trying to achieve but I presume you need to check if dynamic arrays are supported and use either Formula or Formula2 not both at the same time. – Cristian Buse Feb 10 '22 at 09:28

EvR · Answer 3 · 2022-01-25T15:00:32.400

1

Another thought: With the newly support of Dynamic Arrays, there are also new Error Enums. Check if the Enum exist, if so Dynamic Arrays exist...

 IsDynamicArrayHere = CLng(CVErr(xlErrSpill)) = 2045

*edit: But see comments below; Not completely waterproof as Error enums were updated before functionality was.

edited Jan 25 '22 at 15:00

answered Jan 25 '22 at 09:21

EvR

3,418
2
13
23

I have the ```xlErrSpill``` constant on my Excel for Mac x64 version 16.16.27 (201012) and it equals 2045 but there is no dynamic array functionality. So, I suggest removing your answer before you start getting downvotes. – Cristian Buse Jan 25 '22 at 12:29
So there are (old) versions which have the updates underneath but have not been updated with the new functionality...? I thought that all 365Offices have been updated to dynamic arrays – EvR Jan 25 '22 at 14:39
Correct. I actually rememeber seeing the ```xlErrSpill``` constant back in 2017 way before DA were announced. – Cristian Buse Jan 25 '22 at 14:55

score 1 · Answer 4 · answered Aug 05 '22 at 19:33

1

Here is another approach:

On Error Resume Next
    ' test support for dynamic arrays
    V = WorksheetFunction.Unique([{1;2}])
    If Err <> 0 Then ... ' requires Excel 365/2021+
On Error GoTo 0

answered Aug 05 '22 at 19:33

J. Woolley

88
7

Detect if Dynamic Arrays is enabled in Excel version with VBA

4 Answers4

Linked