使用 VBA 检测 Excel 版本中是否启用动态数组

我正在尝试实施 Microsoft 所说的最佳实践，但没有成功。这是由于 Excel 中现在支持新的动态数组。这是他们的文章，下面是具体部分。HERE https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2#best-practice

最佳实践

如果针对 DA 版本的 Excel，则应优先使用 Range.Formula2，而不是 Range.Formula。

如果针对 Excel 的 Pre 和 Post DA 版本，您应该继续使用 Range.Formula。但是，如果您希望严格控制用户公式栏的公式外观，则应检测是否支持 .Formula2，如果支持，则使用 .Formula2，否则使用 .Formula

在 VBA 中如何检测版本（Pre DA 或 Post DA）？

我已经在Excel中创建了宏，这些宏在旧版本的Excel中都可以很好地工作，但是一旦引入新版本，公式就会发生变化，因为它依赖于以前的默认值“隐式交集评估（IIE）”。由于新版本 excel 中的被取代方法，所有 VBA 实现都依赖于旧方法，并且新 excel 添加了隐式交集运算符@到公式。因为存在这会破坏复杂工作表的风险，所以我希望能够检测当前版本的 excel 是否支持动态数组，如果是这样，我希望能够拥有range.formula替换为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 作为示例，它可以是任何内容，与“变量”相同

您应该检测是否支持 .Formula2，如果支持，则使用 .Formula2，否则使用 .Formula

这就是您确定Excel版本是否支持DA函数的方法。它没有说的是，您可以通过尝试分配给该属性来在不支持 DA 函数的系统上捕获错误 1004。

所以我们可以想象封装一个检查来看看是否Formula2被支持，作为一个属性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

我想我会用一个结束通话Sub过程需要一个Object后期绑定参数Range，以及公式字符串 - 像这样：

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

这样其余的代码就可以完成SetFormula someCell, someFormula无需担心这是否会发生Formula2 or Formula，但他们仍然可以检查是否ThisWorkbook.SupportsDynamicArrays有条件地确定要通过什么公式......如果遇到更好的方法，那么就留下了一个可以调整的地方！