VBA Compile Error: "For control variable already in use"

user_15067405 0
Viewed 300

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
vba excel
2 Answers

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
user_60718710

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
user_150674050
Related