Solved

For...Next Syntax Question VBA - Uncompressing!

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

930 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

14 Experts available now in Live!

Get 1:1 Help Now