CODEWITHSUNDEEP
vbaexceldynamic-arrays
VBAnoob
VBAnoob

Reputation: 21

Trouble with Dynamic Arrays

As a noobie to VBA, I am having a Hell of a time understanding how arrays, dynamic arrays in specific, work. I am an Industrial Engineering student, so most of my coding has been done with MatLab.

My question is: Why do I keep getting Run-time error '9' "Subscript out of range" for the following code:

Sub FunWithArrays()

Dim DynArray()
Dim i As Integer
Dim j As Integer
j = 1


For i = 1 To 10

    DynArray(j) = i
    j = j + 1
    ReDim DynArray(UBound(DynArray) + 1)
    MsgBox DynArray(i)

Next

End Sub

Any and all help is appreciated!

Upvotes: 1

Views: 317

Answers (1)

WhatEvil
WhatEvil

Reputation: 481

As people in the comments have mentioned, dynamic arrays are just that: dynamic. That is to say that if you declare an array without dimension, as you have here with Dim DynArray() then it doesn't at this point have any "slots" to store anything.

When an array is declared this way, you then need to Redim it to tell it how many slots you want it to have (presumably after determining this from some other data or user input).

The full code for this would be:

Sub FunWithArrays()

    Dim DynArray()
    Dim i As Integer
    Dim j As Integer
    j = 1

    Redim DynArray(10) 
    ' redimensioned the array to hold 10 items (since 
    ' you've set the loop as i=1 to 10 you must already 
    ' know that the array wants to hold 10 things)

    For i = 1 To 10

        'ReDim Preserve DynArray(i) 
        ' this is how you could do it if you didn't know in 
        ' advance that you were going to loop 10 times, 
        ' but as somebody else said, this is a slow way to do it. 

        DynArray(i) = j
        j = j + 1
        MsgBox DynArray(i)
        ' Generally instead of a messagebox here I would use:
        ' Debug.Print DynArray(i)
        ' this then prints out the value (on a new line each time) 
        ' into the immediate window which means you can see the 
        ' output without having to click "OK" each time.

    Next

End Sub

You can also redimension the array within the loop, or later on, to hold a different number of items, but if you want to keep the items already stored in the array you must use ReDim:

ReDim Preserve DynArray(i)

It's also good practice in general to declare the array type. When you use Dim DynArray() this creates an array of type Variant which can hold anything (String, Double, Object, etc.). If you explicitly declare the type e.g. Dim DynArray() as Integer then it'll only allocate enough memory to hold integers, which is more efficient. For many applications the difference in speed will not make a difference, but when you're dealing with looping through something thousands of times it can matter.

Upvotes: 1

Related Questions