CODEWITHSUNDEEP
vbaexcelloopsfor-loop
Fubudis
Fubudis

Reputation: 251

VBA for loop ending early

If I'm iterating k = 1 to 100, I need to sometimes restart an iteration if a criteria isn't met. In this case, I'm creating a random number between 1 and 100, but if that number is below 51, I want the iteration to start over.

Right now the code is returning a variable number of values. Sometimes 1, sometimes 90.

Any ideas why?

Public k as long
Sub Example()

For k = 1 To 100
    Call ExampleExtended
Next k

End Sub


Sub ExampleExtended()
Dim Val As Integer

Val = Int((100 - 1 + 1) * Rnd)

If Val < 51 Then
    k = k - 1
    Exit Sub
End If

Debug.Print Val

End Sub

Upvotes: 0

Views: 691

Answers (4)

sous2817
sous2817

Reputation: 3960

k is considered a local variable for both subs in your example, so you're not updating k like you think you are. Either increase the scope of k (which I don't recommend) or make ExampelExtended() a function that returns something you can use in the calling sub.

Here is one way. Obviously the function is kind of overkill, but trying to show how you can use a function to return a value to the calling sub:

    Sub Example()
    Dim counter     As Integer, _
        randNumber   As Integer

    For counter = 1 To 100

        randNumber = RandomBetween(50, 100)
        Debug.Print randNumber

    Next counter

    End Sub


    Function RandomBetween(lowerBound As Integer, upperBound As Integer) As Integer
        RandomBetween = Application.WorksheetFunction.RandBetween(lowerBound, upperBound)
    End Function

Is this closer?

Option Explicit

Sub Example()
Dim counter     As Integer, _
    randNumber   As Integer

For counter = 1 To 100

    randNumber = RandomBetween(1, 100)


    If randNumber < 50 Then
        counter = counter - 1
    Else ' this is here just as a check...should only print 100 numbers
        Debug.Print randNumber
    End If

Next counter

End Sub

Upvotes: 4

Uri Goren
Uri Goren

Reputation: 13700

Your loop has at least 100 iterations, and 150 iterations on average !

However, the Debug.Print Val is called 50 times on average. Therefore, you will almost always see less then 100 values in the console window.

(There's a 2^(-100) chance of seeing 100 lines in the console window)

If you change your code to:

Public k as long
Sub Example()

For k = 1 To 100
    Call ExampleExtended
Next k

End Sub


Sub ExampleExtended()
Dim Val As Integer

Val = Int((100 - 1 + 1) * Rnd)

Debug.Print Val

If Val < 51 Then
    k = k - 1
    Exit Sub
End If

End Sub

The amount of time the loop is executed will be the same as the amount of lines you have in your console

Upvotes: 1

Grade &#39;Eh&#39; Bacon
Grade &#39;Eh&#39; Bacon

Reputation: 3833

It looks like this would be better suited by using a Do While loop. Do While makes a loop indefinate until a condition is met - in this case, until a random number is > 51. Like so:

val = 1
Do While val < 51
    Val = Int((100 - 1 + 1) * Rnd)
Loop

Although in your case, this could be further simplified by just forcing the random number to be between 51 & 100, like so [taken from here: http://www.cpearson.com/excel/randomNumbers.aspx:

Dim Low As Double
Dim High As Double
Low = 51 '<<< CHANGE AS DESIRED
High = 100 '<<< CHANGE AS DESIRED
val = Int((High - Low + 1) * Rnd() + Low)

Upvotes: 2

MVAmorim
MVAmorim

Reputation: 105

You can use goto: 

Sub Example()

For k = 1 To 100

Call ExampleExtended

Next k

End Sub


Sub ExampleExtended()
Dim Val As Integer
start:
Val = Int((100 - 1 + 1) * Rnd)

If Val < 51 Then
goto start
k = k - 1
Exit Sub
End If

Debug.Print Val

End Sub

see if it works

Upvotes: 0

Related Questions