How to use Excel's array-formulas for a UDF to read each cell correctly?

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 VariantDim Ansa() As VariantDim vData As VariantDim j As LongvData = AnimalName.Value2ReDim 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 SelectNext jMakesSound = AnsaEnd Function

The purpose of using Array formulas for the given sample is really obscure for me, but anyway, if you insist - try the following:

1. Select region C3:C7 (as on your topmost screen).
2. Press F2 to edit on the spot and type the following formula: =MakesSound(B2:B7)
3. Press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).

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.

Hope that was somehow helpful. Good luck!