你需要做您的数组函数将数据读入数组，处理并创建一个输出数组。
然后使用ctrl-shift-enter将数组函数nneeds输入到多单元格数组公式（D3：D7）中。

 公共函数MakesSound（AnimalName As Range）As Variant 
 Dim Ansa（）As Variant 
 Dim vData As Variant 
 Dim j As Long 
 vData = AnimalName.Value2 
 ReDim Ansa（1到UBound（vData），1到1）
对于j = 1到UBound（vData）
选择案例vData（j，1）
案例Is =鸭
 Ansa（j，1）=嘎嘎！ 
 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？ 
结束选择
下一步j 
 MakesSound = Ansa 
结束函数
 

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.

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.

Unfortunately, I thought an arrayformula could pick up that I have different cells listing the animals and it ends like this snapshot instead.

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. :-)

Here is another snapshot showing the formulas next to arrayformulas for comparsion and code I used as well.

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.

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.

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