Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

For...Next Syntax Question VBA - Uncompressing!

Posted on 2013-05-29
4
Medium Priority
?
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

704 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