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

Question

我正在尝试实现Microsoft所称的最佳实践，但无济于事。这是由于Excel中新支持的动态数组。以下是他们的文章和具体部分。HERE

最佳实践

如果针对Excel的DA版本，请优先使用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

*以上使用vbTrue作为示例，它可以是任何东西，同样适用于“变量”

- Ben

---

https://learn.microsoft.com/en-us/office/vba/api/excel.application.version - braX

@braX 最后一个预 DA 版本是什么？或者第一个 DA 版本是什么？如何测试是否支持 .formula2？ - Ben

https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2 - braX

Answer 1

您应该检测是否支持.Formula2，如果支持，则使用.Formula2，否则使用.Formula。这是查找Excel版本是否支持DA函数的方法。但它没有说到，您可以通过尝试分配该属性来在不支持DA函数的系统上捕获1004错误。

因此，我们可以将检查是否支持Formula2封装为ThisWorkbook模块的属性。

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 还是 Formula，但它们仍然可以检查 ThisWorkbook.SupportsDynamicArrays 以有条件地确定要传递的公式… 这只留下一个地方可以在更好的方法出现后进行调整！

- Mathieu Guindon

---

很好。应该从 Static BeenThere As Boolean = False 中删除 = False。您能分享一下为什么使用 Target As Object 而不是 Target As Range 吗？ - VBasic2008

因为早期绑定会在属性不存在时引发编译时错误。但是，奇怪的是，当我尝试使用Range.Formula5时，我的Office365 x64版本的Excel没有触发编译时错误，但我可以向您保证，在旧版本（如Excel for Mac x64版本16.16.27）中，如果我使用Range.Formula2，就会触发编译时错误，因为它不存在。显然，晚期绑定跳过任何编译时检查，因为它无法知道将在运行时分配什么类型的对象。 - Cristian Buse

@MathieuGuindon 谢谢！很有道理。我已经安装了Rubberduck。我只是没有注意到这个变化。我从来没有想过我会失去编译时检查。哦，无论如何，在VBA中这只是又一次伤口上的一滴盐水。至少，对于Excel/Office的VBA。 - Cristian Buse

@MathieuGuindon 这里是另一个尝试 VBA 的地方，这让我在过去的一个月里感到痛苦。 - Cristian Buse

Answer 2

我们可以使用隐式交集操作符 (@) 来检查是否支持动态数组：

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

- Cristian Buse

---

这也是一个非常优秀的方法！ - Mathieu Guindon

@MathieuGuindon和Cristian，我正在尝试结合这些方法，但好像出了问题。当我运行将使用新代码的Sub时，会出现编译错误：找不到方法或数据成员。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 - Ben

`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 IfEnd 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

这也可能是一个很好的地方，可以询问一下关于以下类型的查询是否仍然适用于新实现，还是需要进行一些重新设计？If (Cells(1,5).Value <> Empty or Cells(1,5).Formula <> Empty) Then我需要将其更改为以下内容吗：If (Cells(1,5).Value <> Empty or Cells(1,5).Formula <> Empty or Cells(1,5).Formula2) Then - Ben

@Ben 代码ThisWorkbook.HasDynamicArrays期望HasDynamicArrays在ThisWorkbook模块中。如果HasDynamicArrays在标准模块中，则用HasDynamicArrays替换ThisWorkbook.HasDynamicArrays。在您的SetFormula方法中，End Sub之前有一个不应该存在的Else和End If。不确定为什么会出现这些松散的代码。 - Cristian Buse

@Ben 关于上一个问题，我不确定你想要实现什么，但我猜测你需要检查是否支持动态数组，并且不能同时使用Formula和Formula2。 - Cristian Buse

Answer 3

这里有另一种方法：

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

- J. Woolley

Answer 4

另一个想法：随着动态数组的新支持，也出现了新的错误枚举。 检查枚举是否存在，如果是，则动态数组存在...

 IsDynamicArrayHere = CLng(CVErr(xlErrSpill)) = 2045

*编辑：但请查看下面的评论；由于错误枚举在功能之前已更新，因此不完全防水。

- EvR

---

我在我的Excel for Mac x64版本16.16.27（201012）中有xlErrSpill常量，它等于2045，但是没有动态数组功能。因此，我建议在你开始被踩之前删除你的答案。 - Cristian Buse

那么，有（旧的）版本下面有更新，但没有用新功能进行更新...？我以为所有的365办公室都已经更新到动态数组了。 - EvR

没错。我记得早在2017年DA被宣布之前就看到过xlErrSpill常量。 - Cristian Buse