Excel VBA - 如何添加动态数组公式

Question

我正在通过 VBA 向工作表添加一个公式，它应该是:

=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""))

这利用 Excel 中的新 SPILL 功能为我提供 B 列值的列表，其中 A 列中的相关值与单元格 A 中的值匹配。我还应用 UNIQUE 函数来删除任何多个空白 ("") 结果。

如果我在 Excel 中手动键入公式，这将非常有效，但是在使用 VBA 添加公式时，Excel 会在公式中添加 @ 符号，并使其显示 #VALUE!。

用于添加公式的 VBA 行是:

=Cells(x,y).Formula = "=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""""))"

Excel 中的结果输出为:

=@UNIQUE(IF(TableA[@[ColumnA]]=A1,TableA[ColumnB],""))

发生了什么，我错过了什么？

提前致谢!

Answer 1

好问题，我查了一下...

简而言之:

使用=Cells(x,y).Formula2而不是 =Cells(x,y).Formula
说明:
@显示的称为隐式交集运算符。来自 MS 文档:

Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background).

但为什么它会出现在您较新的 Excel O365 中？好吧， Range.Formula使用 IIE(隐式交集)，因此添加了 @基本上撤消您的动态数组功能。 UNIQUE 是一个新的动态数组函数。所以，要用代码写出来，你应该使用 Range.Formula2 属性(或 Range.Formula2R1C1 如果您使用 R1C1 表示法)。这些属性使用 AE(数组评估)，现在是默认值。

Here是来自 MS 的关于该主题的信息文档，它解释了 Formula 之间的区别和 Formula2更详细。

如果您想了解有关隐式交集运算符的更多信息，请查看 this

我之前回答了另一个关于隐式交集的问题，并举例说明了它是如何工作的 here如果有人觉得有趣。