Class variable as the counter of a For loop in VBA
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
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.
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.
If the scope of
counterisn'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.
- 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.