Problem changing For Loop Maximum inside the loop

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
123
Consider the following code:
Code:
    Last = 10
    For I = 1 To Last
        Last = Last + 1
    Next I
At the end of the code you find I = 11 and Last = 21.

The implication of this is that once you set the Maximum for the loop, you can't change it.

So you have to use a Do While loop explicitly testing for the changed Maximum.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,953
Office Version
2010
Platform
Windows
Not sure what your question is, but at the end of the looping Last = 20, not 21.

Code:
Sub test()
Last = 10
    For i = 1 To Last
        Last = Last + 1
    Next i
    MsgBox Last & vbCrLf & i
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,057
Office Version
2010
Platform
Windows
The implication of this is that once you set the Maximum for the loop, you can't change it.
Correct... the loop limits are set immediately before the loop begins and cannot be changed by anything you do within the loop.
 

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
123
Yes, this is what I discovered. From my point of view (and something I find that often happens to me when I've been working with a piece of code over a long time), I understand how it should work in a way that I hadn't considered when I first wrote it. But it certainly took a long time to actually figure out what was going on in code that I expected to work since it was in the system programming. There is nothing I have ever encountered before that had this limit hard wired and un-documented feature before.
 
Last edited:

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
123
One other comment:
Don't forget to explicitly update the control variable which was handled by the "For Loop".
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
There is nothing I have ever encountered before that had this limit hard wired and un-documented feature before.
It's actually clearly documented in the language spec.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,843
Messages
5,489,216
Members
407,679
Latest member
Elaine Grass

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top