Consulting

Results 1 to 9 of 9

Thread: VBA Loop Control Variable Issue / Question

  1. #1

    VBA Loop Control Variable Issue / Question

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    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

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    Dim LCV
    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.


    Please take the time to read the Forum FAQ

  4. #4

    Thanks for the reply!

    Quote Originally Posted by Paul_Hossler View Post
    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.

  5. #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

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.


    Please take the time to read the Forum FAQ

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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
    or
    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
  •