Can anyone tell me why this particular function doesn't work as a formula on a spreadsheet but it works fine in the immediate window?

Public Function test() As String

Range("A1:A3").Value = Application.WorksheetFunction.Transpose(Array("Apple", "Banana", "Orange"))

test = "Completed"

End Function