Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
What is structured programming?
Hang around with programmers, and sooner or later you’ll hear the term structured programming.
You’ll also discover that structured programs are considered superior to unstructured programs.
So what is structured programming? And can you do it with VBA?
The basic premise of structured programming is that a routine or code segment should have
only one entry point and one exit point. In other words, a body of code should be a stand-alone
unit, and program control should not jump into or exit from the middle of this unit. As a result,
structured programming rules out the GoTo statement. When you write structured code, your
program progresses in an orderly manner and is easy to follow — as opposed to spaghetti code,
in which a program jumps around.
A structured program is easier to read and understand than an unstructured one. More
important, it’s also easier to modify.
VBA is a structured language. It offers standard structured constructs, such as If-Then-Else
and Select Case and the For-Next , Do Until , and Do While loops. Furthermore, VBA
fully supports modular code construction.
If you’re new to programming, forming good structured-programming habits early is a good idea.
In this example, Count (the loop counter variable) starts out as 1 and increases by 1 each time
the loop repeats. The Sum variable simply accumulates the square roots of each value of Count .
When you use For-Next loops, it’s important to understand that the loop counter is a
normal variable — nothing special. As a result, it’s possible to change the value of the
loop counter within the block of code executed between the For and Next statements.
Changing the loop counter inside of a loop, however, is a bad practice and can cause
unpredictable results. In fact, you should take precautions to ensure that your code
doesn’t change the loop counter.
You can also use a Step value to skip some values in the loop. Here’s the same procedure
rewritten to sum the square roots of the odd numbers between 1 and 100:
Dim Sum As Double
Dim Count As Integer
Sum = 0
For Count = 1 To 100 Step 2
Sum = Sum + Sqr(Count)