Excel VBA : Returning a Row Vector and a Column Vector

I want to select four cells in a column and send an array from a function to fill them. How?

See example code below

1. Select four cells
2. Start typing "= retArray()" without the quotes of course.
3. Now, instead of pressing ENTER, press CTRL-SHIFT-ENTER

The code :

Option Explicit
Function retArray() As Variant
    Dim retVal(4) As Integer
   
    retVal(0) = 1
    retVal(1) = 3
    retVal(2) = 4
    retVal(3) = 0
   
    retArray = Application.Transpose(retVal)
End Function

How are you supposed to know to use transpose? Good q.

If you want to write to four cells that are all in the same row, i.e., send a row vector, then you don't need the transpose. Think of it this way - you pick up a book on programming and they get to arrays. Are they going to talk about matrices - in which case the concept of a column vector arises? Nope - they put the entire [a b c.. z] on one line. Moral : the default is a row vector :)

Same thing for the row … and here's the code :

Option Explicit
Function retArray() As Variant
    Dim retVal(4) As Integer
   
    retVal(0) = 1
    retVal(1) = 3
    retVal(2) = 4
    retVal(3) = 0
   
    retArray = retVal
End Function

Kudos :
https://stackoverflow.com/questions/46915012/excel-vba-function-return-array-and-paste-in-worksheet-formula?rq=1
https://www.dummies.com/software/microsoft-office/excel/working-with-vba-functions-that-return-an-array-in-excel-2016/