Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

For...Next Syntax Question VBA - Uncompressing!

OK, this is a further question to one I asked earlier about suppressing lines in VBA For...Next loops using a colon, i.e.:

For i = 1 to 4: choices(i) = 0 : Next i

Open in new window


I'm having problems with the code execution (subscript out of range on one of the arrays) and it is generating the error on the code loop shown below. The question is, how can I decompress the For...Next loops into several lines so I can actually follow what's going on? I know the record where it fails, and the line of code where it fails. The thing I can't work out is how it gets there and how to make the code easier to follow!

For i = 1 To 4: For J = 1 To 4: For K = 1 To 4: For L = 1 To 4
    If i <> J And i <> K And i <> L And J <> K And J <> L And K <> L Then
        Print #1, "i=" & i & "; J=" & J & "; K=" & K & "; L=" & L
        best = places(pos(1), i) + places(pos(2), J) + places(pos(3), K) + places(pos(4), L)
        Print #1, "places(pos(2), " & i & ") = " & places(pos(1), i) & _
            "; places(pos(2), " & J; " = "; places(pos(1), i) & "; " & _
            "places(pos(3), )" & K & ") = " & places(pos(3), K) & "; " & _
            "places(pos(4), " & L & ") = " & places(pos(4), L) & vbCrLf & "best = " & best

    End If

If best < min Then
    Print #1, "best = " & best & "; min = " & min & "Setting min = best"
    min = best
    session(1) = i
    Print #1, "session(1) = " & i
    session(2) = J
    Print #1, "session(2) = " & J
    session(3) = K
    Print #1, "session(3) = " & K; ""
    session(4) = L
    Print #1, "Session(4) = " & L
End If

Next L: Next K: Next J: Next i

Open in new window


Note that I added the Print commands to output to a logfile so I can see what it is executing at a point in time.
0
Karl_mark
Asked:
Karl_mark
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This is the decompressed version:

    For i = 1 To 4
        For J = 1 To 4
            For K = 1 To 4
                For L = 1 To 4
                    If i <> J And i <> K And i <> L And J <> K And J <> L And K <> L Then
                        Print #1, "i=" & i & "; J=" & J & "; K=" & K & "; L=" & L
                        best = places(pos(1), i) + places(pos(2), J) + places(pos(3), K) + places(pos(4), L)
                        Print #1, "places(pos(2), " & i & ") = " & places(pos(1), i) & _
                                  "; places(pos(2), " & J; " = "; places(pos(1), i) & "; " & _
                                  "places(pos(3), )" & K & ") = " & places(pos(3), K) & "; " & _
                                  "places(pos(4), " & L & ") = " & places(pos(4), L) & vbCrLf & "best = " & best

                    End If

                    If best < min Then
                        Print #1, "best = " & best & "; min = " & min & "Setting min = best"
                        min = best
                        session(1) = i
                        Print #1, "session(1) = " & i
                        session(2) = J
                        Print #1, "session(2) = " & J
                        session(3) = K
                        Print #1, "session(3) = " & K; ""
                        session(4) = L
                        Print #1, "Session(4) = " & L
                    End If

                Next L
            Next K
        Next J
    Next i

Open in new window

Where are your places, pos and session arrays created? If you're getting a subscript out of range, then generally that means exactly what it says - you're trying to get an element "larger" than the array holds (like you're trying to get an element at position 5 when there are only 4 elements in your array).

BTW, here's the tool I use to help me with indention in VBA:

http://www.oaltd.co.uk/indenter/default.htm

It's free and works very well in all versions of Access (at least all the ones I've tried).
0
 
peter57rCommented:
Can you post the code as it was before you amended it for testing.

For me, this layout is much cleaner than what you would get by expanding the for loop structure.  After all, the only thing that matters is what is going on in the innermost loop.
(I have never done it like that before but I shall from now on).

How are the arrays being declared?

Note that Dim x(4) contains 5 elements  with indexes 0 to 4.
0
 
Patrick MatthewsCommented:
Note that Dim x(4) contains 5 elements  with indexes 0 to 4.

Unless you are using Option Base 1.  Which I do not recommend.

IMHO, when declaring arrays, you should always, always, always explicitly state both the lower and upper bounds:

Dim x(0 To 4) As String
Dim y(1 To 5) AS Long

etc.
0
 
Karl_markAuthor Commented:
There are many areas in this code that do not adhere to good practice, hence my problems with debugging and trying to read it!
With the subscript out of range error, I know what it means, the problem is trying to fathom exactly where it is setting the values as the code is littered with examples such as that shown above. Thanks for all the suggestions; I'll take another look tomorrow.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now