bad number of element in dynamic array with for each loop

Question

I don't understand why for each loop in vba doesn't return the good number of element when i use dynamic array.

For exemple, my array size is 4, and i have 5 iteration in for each loop ...

Public Sub test()

Dim t_direction() As String
Dim t_nextDirection() As String
Dim myDirection As Variant
Dim test As Integer
Var = 0

ReDim t_direction(4)

t_direction(0) = "N"
t_direction(1) = "S"
t_direction(2) = "E"
t_direction(3) = "W"

t_nextDirection = randomizeArray(t_direction)

For Each myDirection In t_nextDirection
    Var = Var + 1
Next myDirection

MsgBox (UBound(t_nextDirection))
MsgBox (Var)

End Sub

Public Function randomizeArray(ByVal t_array As Variant) As String()
Dim i As Integer
Dim j As Integer

Dim tmp As String
Dim numItems As Integer

numItems = UBound(t_array) - 1

    ' Randomize the array.
    For i = 0 To numItems
        ' Pick a random entry.
        j = Rand(0, numItems)

        ' Swap the numbers.
        tmp = t_array(i)
        t_array(i) = t_array(j)
        t_array(j) = tmp
    Next i
    'MsgBox (UBound(t_array))
    randomizeArray = t_array

End Function

Public Function Rand(ByVal Low As Long, _
                     ByVal High As Long) As Integer
  Rand = Int((High - Low + 1) * Rnd) + Low
End Function

brettdj · Accepted Answer · 2012-01-02 00:33:10Z

At the moment you are creating a 5 element array with
ReDim t_direction(4)
as the first element occurs as t_direction(0)

You should either

create a 4 element array ReDim t_direction(3) (ie 0 to 3) and then use numItems consistent with that, or
create a 4 element array ReDim t_direction with a base of 1 (ie 1 to 4) and then use numItems consistent with that (ie numItems = UBound(t_array)). The Option Base 1 below forces the first element to be 1 (which is then ensured anyow by using ReDim t_direction(1 To 4)

The code below uses the later approach. It returns 4 and 4 rather than your current 4 and 5

Option Base 1
Public Sub test()

Dim t_direction() As String
Dim t_nextDirection() As String
Dim myDirection As Variant
Dim test As Integer
Var = 0

ReDim t_direction(1 To 4)

t_direction(1) = "N"
t_direction(2) = "S"
t_direction(3) = "E"
t_direction(4) = "W"

t_nextDirection = randomizeArray(t_direction)

For Each myDirection In t_nextDirection
    Var = Var + 1
Next myDirection

MsgBox (UBound(t_nextDirection))
MsgBox (Var)

End Sub

Public Function randomizeArray(ByVal t_array As Variant) As String()
Dim i As Integer
Dim j As Integer

Dim tmp As String
Dim numItems As Integer

numItems = UBound(t_array)

    ' Randomize the array.
    For i = 1 To numItems
        ' Pick a random entry.
        j = Rand(1, numItems)

        ' Swap the numbers.
        tmp = t_array(i)
        t_array(i) = t_array(j)
        t_array(j) = tmp
    Next i
    'MsgBox (UBound(t_array))
    randomizeArray = t_array

End Function

Public Function Rand(ByVal Low As Long, _
                     ByVal High As Long) As Integer
  Rand = Int((High - Low + 1) * Rnd) + Low
End Function

nice shot, as always :) and congrats for the 5000+, you've been there very quickly

chris neilsen · Accepted Answer · 2012-01-02 00:44:36Z

2

ReDim t_direction(4) actually declares t_direction as 0 To 4

Its better to be explicit:

ReDim t_direction(0 To 3)

In the absence of a specified lower bound (using the To clause), then the default lower bound is used.
This default can be set to 0 or 1 by using Option Base {0|1} at module level.
In the absence of Option Base then the default default is 0

Notes:

In VBA you are not limited to 0 or 1 as the lower bound, you can use any value you want.

To iterate over an array use

For i = LBound(arr) To UBound(arr)

To calculate the number of items in an array use

numItems = UBound(arr) - LBound(arr) + 1

This way you are not making any assumptions on what the lower bound is

edited Jan 2, 2012 at 0:44

answered Jan 2, 2012 at 0:26

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

3 Comments

brettdj Over a year ago

Making this single change doesn't address the issue. It simply moves the issue from 4/5 to 3/4

chris neilsen Over a year ago

@Brettdj The root of the issue is bad assumptions on the operation of Redim and undersatnding of lower bounds. This answer is attempting to address the root problem, rather than simply debugging his code

brettdj Over a year ago

your original answer only went part way on the root issue as it didn't address the inconsistency on the bounding of numItems.

Collectives™ on Stack Overflow

bad number of element in dynamic array with for each loop

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related