Solved

For...Next Syntax Question VBA

Posted on 2013-05-29
6
665 Views
Last Modified: 2013-05-29
I've taken over some systems at a college and some of the Access databases contain some code which has not been touched for years because "it works" and everyone is too afraid to alter it! Basically, the code takes a student's selected courses and allocates them to classes which is a lot more complex than it initially sounds. Anyway, I can figure out a lot of it, but I am a little perplexed by the code shown below which crops up in similar format throughout:

    For i = 1 To 4: choices(i) = "": pos(i) = 0: groups(i) = 0: Next i

Open in new window


It looks to me like the For...Next loop is being compressed into a single line. Would I be correct? In theory I should be able to expand the code out to a more standard four lines. I just want to make sure that my assumption is correct before messing with code which is nearly two decades old!
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
6 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 250 total points
ID: 39203781
Yes. The colon is a statement separator, allowing you to put more than one statement on a line. You could write the same as

For i = 1 To 4
    choices(i) = ""
    pos(i) = 0
    groups(i) = 0
Next i

Open in new window


Looks like some arrays are being initialized.
0
 

Assisted Solution

by:dtelfer
dtelfer earned 250 total points
ID: 39203782
Hi Karl,

you are completely corect.  the colon (":") acts as a command seperator in many dialects of BASIC, allowing multiple lines of code to be specified on a single source file line.  

You can separate the code into five lines by replacing the colons with carriage returns, which will substantially improve readability for you and the rest of your team.

For i = 1 to 4
   choices(i) = ""
   pos(i) = 0
   groups(i) = 0
Next i

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 39203783
Yes -it's exactly the same as a vertically presented set of 5 lines .
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Simon Ball
ID: 39203795
I assumed the : was a terminator but did a little accdb investigation to see if i could test the loop and output results using a "normal" for/next to get it to run in access 2010, and I get "subscript out of range" on . : pos(i) = 0

Not used arrays in Access for a long time though so it could be my error... SO you might be wise to test loops with ":" in in newer versions of access if you are using an older version and intending to upgrade.
0
 

Author Closing Comment

by:Karl_mark
ID: 39203797
Thanks all. I'll set about making the code more legible. It was written by a mathematician back in around 1992, and I'm trying to fathom how it actually does the allocation; there are lots of number variables thrown in with no commenting, so I'm adding some logfiles to see how it processes the records.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39203830
Sounds fun! Good luck :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 15 hours left to enroll

617 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