vba 如何将 Excel 的数组公式用于 UDF 以正确读取每个单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14661431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Excel's array-formulas for a UDF to read each cell correctly?
提问by Peter M Taylor
G'Day,
G'Day,
I have a question more towards helping myself understand how Excel's array-formulas (Control+Shift+Enter) can read each cell dynamically into the formula.
我有一个问题是帮助自己了解 Excel 的数组公式 (Control+Shift+Enter) 如何将每个单元格动态读入公式中。
I made an simplified example to show you what I mean.
我做了一个简化的例子来向你展示我的意思。
I created a small fictional farm that has some animals, listed by names and will provide the sound of what the animal makes. In the next column I made a User Defined Function called MakesSound which takes an input of what animal it is and respond the sound that animal it makes. As shown in the snapshot picture below.
我创建了一个虚构的小农场，里面有一些动物，按名称列出，并提供动物制作的声音。在下一列中，我创建了一个名为 MakesSound 的用户定义函数，它输入它是什么动物并响应它发出的声音。如下面的快照图片所示。
Unfortunately, I thought an arrayformula could pick up that I have different cells listing the animals and it ends like this snapshot instead.
不幸的是，我认为 arrayformula 可以发现我有不同的单元格列出了动物，但它以这个快照结束。
so how can I ask the arrayformula to recognise I have different cells in Column B as I know Quacks isn't the answer for other animals. :-)
那么我怎样才能让 arrayformula 识别我在 B 列中有不同的细胞，因为我知道 Quacks 不是其他动物的答案。:-)
Here is another snapshot showing the formulas next to arrayformulas for comparsion and code I used as well.
这是另一个快照，显示了 arrayformulas 旁边的公式，用于比较和我使用的代码。
Public Function MakesSound(AnimalName As String) As Variant
Select Case AnimalName
Case Is = "Duck"
MakesSound = "Quack!"
Case Is = "Cow"
MakesSound = "Moo!"
Case Is = "Bird"
MakesSound = "Tweet!"
Case Is = "Sheep"
MakesSound = "Ba-Ba-Ba!"
Case Is = "Dog"
MakesSound = "Woof!"
Case Else
MakesSound = "Eh?"
End Select
End Function
I'm open to suggestions.
我愿意接受建议。
Thanks, Peter.
谢谢，彼得。
回答by Charles Williams
you need to make you array function read the data into an array, process it and create an output array.
Then the array function nneeds to be entered in a multi-cell array formula (D3:D7) using ctrl-shift-enter.
您需要让数组函数将数据读入数组，对其进行处理并创建输出数组。
然后需要使用 ctrl-shift-enter 在多单元格数组公式 (D3:D7) 中输入数组函数。
Public Function MakesSound(AnimalName As Range) As Variant
Dim Ansa() As Variant
Dim vData As Variant
Dim j As Long
vData = AnimalName.Value2
ReDim Ansa(1 To UBound(vData), 1 To 1)
For j = 1 To UBound(vData)
Select Case vData(j, 1)
Case Is = "Duck"
Ansa(j, 1) = "Quack!"
Case Is = "Cow"
Ansa(j, 1) = "Moo!"
Case Is = "Bird"
Ansa(j, 1) = "Tweet!"
Case Is = "Sheep"
Ansa(j, 1) = "Ba-Ba-Ba!"
Case Is = "Dog"
Ansa(j, 1) = "Woof!"
Case Else
Ansa(j, 1) = "Eh?"
End Select
Next j
MakesSound = Ansa
End Function
回答by Peter L.
The purpose of using Array formulas for the given sample is really obscure for me, but anyway, if you insist - try the following:
对给定样本使用数组公式的目的对我来说真的很模糊，但无论如何，如果你坚持 - 请尝试以下操作：
- Select region
C3:C7(as on your topmost screen). - Press F2to edit on the spot and type the following formula:
=MakesSound(B2:B7) - Press CTRL+SHIFT+ENTERinstead of usual ENTER- this will define an ARRAY formula and will result in
{}brackets around it (but do NOT type them manually!).
- 选择区域
C3:C7（如最上面的屏幕）。 - 按F2即可当场编辑并输入以下公式：
=MakesSound(B2:B7) - 按CTRL+ SHIFT+ENTER而不是通常的ENTER- 这将定义一个 ARRAY 公式并
{}在它周围产生括号（但不要手动输入它们！）。
I'm not sure whether your UDF may handle array notation properly, but for usual Excel formulas this works as expected, e.g. try =LEFT(B2:B7,2)as an array one for step 2 - and this will return 2 starting letters from each animal name.
我不确定您的 UDF 是否可以正确处理数组表示法，但是对于通常的 Excel 公式，这可以按预期工作，例如尝试=LEFT(B2:B7,2)将第 2 步的数组作为一个数组 - 这将从每个动物名称返回 2 个起始字母。
Hope that was somehow helpful. Good luck!
希望这在某种程度上有所帮助。祝你好运！
