VBA oddity, modifying For loop

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
It's refreshing to know that to make VBA even more lightning fast it doesn't check the For limiting variable. /sarcasm mode off

Does the following surprise anyone? Surprises me. (And yes, I know what an Exit For is. This code is designed solely to illustrate the point.)
Code:
Sub JimmyLoopVar()
    Dim i As Long, K As Long
    K = 6
    For i = 0 To K
        If (i > 3) Then K = 2
        Debug.Print i; ; K
    Next
End Sub
So the loop runs 7 times regardless of K! (At least on XL03 )
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
K = 6
For i = 0 To K

The first loop is the only time it calculates K in the For/Next...

at the time it first reads for i = 0 to K,
it actually reads for i = 0 to 6.

after the first loop when it reads next, it doesn't REread for i = 0 to K...it goes straight to the first line after the for line..

You will see this demonstrated by Stepping Through the macro using F8.
it will only highlight the line "for i = 0 to K" on the first time through. Each loop after the first will go directly to the line "If (i > 3) Then K = 2"
 
Upvote 0
Would you expect this to do something other than it does? :-

Code:
Dim rng As Range, cell As Range
Set rng = [A1:A5]
For Each cell In rng
    Set rng = rng.Offset(, 1)
    MsgBox rng.Address
Next
 
Upvote 0
Where I said "it doesn't check" it should have said "but once," so jonmo1's expansive explanation fits what I literally said. But man! The way this goes, VBA is more optimized than C :cool:
 
Upvote 0
Well For Each is another matter. It necessitates a "list" or "collection," loosely speaking. Meanwhile integer for loops are very simple - only about 6 assembler statements. Now, it looks more like 5 :devilish:

BTW I'm not getting subscription notifications today...
 
Upvote 0
If you had expected an endless loop, you are changing the wrong variable:

Code:
Sub JimmyLoopVar()
    Dim i As Long, K As Long
    K = 6
    For i = 0 To K
        If (i > 3) Then i = 2
        Debug.Print i; ; K
    Next
End Sub
 
Upvote 0
No, I expected the loop to bail out. (You didn't?!) Here's the thing: in my recollection I' ve never see a compiled or interpreted language that did NOT check the limiting For variable every time. (Someone please check BASIC and confirm or deny this.) The key here is the word "variable."

Everyone's probably aware of the side effect of modifying the looping control variable, in almost any language. And there's always been controversy over whether the looping variable gets incremented "one extra time" or not when it's made it to the limiting variable. Now, in VBA there's no side effect on the limiting variable.

BASIC is what probably should be the best guide. Could someone check that?
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top