Добрый день, уважаемые форумчане.
Получил в наследство на новом рабочем месте «почти готовый файл». Он, конечно, многое решает в работе. Но есть, по крайней мере, один косяк. Коллега посоветовал к Вам обратиться.
Одна из форм никак открывается. Я пробовал её подогнать по образцу других форм, но не смог. А в работе она совсем не лишняя.
Вот, пришёл к Вам за помощью.
В приложенном примере при запуске формы с кнопки появляется окно
Compile error:
For control variable already in use А в коде формы выделяется For i = 2 To
Причём во вроде бы аналогичной нормальной, рабочей форме (которую я удалил в примере) такой же фрагмент используется и вроде комбобокс точно такой же, и она работает.
А та, что оставлена в примере, не работает.
Знатоки, пожалуйста, помогите запустить форму.
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,01910 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
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 useCountas the control variables.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
[!includeSupport and feedback]
-
#1
Greetings,
I am receiving the compile error «For Control Variable Already in Use».
I have nested For Each Next loops with the Control Variable as «Row». I am looping by the row with a predefined range of rows. 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?
Thanks!
How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
-
#3
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 rowIn 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
-
#4
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 BBInitially 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?
-
#5
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.
-
#6
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?
-
#7
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")).RowI think you need to post your full code
|
|
