Calculating mean/ standard deviation from data in a closed Excel file

Question

I know I can get a value from a closed workbook using
ExecuteExcel4Macro("'C:\Location\Name\[excel1Test.xlsx]Sheet1'!R3C3") (https://stackoverflow.com/questions/32015693/getting-a-range-from-a-closed-workbook-into-an-array)

and do standard deviation calculation on open work book by

Public Function MySdiv(Worksheet As String, lookRange As String) As Double
  Set myRange = Worksheets(Worksheet).Range(lookRange)
  answer = Application.WorksheetFunction.StDev(myRange)
  MySdiv = answer
End Function

I have a closed Excel, I know the name and the path and everything about this Excel and I want to perform a .StDev function on array C1:C2000 for Sheet1 on excel1Test.xlsx.

The problem is getting the array of C1:C2000, and performing a .StDev on this array.

The excel1Test.xlsx has a lot of brothers, and they are all located in the same folder. They all look somewhat the same. In other words, they all have C1:C2000 and Sheet1.

I got it

By using ExecuteExcel4Macro(arg) and a While loop to find out the size of the array and a For loop to populate the array.

Function ArrayOut(strLocation As String, sheet As String, filename As String) As Double()

    Dim RowNum As Long, ref As String, arrWh() As Double, x As Integer, arg As String
    RowNum = 3
    ref = "C" & RowNum

    arg = " '" & strLocation & "[" & filename & "]" & sheet & "'!" & _
            Range(ref).Range("A1").Address(, , xlR1C1)
    x = 1
    Do While Not (ExecuteExcel4Macro(arg) = 0) ' while this cell doesn't equal to 0
        ReDim arrWh(0 To x - 1)
        RowNum = RowNum + 1
        ref = "C" & RowNum
        arg = " '" & strLocation & "[" & filename & "]" & sheet & "'!" & _
            Range(ref).Range("A1").Address(, , xlR1C1)
        x = x + 1
    Loop
    
    RowNum = 3
    x = 1
    ref = "C" & RowNum
    arg = " '" & strLocation & "[" & filename & "]" & sheet & "'!" & _
            Range(ref).Range("A1").Address(, , xlR1C1)
            
    For i = LBound(arrWh) To UBound(arrWh)
        arrWh(i) = ExecuteExcel4Macro(arg)
        RowNum = RowNum + 1
        ref = "C" & RowNum
        arg = " '" & strLocation & "[" & filename & "]" & sheet & "'!" & _
            Range(ref).Range("A1").Address(, , xlR1C1)
    Next i

    RowNum = 3
    x = 1
    ArrayOut = arrWh
End Function

Then putting the result into this function:

Function AssiSTDVCal(Arr() As Double) As Double
    Dim Mean As Double, i As Long, x As Long, NewArr() As Double
    ReDim NewArr(UBound(Arr))
    
    Mean = MeanCal(Arr)
    For i = LBound(Arr) To UBound(Arr)
        NewArr(i) = (Arr(i) - Mean) * (Arr(i) - Mean)
    Next i
    
    For x = LBound(NewArr) To UBound(NewArr)
        Sum = Sum + NewArr(x)
    Next x
    AssiSTDVCal = Sqr(Sum / (x - 1))
    
End Function

Function AssistSTDVCal(MeanNum As Double, Arr() As Double) As Double
    Dim i As Long, NewArr(UBound(Arr)) As Double, Sum As Double
    For i = LBound(Arr) To UBound(Arr)
        NewArr(i) = Abs(Arr(i) - MeanNum)
    Next i
    
    Dim x As Long
    For x = LBound(NewArr) To UBound(NewArr)
        Sum = Sum + NewArr(x)
    Next x
    
    AssistSTDVCal = sqrt(Sum / (x - 1))
    
End Function

'Mean calculation
Function MeanCal(Arr() As Double) As Double
    Dim i As Long, Sum As Double, avg As Double
    For i = LBound(Arr) To UBound(Arr)
        Sum = Sum + Arr(i)
    Next i
    avg = Sum / i
    MeanCal = avg
End Function

This code is very VERY slow. I have 100+ Excel files, 6 sheets per file, anywhere from 5000+ to 100 rows per sheets...

I'm looking for help again to speed this code up.