Array returns “subscript out of range”

[Glory]

Sub test()
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Dim b() As Variant
<o:p> </o:p>
For i = 1 to 2
            b(i) = i
            MsgBox b(i)
Next

<o> </o>
<o></o>
The red line in this procedure returns an error. Why?
<o> </o>
Is it not possible to loop through the contents of a dynamic array?

 

[Marcelo Branco]

Hi Glory,

You have to Redim the array before you use it

Try this before the For

ReDim b(1 To 2)

HTH

M.

 

[AlphaFrog]

Sub test()
 
Dim b([COLOR="Red"]1 To 2[/COLOR]) As Variant
 
For i = 1 To 2
            b(i) = i
            MsgBox b(i)
Next

End Sub

 

[Marcelo Branco]

Glory,

Take a look at
http://www.cpearson.com/excel/vbaarrays.htm

M.

 

[Glory]

Marcelo; I appreciate the resource, but I don't know if I'm going to be able to parse what I need out of that. I think this is a really specific issue that might cause me some trouble if I don't understand what's going wrong.

To both of you: I need a dynamic array, set as the value returned from a user input box. I see that it works when I "Redim" the quantity of arguments of the array using the variable returned from the first inputbox... eg:

Sub test2()
 
a = InputBox("Quantity")
 
ReDim b(1 To a)
 
b(1) = InputBox("Title")
 
MsgBox b(1)
 
End Sub

Is that how I should be doing this, or is there a cleaner way?

Why do I have to redimension the scope of the array if it's dynamic? Isn't the whole point that it doesn't need an upper bound if you don't specifically declare it as a limited array?

 

[Glory]

Wait, but that isn't what I wanted to do anyway. That was just the simplest example of a fialure I was encountering that I put up here to make sure that no one got lost trying to interpret my question.

I was trying to loop through an array, setting the argument list as the looping counter variable.

Sub test2()
a = InputBox("Quantity")
ReDim b(1 To a)
[INDENT]For i = 1 To a
[INDENT]b(i) = InputBox("Title")
MsgBox b(1)
[/INDENT]Next
[/INDENT]End Sub

...And it works with Redim. I'm still wondering baout my previous question though. I thought the whole point of a dynamic array was to avoid explicitly declare the scope of the array.

Was I wrong about that? Is there a simpler solution than the one I'm using, if it's impossible to know the quantity of loops ahead of time?

 

[AlphaFrog]

ReDim is the dynamic part of a dynamic array. If you want to "add" elements to an array, use Redim with the Preserve keyword to keep the previous elements and their contents.

Sub test3_ReDim_Preserve()
    Dim b() As String, strTemp As String, Counter As Long
    Do
        strTemp = InputBox("Title")
        If strTemp <> vbNullString Then
            Counter = Counter + 1
            ReDim Preserve b(1 To Counter) ' Add another element
            b(UBound(b)) = strTemp
            MsgBox b(UBound(b))
        End If
    Loop Until strTemp = vbNullString
    MsgBox "b has " & UBound(b) & " elements."
End Sub

There are other ways this could be done as well:
The Split command will return an array from a delimited string.
A Dictionary object is a special type of array with an .Add method among other useful methods.