Array Problem When Dimension Not Known

[Orada]

Hi

I am trying to read data into an array for various reasons but I get the subscript out of range error.

I know the array is not dimensioned but the problem is that on each loop I do know know how many elements will be read. I assume that if I dim the array to 10 then the Ubound will become 10 and therefore I will have various zero entries.

I have tried to dimension the array with a variable "count" but that is not working either.

All help and learning appreciated.

count = 4
 
Dim arr() As Integer
'Dim arr(Count) As Integer - THIS DOESN'T WORK
arr(1) = 67
arr(2) = 21
arr(3) = 35
arr(4) = 28
For x = LBound(arr) To UBound(arr)
av = av + arr(x)
av = av / count
Next x
MsgBox "average = " & av

 

[mirabeau]

I guess this really gets into why you are doing this in the first place.

Depending, e.g. just how do you want to enter your numbers, there's a number of ways you might approach this.

But consider this code and see if it gives you any ideas

Sub arraze()
Dim arr As Variant
arr = Array(67, 21, 35, 28)
MsgBox "average = " & Application.Average(arr)
End Sub

Just add more numbers to the array if you like, or delete ones if you like.

 

[Domenic]

If you don't have a preset number of elements for your array, you'll need to declare a dynamic array using an empty set of parenthesis...

Dim arr() As Integer

Then, once you know how many elements you have in your array, you'll need to use the ReDim statement to tell VBA how many elements there are in your array...

ReDim arr(Count)

Note, however, that arrays are zero-based by default. So, for example, if Count equals 4, the array will contain 5 elements. If you want VBA to assume that arrays are 1-based, include the following statement before any procedure in the module...

Option Base 1

Alternatively, you can re-dimension your array as follows...

ReDim arr(1 to Count)

So, now, if COUNT equals 4, the array consists of 4 elements. Try...

Option Explicit

Sub test()

    Dim arr() As Integer
    Dim Cnt As Integer
    Dim x As Integer
    Dim av As Double
    
    Cnt = 4
    
    ReDim arr(1 To Cnt)
    
    arr(1) = 67
    arr(2) = 21
    arr(3) = 35
    arr(4) = 28
    
    For x = LBound(arr) To UBound(arr)
        av = av + arr(x)
    Next x
    
    av = av / Cnt

    MsgBox "average = " & av

End Sub

 

[mikerickson]

You can loop through any array or any number of dimensions (with any uBound or LBound in those dimensions) with a For Each loop

Dim oneElement as Variant

For Each oneElement In someArray
    Rem do something
Next oneElement

 

[Orada]

Thanks everyone for your replies, especially Domenic who explained it perfectly in my language as exactly what I wanted to achieve.