excel - 从 VBA 用户定义函数返回 Excel 数组常量

Question

我有一个这样的电子表格:

A1: APPLE
A2: BANANA
A3: ORANGE
A4: APPLE
A5: BANANA
A6: ORANGE

(这会重复直到 A20)

因此这个公式将计算“APPLE”的所有实例:

=COUNTIF(A1:A20, "APPLE")

此公式的作用相同，但范围不连续:

=SUM(COUNTIF(INDIRECT({"A1:A6", "A8:A20"}), "APPLE"))

INDIRECT函数采用 array constant范围定义为字符串。请注意每个范围周围的花括号和引号。 (这是基于here描述的技术。)

但是，如果我定义一个返回字符串数组的 VBA 函数并将该传递给INDIRECT，则似乎只考虑第一个范围。

这是我的 VBA 函数:

Function TestFn() As Variant
    TestFn = Array("A1:A6", "A8:A20")
End Function

这是我的公式:

=SUM(COUNTIF(INDIRECT(TestFn()), "APPLE"))

如果我使用 Excel 的“计算公式”按钮，我可以看到 TestFn() 解析为单个字符串“B1:B6”。但是，如果我使用 VBA 调试器，我会看到两个字符串都在那里。

Expression      Value       Type
TestFn                      Variant/Variant(0 to 1)
 - TestFn(0)    "B1:B6"     Variant/String
 - TestFn(1)    "B8:B20"    Variant/String

如何从 Excel VBA 函数返回一个字符串数组，并以相同的方式传递到 INDIRECT 中？

我认为这可以归结为:在没有 VBA 的情况下构造公式时使用花括号创建什么内部对象，我可以从 VBA 中创建相同的内部对象吗？

<小时/>

我还尝试返回一个字符串数组，而不是包含字符串数组的变体。

Function TestFn() As String()
    TestFn = Split("B1:B6,B8:B20", ",")
End Function

这些是调试器中的类型:

Expression      Value       Type
TestFn                      String(0 to 1)
 - TestFn(0)    "B1:B6"     String
 - TestFn(1)    "B8:B20"    String

但是结果是一样的。我认为我需要使用某种类型的容器类型，但我不确定要使用什么或如何弄清楚要使用什么。

<小时/>

如果有更好的方法，我实际上不需要使用 SUM...INDIRECT hack，并且我很乐意使用数组公式如果合适的话。

如果我可以让我的函数直接返回范围，而不是表示范围的字符串，那会比我当前的方法更好。

如果我可以设置一个像这样的数组公式(不必完全像这样):

{=COUNTIF(TestFn(), "APPLE")}

然后让我的 UDF 看起来像这样:

Function TestFn() As Range()
    TestFn = Array(Range("A1:A6"), Range("A8:A20"))
End Function

或者可能是这样的:

Function TestFn() As Range
    TestFn = Union(Range("A1:A6"), Range("A8:A20"))
End Function

这会满足我的需求。

这里的关键是我只希望我的 VBA 只定义对哪些单元格进行操作，而不是操作是什么，并且我想处理非连续范围。我希望在 Excel 公式中定义实际操作，因为这些操作将由不希望了解如何读写 VBA 代码的人进行修改。 (哎呀，我也不想再次查看 VBA 代码。)在本例中，我使用的是 COUNTIF，但我的 VBA 函数也将与其他 Excel 公式函数一起使用。

Answer 1

当然有人可以更好地解释这一点，但让我尝试一下。您都使用过:

=SUM(COUNTIF(INDIRECT({"A1:A6","A8:A20"}), "APPLE"))

还有...

=SUM(COUNTIF(INDIRECT(TestFn()), "APPLE"))

关于第一个公式；您基本上已经使用了 INDIRECT() 函数将文本字符串转换为实数范围。我必须说，这实际上是 INDIRECT() 的一个非常好的用法:)。

但是使用大括号这样做，您告诉 Excel 它不仅仅是一个字符串，您已经向函数提供了多个数据，一个数组!通常，Excel 中的数组是一组简单的数据。该数据可以是文本、数字或两者。您使用了文本。

在第二个公式中，您通过 UDF 为公式提供了一个数组，但它缺少大括号。 Excel 不知道您想要比较多个范围，因此只会计算数组中的第一项。

但是，使用 CtrlShiftEnter 可以告诉 Excel 您想要向公式提供一个数组，即一组数据/范围比较一下。

所以:

{=SUM(COUNTIF(INDIRECT(TestFn()), "APPLE"))}

可以用:)

我确信其他人可以更好地解释；)