Solved

For...Next Syntax Question VBA - Uncompressing!

Posted on 2013-05-29
4
387 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 92

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now