VBA Programming Series...42

What is structured programming? Does it matter?

If you hang around with programmers, sooner or later you hear the term structured program-ming. This term has been around for decades, and programmers generally agree that struc-tured programs are superior to unstructured programs. So, what is structured programming? And can you do that using 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 block of code should be a stand-alone unit. A program cannot jump into the middle of this unit, nor can it exit at any point except the single exit point. When you write structured code, your program progresses in an orderly manner and is easy to follow — unlike a program that jumps around in a haphazard fashion. This pretty much rules out using the GoTo statement.

In general, a structured program is easier to read and understand. More important, it’s also easier to modify when the need arises.

VBA is indeed a structured language. It offers standard structured constructs such as If-Then-Else, For-Next loops, Do-Until loops, Do-While loops, and Select Case structures. Furthermore, it fully supports module code constructions. If you’re new to programming, you should try to develop good structure-programming habits early on. End of lecture.

How fast are loops?

You might be curious about how fast VBA can run through If-Then loops. Do some systems run code significantly faster than others? As an informal experiment, I posted the following VBA procedure at my blog, and asked others to post their result.

Sub TimeTest()

‘100 million random numbers, tests, and math operations Dim x As Long

Dim StartTime As Single

Dim i As Long

x = 0

StartTime = Timer

For i = 1 To 100000000

If Rnd <= 0.5 Then x = x + 1 Else x = x - 1 Next i

MsgBox Timer - StartTime & “ seconds” End Sub

The code loops 100 million times, and performs some operations inside the loop: It generates a random number, does an If-Then comparison, and performs a mathematical operation. When the loop is finished, the elapsed time is displayed in a message box. My system ran through this loop in 8.03 seconds. About 100 others posted their results, and times ranged from about 5 seconds up to about 30 seconds. In other words, some computers are about six times faster than others. That’s good to know.

But the real question is, how long would it take me to do this task manually? I wrote 0 on a piece of paper, and I flipped a coin. If it came up heads, I added one to my tally. If it came up tails, I subtracted 1. I did this ten times and it took me 42 seconds. So, one time through my “loop” took 4.2 seconds. Using this information, I calculated that it would take me 799 years to perform this task 100 million times — but only if I work non-stop. The conclusion: My computer is about 52.3 million times faster than I am.