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

---

_说明：

@ 显示的称为隐式交集运算符。来自微软文档：

隐式交集逻辑将许多值减少为单个值。 Excel 这样做是为了强制公式返回单个值，因为
单元格只能包含单个值。如果您的公式返回
一个值，然后隐式交集什么也不做（即使它
从技术上讲是在后台完成的）。

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

• 这里是 MS 就该主题提供的信息丰富的文档，其中更详细地解释了 Formula 和 Formula2 之间的区别。

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

• 我之前回答了另一个问题，该问题涉及隐式交集，并举了一个关于其实际工作原理的示例这里如果有人觉得有趣的话。