Create new array in loop

Question

I want to find the average of some set of values.

I am doing this:

Dim avg(1 To 14)

For i = 1 To 14
    Dim helparray(1 To 3)
    For j = 1 To 3
         helparray(j) = Sheets("mysheet").Cells(i, j)
    Next j
    Avg(i) = Application.Average(helparray)
    Erase helparray
Next i

What happens with the declaration of helparray? Is it be redeclared on every iteration? Or is it only cleaned?

How do I create a NEW array on every iteration (and then delete it)?

Answer 1

Dynamic vs Static Arrays

• When you erase a static array like avg, as you put it, it only gets cleaned.
• You have to declare a dynamic array like helparray and later redim it to the size you need. And when you erase it, it will be deleted (will not occupy 'space' in memory).
• The second procedure shows how you can (should) avoid the use of the second array.
• Read this to find out more about static (fixed) and dynamic arrays.

The Code

Option Explicit

Sub testArrays()
    
    Dim i As Long
    Dim j As Long
    Dim avg(1 To 14)
    Dim helparray() As Variant
   
    For i = 1 To 14
        ReDim helparray(1 To 3)
        For j = 1 To 3
             helparray(j) = Sheets("mysheet").Cells(i, j)
        Next j
        avg(i) = Application.Average(helparray)
        Erase helparray
    Next i

    Debug.Print Join(avg, vbLf)
    
End Sub

Sub testArrays2()
   
    Dim i As Long
    Dim j As Long
    Dim avg(1 To 14)
    
    For i = 1 To 14
        avg(i) = Application.Average(Sheets("mysheet").Cells(i, 1).Resize(, 3))
    Next i

    Debug.Print Join(avg, vbLf)
   
End Sub

Answer 2

I haven't tested this but try the below:

Dim avg(1 To 14)
Dim helparray As Variant

For i = 1 To 14
    With ThisWorkbook.Worksheets("<name of your sheet here>")
        helparray = .Range(.Cells(i, 1), .Cells(i, 3))
    End With
    avg(i) = Application.Average(helparray)
Next i