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)?

Why do you need a new array for each loop iteration? You can just as well use the same array and delete its contents (which you do with Erase) after each iteration. — Nacorid
– Nacorid, Commented Nov 6, 2020 at 11:12
If you're just assigning a range to an array, it might be easier to just write Dim avg as Variant to declare a variant. And then avg = Range("A1:C14").value to put the values from the range into the array. — basodre
– basodre, Commented Nov 6, 2020 at 13:26

VBasic2008 · Accepted Answer · 2020-11-06 13:51:23Z

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

Zac · Accepted Answer · 2020-11-06 11:36:32Z

0

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

answered Nov 6, 2020 at 11:36

Zac

1,9501 gold badge10 silver badges22 bronze badges

Collectives™ on Stack Overflow

Create new array in loop

2 Answers 2

Dynamic vs Static Arrays

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Dynamic vs Static Arrays

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related