Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

For...Next Syntax Question VBA - Uncompressing!

Posted on 2013-05-29
4
Medium Priority
?
442 Views
Last Modified: 2013-06-07
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
Comment
Question by:Karl_mark
4 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39204099
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
 
LVL 77

Expert Comment

by:peter57r
ID: 39204104
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39204805
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
 

Author Comment

by:Karl_mark
ID: 39204871
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question