Class variable as the counter of a For loop in VBA

excel vba loop counter
vba counter increment
for loop vba
excel vba for loop two variables
vba for each loop
vba increment cell value by 1 loop
vba infinite loop
vba for loop step

I have a class module called MyClass, with a public integer in it:

Public i as Integer

When I try to use this variable in a For loop like so:

Dim MyInstance as MyClass: Set MyInstance = New MyClass
For MyInstance.i = 1 To 10
    Debug.Print "Hello"
Next

I get the error: Variable required. Can't assign to this expression

I have consulted the help page but cannot see how it applies to my case. The relevant fragment is: "You tried to use a nonvariable as a loop counter in a For...Next construction. Use a variable as the counter." But i is a variable after all, and not a Let Property function or any other expression.

What is wrong with the code?

EDIT: I should point out that the reason I want my iterator to be part of the class is that I have multiple instances of the class, serving different purposes in my project, and there are multiple nested For loops for each instance of the class. Therefore it is worth having the iterators belong to their respective objects, say:

For Client.i = 1 To Client.Count
    For Order.i = 1 To Order.Count
        For Item.i = 1 To Item.Count

etc.

I have settled for the following workaround but am still not entirely satisfied with it:

For ciii = 1 To Client.Count
    Client.i = ciii ' Client.i is later used in the code
    For oiii = 1 To Order.Count
        Order.i = oiii
        For iiii = 1 To Item.Count
            Item.i = iiii

If you need a workaround so that you iterate through a property of the instance, you could create a method to increment it, change your loop to a Do While ... Loop and call that method before the loop call.

'Class Module
Option Explicit

Public i As Integer

Public Sub increment_i()
i = i + 1
End Sub

Private Sub Class_Initialize()
i = 0
End Sub

'Module
Sub loop_myclass()

Dim instance As MyClass: Set instance = New MyClass

Do While instance.i <= 10
'Instance property dependent code here
    Debug.Print instance.i
    instance.increment_i
Loop

End Sub

Do Loops, Do While counter < 5. counter = counter + 1. Loop. If we didn't increment the counter variable then there would be no way for VBA to reach the end condition of  A for loop is a repetition control structure that allows a developer to efficiently write a loop that needs to be executed a specific number of times. Following is the syntax of a for loop in VBA. The For step is executed first. This step allows you to initialize any loop control variables and increment the step counter variable.

You cannot use MyInstance.i as the increment counter but you can use it as the terminator; e.g. For i = 1 To MyInstance.i.

MyClass class

Option Explicit

Public pi As Long

Public Property Get i() As Long
    i = pi
End Property

Public Property Let i(Value As Long)
    pi = Value
End Property

test sub procedure in Module1

Sub test()
    Dim MyInstance As MyClass, i As Long
    Set MyInstance = New MyClass
    MyInstance.i = 10
    For i = 1 To MyInstance.i
        Debug.Print "Hello"
    Next
End Sub

For Loops, The result is then stored in the variable MyNumber. And that's nice and easy. But suppose you want to add up the numbers 1 to a 1000. You wouldn't have to type​  In the code below, we're increasing (incrementing) the counter variable by 1 each time round the loop: Do While counter < 5. counter = counter + 1. Loop. If we didn't increment the counter variable then there would be no way for VBA to reach the end condition of "Do while counter is less than 5".

If you want a publicly accessible loop variable stick it at the top of a standard module i.e. declare the Public i at the top of a standard module.

Note that this would mean you need to re-write your standard module code as, as per point two, you are treating i as if it is a property/method of the class.

So, standard module code would be:

Public i As Long

Sub ........

   For i = 1 To 10
       Debug.Print "Hello"
   Next i

End Sub ......

If you want it to somehow be a property/method then you need to define Getters and Setters (potentially) in the class. And then re-write your module code accordingly. Especially if you are planning on looping using i, you will need an incrementor method in the class.

And yes, I have changed i to Long as there are no advantages, in this case I believe, of having it declared as Integer. A Long is a safer bet for avoiding potential overflow.

VBA Counter, We will declare 3 variables. One for loop purpose, one to count and one to store the value for the last row. Popular Course in this category. Sale. VBA Training (3​  Sub Beeps() For x = 1 To 50 Beep Next x End Sub. Using the Step keyword, you can increase or decrease the counter variable by the value you specify. In the following example, the counter variable j is incremented by 2 each time the loop repeats. When the loop is finished, total is the sum of 2, 4, 6, 8, and 10.

OK, I found the answer. There is a Microsoft help page on For…Next loop regarding VB, but I think it pertains to VBA as well.

It says:

If the scope of counter isn't local to the procedure, a compile-time warning occurs.

So there's not much to discuss here, it's just the way MS wants it to be. Though I'd think that if the scope is greater than the procedure it shouldn't cause any problems, but apparently it does.

Using For EachNext statements (VBA), Visual Basic automatically sets a variable each time the loop runs. Next loops can also iterate over a VBA class that you have written. The inner loop decrements a loop counter variable for every iteration of the loop. For indexA = 1 To 3 ' Create a new StringBuilder, which is used ' to efficiently build strings. Dim sb As New System.Text.StringBuilder () ' Append to the StringBuilder every third number ' from 20 to 1 descending.

Static statement (VBA), Variables declared with the Static statement retain their values as long statement declares a variable for a new instance of a worksheet: VB “History is about loops and continuums” – Mike Bidlo. This post provides a complete guide to the VBA For Loop, the VBA For Each Loop. If you are looking for information about the VBA While and VBA Do Loop then go here. If you want some quick info about the For loops then check out the Quick Guide table in the first section.

VBA For Loop, To learn more, launch our VBA Excel course now! The criteria set in the for loop automatically creates a counter variable, and will add 1 to the loop until the  However there are some clever guys out there, and on the Internet I discovered a way to enable the For Each Next loop in your own class collections. More about this in a moment. Looping with Item and a counter. Imagine a class collection, "clClients", which is a collection of clients with each client in a client class, "clClient".

Excel VBA Loops: For Next, Do While, Do Until, For Each (with , Now this, of course, is very simple of looping in Excel VBA (and you can also do In the For Next loop, you can use a Counter (or any variable) that will be used  Loop 5 (VBA For Loop in Reverse with STEP Instruction) It is not necessary that counter in the For loop will only move from low to higher values; instead, For loop can run backwards, too i.e. high to lower values. Even though the Step value is forward 1 by default, however, it can be set to a number in reverse order.

Comments
  • You cannot use MyInstance.i as the increment but you can use it as the terminator; e.g. For i = 1 To MyInstance.i
  • Cool. + 1. Can you not also use it as the start?
  • Sure! As long as it isn't the increment. Seeing my class page bounce into the foreground as I F8'ed through that probably has something to do with the restriction.