Processing a Range

Let's combine the last two lessons, and see how we can process the data in a Range.

A record, another record, another...

This is from before.

- - FLASHBACK BEGINS - -

(Cue music: doodly do, doodly do, doodly do...)

Imagine we made a variable called, say, sCurrentRecordNumber. It holds..., er, the current record number. It would start at one:

When you're processing the next record...

Then...

Then...

sCurrentRecordNumber points to the record being processed.

- - FLASHBACK ENDS - -

(Cue music: doodly do, doodly do, doodly do...)

Ranges let us do this.

Averages

Here's the data again from the last lesson.

How would you figure out the average happiness?

Good.

So we need total happiness, and the records count.

To compute the total, we can use the accumulator pattern. It goes like this.

This isn't VBA. It's pseudocode. Just a made-up description of what the actual code will do. Helps plan the program, without worrying about of the programmy deets.

The line total = total + happiness is run over and over. Each time, the happiness for one record gets added in.

Think back to the last lesson. This is how we got happiness for a record.

sCurrentRecordNumber is a variable, telling VBA which record of rGoatDataTable to look in.

If sCurrentRecordNumber is 1, then...

...looks in record 1.

No, that's row 3 you're seeing. The data for record 1 starts in row 3, because the Range starts in row 3.

An easy mistake to make. In fact, I made that mistake, when I was writing one of the sample programs for these lessons.

Anyway, back to how you access a record's happiness.

If sCurrentRecordNumber is 2, then...

...looks in record 2.

And so on, until we get to the end of the range. At that point, sCurrentRecordNumber is 13, so...

...looks in record 13.

We could access each record's happiness like this:

Then we could total them up:

Not too bad when there are 13 records, but what if there are 128? Or 8,192? ACK!

That's where our loop comes in. Here's another pseudocode version:

The loop zips around 13 times. Line 3 does something different each time through the loop, because it depends on the value of a variable that changes each time through the loop:

The value of sCurrentRecordNumber keeps changing, so what the code does keeps changing.

If there are 8,192 records?

Line 3 runs 8,192 times.

Good question. 8,192 is a lot, to a human. To a computer, it's nothing. Adding up 8,192 would take less than a second, even on an old PC.

The For loop

Here's the data again.

The VBA code for this will use the For statement. It has the form:

This runs Some code a bunch of times. The first time, variable is start value. The second time, variable is start value + 1. Next, variable is start value + 2. And so on. The last time, variable is end value.

Here's an example:

This will run for sIndex is 1, 2, 3, and 4.

Click the button to try it.

Run

Another example:

Run

You can compute with the looping variable. Let's say we want to add up all the whole numbers between 1 and some other number. So if the other number was 9, it would add up 1+2+3+4+5+6+7+8+9. This code will do it.

You can try it, and see.

Run

Try it for different values.

You're right, browsers can't run VBA, but they can run another language called JavaScript. You can read about the similarities between the languages.

Here's VBA code that will compute average happiness, and coolness.

​OK, OK. I'll pay attention.

Let's focus on this bit:

VBA runs the code between For and Next a bunch of times. So...

... runs this...

... for:

• sCurrentRecordNumber = 1
• sCurrentRecordNumber = 2
• sCurrentRecordNumber = 3
• ...
• sCurrentRecordNumber = sNumberOfRecordsInRange

When it's done, sTotalHappiness and sTotalCoolness will have the values we need.

The rest is easy enough. Average is total / count. Count, the number of records, we already know:

So:

M0000t!

Exercise