Excel VBA-如何添加动态数组公式 [英] Excel VBA - How to add dynamic array formula

问题描述

我正在通过VBA向工作表中添加一个公式，该公式应为：

I am adding a formula to a worksheet via VBA which should be:

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

这利用了Excel中的新SPILL功能给我一个B列值的列表，其中A列中的相关值与单元格A中的值匹配。我还应用UNIQUE函数来删除任何多个空白（）结果。

This utilises the new SPILL feature in Excel to give me a list of column B values where the related value in column A matches what is in cell A. I'm also applying the UNIQUE function to remove any multiple blank ("") results.

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

This works perfectly if I manually type the formula into Excel, however in using VBA to add the formula, Excel is adding @ symbols within the formula, and causing it to show #VALUE!.

用于添加公式的VBA行是：

The VBA line being used to add the formula is:

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

在Excel中的结果输出为：

The resulting output in Excel is:

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

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

What is going on, and what have I missed?

谢谢！

推荐答案

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

Good question, and I looked it up...

_{简而言之：}

使用 = Cells（x，y）.Formula2 代替 =单元格（x，y）。公式

_说明：

显示的 @ 被称为隐式相交运算符。从MS文档开始：

The @ that shows is called the implicit intersection operator. From MS docs:

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

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

But why does it appear in your newer Excel O365? Well, Range.Formula uses IIE (implicit intersection) thus adding the @ to basically undo your dynamic array functionality. UNIQUE is a new dynamic array function. So, to write this out in code, you should use the Range.Formula2 property (or Range.Formula2R1C1 if you use R1C1 notation). These properties use AE (array evaluation) and is now the default.

• 此处是MS提供的有关该主题的资料丰富的文档其中更详细地说明了 Formula 和 Formula2 之间的区别。

• Here is an informative doc from MS on the subject which explains the difference between Formula and Formula2 in more detail.

如果您想进一步了解隐式交集运算符，请查看这

If you want to know more about the implicit intersection operator then have a look at this

我早些时候回答了另一个问题，该问题涉及与如果您觉得有趣的话，请在这里。

I answered another question earlier on that involved implicit intersection with an example on how that actually works here if one finds it interesting.

这篇关于Excel VBA-如何添加动态数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！