Working with an array

[Otto Moehrbach]

Excel 2002, WinXP
The MsgBox in this simple macro produces Two, Three in turn. It skips the
One.
But if I change the "1" in the For statement to a "0", I get One, Two,
Three.
Why does this happen?
Thanks for your help. Otto

Public TheArray() As Variant
Dim c As Long

Sub MacroOne()
ReDim TheArray(1 To 3)
TheArray = Array("One", "Two", "Three")
For c = 1 To UBound(TheArray)
MsgBox TheArray(c)
Next
End Sub

 

[Dave Peterson]

Put a watch on TheArray and step through your code.

When I did it, assigning the array redimmed TheArray(0 to 2).

If I added "option base 1", it redimmed it again, but started at 1 (much like
not redimming it at all):

Option Base 1
Sub MacroOne()
Dim c as long
ReDim TheArray(1 To 3)
TheArray = Array("One", "Two", "Three")
For c = 1 To UBound(TheArray)
MsgBox TheArray(c)
Next
End Sub

But I think I'd just use:

Sub MacroOneA()
Dim c as long
Dim TheArray as Variant
TheArray = Array("One", "Two", "Three")
For c = lbound(TheArray) To UBound(TheArray)
MsgBox TheArray(c)
Next
End Sub

And never have to worry about it again.

 

[Otto Moehrbach]

Thanks Dave. You've come through again. Otto

Put a watch on TheArray and step through your code.

When I did it, assigning the array redimmed TheArray(0 to 2).

If I added "option base 1", it redimmed it again, but started at 1 (much
like
not redimming it at all):

Option Base 1
Sub MacroOne()
Dim c as long
ReDim TheArray(1 To 3)
TheArray = Array("One", "Two", "Three")
For c = 1 To UBound(TheArray)
MsgBox TheArray(c)
Next
End Sub

But I think I'd just use:

Sub MacroOneA()
Dim c as long
Dim TheArray as Variant
TheArray = Array("One", "Two", "Three")
For c = lbound(TheArray) To UBound(TheArray)
MsgBox TheArray(c)
Next
End Sub

And never have to worry about it again.