Finding the average of each column in a multi array in excel vba

Question

I have an array that is

myarray (1 to 37, 1 to 44) as variant

I'm trying to speed up my spreadsheet so rather than using big tables with lots of formulas in I'm turning to arrays, calculating them in memory and then writing the array to the sheet like such.

'Write array to worksheet.
Sheet1.Range("A1").Resize(UBound(myarray, 1), UBound(myarray, 2)).Value = myarray

This is working well except I'm not sure how to write code to perform certain calculations on specific rows or columns of the array. So if I wanted to know the average of all the elements in row 2 for example, how would that be written?

Many thanks in advance.

P.S. I should say I know I can cycle through and perform the average like so but I am more talking about using the various worksheet functions on specific rows or columns, this is what I don't understand how to write.

Answer 1

Thanks but I've sussed it now. I place all the elements of that particular row or column into a new array then simply use the worksheet function command. So in this example I am using functions on each column of the myarray. I'll post this in case it helps anyone else with worksheet functions in arrays.

For i = 1 To 44
    Erase Calc1Array()
    ReDim Calc1Array(1 To 37)
    For j = 1 To 37
        Calc1Array(j) = MyArray(j, i)
    Next j
    Answer1 = Application.WorksheetFunction.Max(Calc1Array)
    Answer2 = Application.WorksheetFunction.Average(Calc1Array)
    Answer3 = Application.WorksheetFunction.StDev(Calc1Array)
Next i