I found an Excel VBA online that can calculate the average and the STDEV of numbers in Cells A1 to A10. See script below. This works. I tested it.
What I would like to do now is change this script so that it can be used for 1D data arrays with varying amount of data.
Assume I have data in column B. The first row is always row 2 but the last row varies.
How would the script look like assuming that the size of the array is variable and the last row is variable? 

Function Mean(k As Long, Arr() As Single)

    Dim Sum As Single
    Dim i As Integer
    
    Sum = 0
    For i = 1 To k
        Sum = Sum + Arr(i)
    Next i
    
    Mean = Sum / k

End Function
Function StdDev(k As Long, Arr() As Single)

    Dim i As Integer
    Dim avg As Single, SumSq As Single
    
    avg = Mean(k, Arr)
    For i = 1 To k
         SumSq = SumSq + (Arr(i) - avg) ^ 2
    Next i
    
    StdDev = Sqr(SumSq / (k - 1))

End Function
Sub Compute()

    Dim Arr(10) As Single
    Dim Average As Single
    Dim Std_Dev As Single
    
    For i = 1 To 10
         Arr(i) = Sheets("Sheet1").Cells(i, 1)
    Next i
    
    Average = Mean(10, Arr)
    Std_Dev = StdDev(10, Arr)
    Sheets("Sheet1").Cells(5, 4) = Average
    Sheets("Sheet1").Cells(6, 4) = Std_Dev

End Sub