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 而不是 =Cells(x,y).Formula

_说明:

显示的 @ 称为隐式交集运算符.来自 MS 文档:

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

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

但为什么它会出现在您较新的 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屋！