For control variable already in use vba ошибка

Permalink

Cannot retrieve contributors at this time

title	keywords	f1_keywords	ms.prod	ms.assetid	ms.date	ms.localizationpriority
For control variable already in use	vblr6.chm1011174	vblr6.chm1011174	office	9b817917-5156-7dc6-f4f1-4fc6626ad5c9	06/08/2017	medium

When you nest For…Next loops, you must use different control variables in each one. This error has the following cause and solution:

An inner For loop uses the same counter as an enclosing For loop. Check nested loops for repetition. For example, if the outer loop uses For Count = 1 To 25, the inner loops can’t use Count as the control variables.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

Источник

This particular use of a nested «For» is something that I did not expect. I have trimmed it down to a very simple example. The outer For loop works perfectly using the member of the Type «abc», but the enclosed For statement produces the compiler error. I’ve hunted for an explanation, but so far have found none.

Option Explicit

Private Type abc
    i As Integer
    j As Integer
End Type

Private Sub CommandButton1_Click()
    Dim dog As abc
    Dim cat As abc
    
    For dog.i = 1 To 10
        For cat.i = 5 To 9
        
        Next
    Next
End Sub

asked Jan 23, 2021 at 20:20

According to this documentation, nested for loops must use different variables for their control counter. And according to this documentation, Type elements follow the same rules as variables.

Element names also follow standard variable naming conventions, except that keywords can
be used.

I suppose there must be something about the struct (Type) that when you reference one of its elements, it uses that as a named variable of sorts. So because your two control abcs have an element named i, the for loop is saying «hey, there is already another for loop above me using a control variable named i«. Notice you won’t get the same issue if you use dog.i for the outer loop, and cat.j for the inner.

As for solutions to this, you could store the values of dog.i and cat.i into a separate variable to use with this loop structure:

Private Sub CommandButton1_Click()
    Dim dog As abc
    Dim cat As abc
    Dim x As Integer
    Dim y As Integer
    
    x = dog.i
    y = cat.i
    
    For x = 1 To 10
        For y = 5 To 9

        Next
    Next
End Sub

answered Jan 23, 2021 at 20:37

ArcherBirdArcherBird

2,0199 silver badges35 bronze badges

I had wanted to determine if the error I was seeing was a VBA limitation (flaw) or whether there was some Setting or Option that I did not know, that would be appropriate.
I have come up with a work-around that is acceptable for what I need to do.

Option Explicit

Private Type abc1
    i As Byte
    j As Byte
End Type

Private Type abc2
    i As Byte
    j As Byte
End Type

Private Sub CommandButton1_Click()
    Dim dog As abc1
    Dim cat As abc2
    
    For dog.i = 1 To 10
        For cat.i = 5 To 9
        
        Next
    Next
End Sub

answered Jan 24, 2021 at 18:11

Источник

How can I maintain the nested loop structure and use a variable that loops using a row without using the «For Each Row in CC» structure?

Presumably, you wrote something of the form:

Rich (BB code):

For Each row In CC
    For Each row In CC
        [....]
    Next row
Next row

In that context, «row» is a variable name. And by the way, «row» is a poor name to use for a variable because it is also a keyword — a special VBA word like «if», «for», «next», etc.

(VBA can usually tell the difference by context. But it is still considered «poor programming practice».)

Choose different control variable names for each nest for-loop. For example:

Rich (BB code):

For Each r1 In CC
     For Each r2 In CC
         [....]
     Next r2
Next r1

(I like brief control variables. But you might prefer row1 and row2 instead of my r1 and r2.)

Note that the order of control variable names in the Next statements is the opposite of the order in the For statements.

In other words, we always loop innermost for-loops first.

Last edited: Nov 26, 2017

Thanks for the reply!

Here is the upper portion of the nested loops:

Code:

For Each Row In CC        
        H = Range(Cells(E, "A")).Row
        BB = PA.ws.Range(Cells(G, "A"), Cells(I, "A")).Row
                      
        With PA.ws
              
            For Each Row In BB

Initially I had integer variables in place of «Row», but received an error «variant or object required». When you assign the control variable r1 and r2, what types of variables are these? Range?

If CC and BB are ranges any loop control variable you use with them will also be a range and should be declared as such.

Will the machine know to step from the G row variable to the I row variable in .Range(Cells(G, «A»), Cells(I, «A»)).Row when

Code:

Set R2 = .Range(Cells(G,"A")).Row

Why do you have .Row in the below

Code:

BB = PA.ws.Range(Cells(G, "A"), Cells(I, "A"))[COLOR="#FF0000"].Row[/COLOR]

which changes it from a Range to a number when you have it looping through a range below?

and below you are using Set which only applies to an Object(in this case a range) when the .Row again changes the Range to a number.

Code:

Set R2 = .Range(Cells(G,"A")).Row

I think you need to post your full code

Источник

According to this documentation, nested for loops must use different variables for their control counter. And according to this documentation, Type elements follow the same rules as variables.

Element names also follow standard variable naming conventions, except that keywords can
be used.

As for solutions to this, you could store the values of dog.i and cat.i into a separate variable to use with this loop structure:

Private Sub CommandButton1_Click()
    Dim dog As abc
    Dim cat As abc
    Dim x As Integer
    Dim y As Integer
    
    x = dog.i
    y = cat.i
    
    For x = 1 To 10
        For y = 5 To 9

        Next
    Next
End Sub

Источник

VBForums
Visual Basic
Visual Basic 6 and Earlier
For Control Variable Already In Use !

Oct 10th, 2003, 04:06 PM

#1
Thread Starter

pathfinder
For Control Variable Already In Use !
For a long Time, I’ve known the following raises the error message «For Control Variable Already In Use».

VB Code:

Private Sub Command1_Click()
Dim M_I_6() As Long
ReDim M_I_6(2)

List1.Clear
For M_I_6(0) = 0 To 1
For M_I_6(1) = 0 To 1
For M_I_6(2) = 0 To 1
List1.AddItem M_I_6(0) & " " & M_I_6(1) & " " & M_I_6(2)
Next M_I_6(2)
Next M_I_6(1)
Next M_I_6(0)
End Sub

I’ve always used either seperate variables, perhaps MyI, MyJ, and MyK when useing Nested Loops in one procedure, or Dimming something like MyI in an iterative call.

However, I just thought to test the following, and it works, no problem!

VB Code:

Private Sub Command1_Click()
Dim M_I_6() As Long
ReDim M_I_6(2)
List1.Clear
Call DO_I2(M_I_6, 0)
End Sub
Private Sub DO_I2(ByRef MyEmmy2() As Long, ByRef MyLev As Long)
If MyLev > 2 Then

List1.AddItem MyEmmy2(0) & " " & MyEmmy2(1) & " " & MyEmmy2(2)
Else
For MyEmmy2(MyLev) = 0 To 1
Call DO_I2(MyEmmy2, MyLev + 1)
Next MyEmmy2(MyLev)
End If
End Sub

As you can see, the Array, M_I_6, is a ByRef array as MyEmmy2 in the DO_I2 sub. I’ve always expected it, if I had thought about it before, that it, too would yell back the error message.

But It Doesn’t!!!

Not that I’m complaining, Although I wish I knew that a few years ago. But…

Do any of you have a decent reasoning why it works useing iterative calls, but not when you use the array elements in one procedure?

-Lou
Oct 10th, 2003, 06:34 PM

#2

Its half twelve at night, I’m not feeling 100%, and I’m a bit tired, but I’d hazard a guess that its a scope issue.
Namely, that within the current scope of the control variable, modifications cannot be made to it by another for loop.
Oct 10th, 2003, 06:37 PM

#3
VB doesn’t really seem to care that a For control points to the same memory, as long as it has a different name. Consider:
VB Code:
```
Sub DoDooDah()
    Dim a As Long
    a = 1




  


                       
  



    DooBee a, a
End Sub
Private Sub DooBee(ByRef DooDah1 As Long, ByRef DooDah2 As Long)
    For DooDah1 = 0 To 5
        For DooDah2 = 0 To 5
            Debug.Print DooDah1
        Next
    Next
End Sub
```
DooDah1 value changes as DooDah2 changes because they point to the same memory. In effect, that is the same as if I had used the same variable name in both Fors.

I guess a better question is why your first version doesn’t work. The «already in use» is a compile error. That would indicate that when VB compiles the code it checks for duplicate For controls and doesn’t allow them—not because they wouldn’t really work, but because it thinks you’re a bonehead that forgot that you already used the control in another For (which is probably usually the case).
Oct 10th, 2003, 09:15 PM

#4

Thread Starter

pathfinder

Originally posted by WorkHorse
….The «already in use» is a compile error. That would indicate that when VB compiles the code it checks for duplicate For controls and doesn’t allow them—not because they wouldn’t really work, but because it thinks you’re a bonehead that forgot that you already used the control in another For (which is probably usually the case).

so, on the eve of my birthday, you are saying the cdode that i posted indicates i’m i bonehead/

forgive me for ther lack of capitals and question marks, but its hard to do when your useing only your middle finger.
Oct 10th, 2003, 09:21 PM

#5

Thread Starter

pathfinder

Originally posted by plenderj
[B]Its half twelve at night, I’m not feeling 100%, and I’m a bit tired, [b]

HAPPY BIRTHDAY hurrrhip! ‘scuze me!

Namely, that within the current scope of the control variable, modifications cannot be made to it by another for loop.

But, isn’t, when you dim an array appropriately, arr(0) is NOT the same variable as arr(1)??and one more, for emphasis?

-Lou
Oct 10th, 2003, 09:29 PM

#6

Originally posted by NotLKH
But, isn’t, when you dim an array appropriately, arr(0) is NOT the same variable as arr(1)??and one more, for emphasis?

-Lou [/B]

Makes sense to me. I think boneheads at MS just coded the compiler to look for any «For » & {variable name before any parenthises}. So if it finds «For arr» and another «For arr» without a closing «Next» then raise compile error. I think it is just a text-based check that MS built into the compile builder without thinking any boneheads like you (Happy Bday!!!) would actually use array elements as For controls. (Actually I think they thought people might use arrays, but figured the elements called in the array if variable could cause duplication errors that could lead to problems—endless loops and such. Anyway, I think it’s just a VB compile text checking error that is trying to do the right thing, but doesn’t really work for people that know what they are doing and REALLY do want to use the same variable names for Fors)

Last edited by WorkHorse; Oct 10th, 2003 at 09:36 PM.

VBForums
Visual Basic
Visual Basic 6 and Earlier
For Control Variable Already In Use !

Posting Permissions

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
[VIDEO] code is On
HTML code is Off

Forum Rules

Click Here to Expand Forum to Full Width

Источник

Forum
VBA Code & Other Help
Excel Help
[SOLVED] VBA Loop Control Variable Issue / Question

Thread: VBA Loop Control Variable Issue / Question

07-15-2021, 11:05 AM

#1

The code below fails to compile and generates a «For control variable already in use» error on the second for loop. The entire test program is here. The loop works if for example the inner and outer loops have var names such as i and n, but while array(1) and array(2) are separate variable locations the compiler seems to be treating the array name as the single specific variable.

The reason I want to have a loop control variable as an element of an array is because I am writing code that can allow a dynamic number of for loops. I am certain I have used compilers that have allowed this in the past but I suspect no dice with VBA. If there is a switch or other trick that allows this I would be very grateful.

Error generated on second loop where comment is placed.

TIA, Paul

btw, this is not a work related issue, I am simply a hobbiest and have been coding for fun since the late ’70’s

Option Explicit
Sub TestForLoopVariableName()

Dim LCV(2) As Integer

For LCV(1) = 1 To 10
For LCV(2) = 1 To 10 ‘for control variable already in use
counter =counter+!
Next LCV(2)
Next LCV(1)

End Sub

07-15-2021, 12:29 PM

1. I think you’re out of luck

https://docs.microsoft.com/en-us/off…next-statement

Syntax

For counter = start To end [ Step step ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]

The For…Next statement syntax has these parts:

Part Description

counter Required. Numeric variable used as a loop counter. The variable can’t be a Boolean or an array element.

start Required. Initial value of counter.

end Required. Final value of counter.

step Optional. Amount counter is changed each time through the loop. If not specified, step defaults to one.

statements Optional. One or more statements between For and Next that are executed the specified number of times.

2. Couple of tweaks
Option Explicit


Sub TestForLoopVariableName()
    Dim LCV(1 To 2) As Long
    Dim counter As Long                 '   Added Dim


    For LCV(1) = 1 To 10
        For LCV(2) = 1 To 10            '   for control variable already in use
            counter = counter + 1       '   s/b a number 1, not exclamation mark
        Next LCV(2)
    Next LCV(1)


    MsgBox counter


End Sub

Part	Description
counter	Required. Numeric variable used as a loop counter. The variable can’t be a Boolean or an array element.
start	Required. Initial value of counter.
end	Required. Final value of counter.
step	Optional. Amount counter is changed each time through the loop. If not specified, step defaults to one.
statements	Optional. One or more statements between For and Next that are executed the specified number of times.

———————————————————————————————————————

Paul

Remember: Tell us WHAT you want to do, not HOW you think you want to do it

1. Use [CODE] ….[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] — (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments — Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq…._new_faq_item3

07-15-2021, 12:36 PM

#3
Sub TestForLoopVariableName() Dim LCV1 As Integer, LCV2 As Integer, Counter as Integer For LCV1= 1 To 10 For LCV2 = 1 To 10 'for control variable already in use counter =counter + 1 Next LCV2 Next LCV1 End Sub
Use of proper parentheses declares an array variable

Create a one dimension Array with 10 «Slots» numbered 0 to 9, where each «slot» can only hold an integer.
Dim LCV(0 to 9) as integer
Create a one dimension Array with 10 «Slots» numbered 0 to 9, where each «slot» can only hold a String.
Dim LCV(0 to 9) as String
General Practice is to create Arrays that can hold any Type:

Create a Variant Variable that can hold darn near anything, and can be turned into an array

Turn a Variant into a 2D array 10 «Rows» deep and 10 «Columns» wide
Redim LCV(1 to 10, 2 to 11)
Create a filled Array from a Variant Variable
LCV = Array(1,"Abc",42,"DEf",2,3,4,5,6,"X")
Using a loop to fill a 1D array
Dim i as Long Dim LCV(1 to 10) as Integer For i = 1 to 10 LCV(i) = i 'VBA Converts Long i to an integer to fit the array "Slot". The Index i is a long. Next i
Using loops to fill a 2D array
Dim i as Long, j As Long Dim LCV Redim LCV (1 To 10, 7 To 20) For i = 1 To 10 For j = 7 To 20 LCV(i, j) = i x j Next j Next i 'Show the result on sheet1 Sheets("Sheet1").Range("A1").Resize(10, 14) = LCV.Value
Also see: http://www.snb-vba.eu/VBA_Arrays_en.html
Last edited by SamT; 07-15-2021 at 01:18 PM.

I expect the student to do their homework and find all the errrors I leeve in.

07-15-2021, 02:01 PM

Thanks for the reply!

Originally Posted by Paul_Hossler

1. I think you’re out of luck

https://docs.microsoft.com/en-us/off…next-statement

Syntax

For counter = start To end [ Step step ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]

The For�Next statement syntax has these parts:

Part Description

counter Required. Numeric variable used as a loop counter. The variable can’t be a Boolean or an array element.

start Required. Initial value of counter.

end Required. Final value of counter.

step Optional. Amount counter is changed each time through the loop. If not specified, step defaults to one.

statements Optional. One or more statements between For and Next that are executed the specified number of times.

[/CODE]

Thanks — I googled around before posting and I believe I saw that page. I remember wondering what could possibly cause me to use a Boolean variable as a loop control variable. Probably while chuckling to myself over that, I missed the rest of the line. If ONLY they had the foresight to highlight it in RED as you did, I wouldn’t have posted. But then again, It would have not caused me to find this BBS and sign up. Happy accident as far as I am concerned.

07-15-2021, 02:04 PM

#5

Thanks for the pointers. I am trying to create a program that will allow for a dynamic number of loops and using a one dimensional array was key in my effort. I will need to change to a different type of loop, one that hopefully allows for array variables. Once I get the logic down for dynamic looping, I am planning on re-doing the code recursively.

Paul
07-15-2021, 05:01 PM

#6
The loop Control can run from n to an array element
Dim i as long For i = n to Array(y) 'where Element y in the array is a Numerical value ' ' ' Next x
Here’s two examples of dynamic loops, one from Paul and the other from myself.
I expect the student to do their homework and find all the errrors I leeve in.
07-15-2021, 05:29 PM

#7
SamT — I believe (from the OP’s first post) that he wants to use array elements as loop control indexes (indicies??)
For A(1) = 1 to 100 For A(2) = 1 to 100 Next A(1) Next A(2)
You can if the loops are not nested, but I don’t know how useful that’d be
Sub TestForLoopVariableName() Dim counter As Long Dim LCV(2) As Long MsgBox VarPtr(LCV(1)) & " -- " & VarPtr(LCV(2)) For LCV(1) = 1 To 10 counter = counter + 1 Next LCV(1) MsgBox counter For LCV(2) = 1 To 100 counter = counter + 1 Next LCV(2) MsgBox counter End Sub
My assumption is that the ‘complier’ uses the Long at the ‘For ‘ variable address as a loop control, so the Long at address (1) is used then the Long at address (2) is used
Last edited by Paul_Hossler; 07-15-2021 at 05:41 PM.

———————————————————————————————————————

Paul

Remember: Tell us WHAT you want to do, not HOW you think you want to do it

1. Use [CODE] ….[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] — (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments — Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq…._new_faq_item3
07-16-2021, 08:55 AM

#8
I believe (from the OP’s first post) that he wants to use array elements as loop control indexes (indicies??)

Yeah, that was obvious. And that’s the part that doesn’t make logical programming sense to me.
For Array(Element #x) = n to n'
If that is even possible: Increment the array Element and run the inner loop code.

That is why I «shotgunned» my answer to cover many aspects of arrays and loops.
I expect the student to do their homework and find all the errrors I leeve in.

Part	Description
counter	Required. Numeric variable used as a loop counter. The variable can’t be a Boolean or an array element.
start	Required. Initial value of counter.
end	Required. Final value of counter.
step	Optional. Amount counter is changed each time through the loop. If not specified, step defaults to one.
statements	Optional. One or more statements between For and Next that are executed the specified number of times.

07-17-2021, 01:59 AM

Sub M_snb()
   M_loop "abcdefg"

   M_loop "hijklmnop"
End Sub

Sub M_loop(c00)
   for j=1 to len(c00)
      x= x+2^j
   next
   msgbox x
End Sub

Sub M_snb()
   sn = Split("4 10")
   
   For j = 1 To sn(0)
     For jj = 1 To sn(1)
        y = y + 1
     Next
   Next
   
   MsgBox y
End Sub

Last edited by snb; 07-17-2021 at 03:07 AM.

Tags for this Thread

Posting Permissions

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
[VIDEO] code is On
HTML code is Off

Forum Rules

Источник

For control variable already in use vba ошибка