Access VBA Macro Programming

Case Is > 8

MsgBox "Grade is above 8"

Case Else

Msgbox "Grade not in conditional statements"

End Select

End Sub

Looping

Without looping facilities, programs would be extremely tedious and difficult to maintain.

Loopingallows a block of code to be repeated until a condition or a specified value is met.

Suppose, for example, you wanted to display the numbers from 1 to 5. You could write the

program as follows:

MsgBox " 1 "

MsgBox " 2 "

Msgbox " 3 "

Msgbox " 4 "

MsgBox " 5 "

This would work, but it is very inefficient and does not make use of the functionality of VBA.

If you wanted to display more numbers, you would have to write more code. If you wanted to

display all the numbers up to 1,000, it would require you to add an additional 995 lines of

code! This would not be very efficient.

For..Next Loops

This code can be reduced and made easier to maintain by using the For..Next looping

statement as follows:

For n = 1 to 5

MsgBox n

Next n

The message box will appear five times showing the values ofnfrom 1 to 5.

The variable used can be anything. Although I usednhere, it could be a word such as

num, but it must be consistent throughout the looping process. You could not useFor n = 1

to 5and then try to use an index calledm. If you changed the line “Next n” with “Next m,”

you would get an error because it does not match your original loop. Also, you must not use a

reserved word for the variable name. You can put as many instructions as necessary between

For and Next and even call subroutines or functions. The start and end values in the For..Next

loop can also be different—they do not have to start at 1 or end at 5.

Stepgives extra functionality. You may have noticed that the variablenis incremented

by 1 each time in the loop—this is the default. You can change this behavior by using the

40 Microsoft Access 2010 VBA Macro Programming