# Processing a Range

Multiple choice

How do you get the number of rows in a `Range`?

A
`rRange.Rows`
B
`rRange.Count.Rows`
C
`rRange.Rows.Count`
D

Ask a nearby goat.

Not graded. So why do it?

Lesson contents

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...)

`Range`s let us do this.

### Averages

Here's the data again from the last lesson.

How would you figure out the average happiness?

Georgina

The total happiness, divided by the number of records.

Good.

So we need total happiness, and the records count.

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

• total = 0
• loop over each record:
•   total = total + happiness
• end loop

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.

Pattern

Loop over a data table. For each record, add a field to a total, count, or whatevs.

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

Ethan

I get the idea of the loop, but how would you get the happiness from each record?

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

• sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

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

If `sCurrentRecordNumber` is 1, then...

• sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

...looks in record 1.

Ray

Isn't that record 3?

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...

• sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

...looks in record 2.

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

• sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

...looks in record 13.

We could access each record's happiness like this:

• rGoatDataTable.Cells(1, HAPPINESS_FIELD)
• rGoatDataTable.Cells(2, HAPPINESS_FIELD)
• rGoatDataTable.Cells(3, HAPPINESS_FIELD)
• ...
• rGoatDataTable.Cells(13, HAPPINESS_FIELD)

Then we could total them up:

• sTotalHappiness = 0
• sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(1, HAPPINESS_FIELD)
• sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(2, HAPPINESS_FIELD)
• sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(3, HAPPINESS_FIELD)
• ...
• sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(13, HAPPINESS_FIELD)

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:

1. sTotalHappiness = 0
2. Run the next line for sCurrentRecordNumber 1, 2, 3, ... 13
3.   sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

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:

• sGoatHappiness = rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

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

If there are 8,192 records?

1. sTotalHappiness = 0
2. Run the next line for sCurrentRecordNumber 1, 2, 3, ... 8192
3.   sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)

Line 3 runs 8,192 times.

Ray

Wouldn't that program take a long time to work out the total? 8,192 is a lot.

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:

• For variable = start value To end value
•     Some code
• Next variable

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:

• For sIndex = 1 To 4
•     MsgBox "sIndex is " & sIndex
• Next sIndex

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

Click the button to try it.

Another example:

• For sToe = 1 To 5
•     MsgBox "I've got toe number " & sToe & "!"
• Next sToe

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.

• sLimit = InputBox("What's the number you want to add up to?")
• sTotal = 0
• For sValue = 1 To sLimit
•     sTotal = sTotal + sValue
• Next sValue
• MsgBox "The sum of the whole numbers between 1 and " _
•     & sLimit & " is " & sTotal & "."
Reflect

Suppose someone types in 4 for the input. What will the total be?

If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.

Ethan

It will be 1 + 2 + 3 + 4. That's 10.

You can try it, and see.

Try it for different values.

OK, I know that VBA can't run on webpages. How are you getting the button to run code on a webpage?

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.

Reminder

Don't just run your eyes over the code. Run through it with your head meat. Think about what each line does.

Yes, it takes effort, but you'll get learning value from the time you spend reading this lesson.

​OK, OK. I'll pay attention.

• 'Consts for field numbers.
• Const NAME_FIELD = 1
• Const HAPPINESS_FIELD = 2
• Const COOLNESS_FIELD = 3
•
• Sub averages()
•     Dim rGoatDataTable As Range
•     Dim sTotalHappiness As Single
•     Dim sTotalCoolness As Single
•     Dim sNumberOfRecordsInRange As Single
•     Dim sCurrentRecordNumber As Single
•     Dim sAverageHappiness As Single
•     Dim sAverageCoolness As Single
•     Dim tOutputMessage As String
•
•     'Set up the Range object.
•     Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))
•     'Get the number of rows in the range. We use one row for each record.
•     sNumberOfRecordsInRange = rGoatDataTable.Rows.Count
•     'Initialize totals.
•     sTotalHappiness = 0
•     sTotalCoolness = 0
•
•     'Compute totals
•     For sCurrentRecordNumber = 1 To sNumberOfRecordsInRange
•         sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)
•         sTotalCoolness = sTotalCoolness + rGoatDataTable.Cells(sCurrentRecordNumber, COOLNESS_FIELD)
•     Next sCurrentRecordNumber
•     sAverageHappiness = sTotalHappiness / sNumberOfRecordsInRange
•     sAverageCoolness = sTotalCoolness / sNumberOfRecordsInRange
•
•     'Output.
•     tOutputMessage = "Average happiness: " & sAverageHappiness & vbCrLf _
•         & "Average coolness: " & sAverageCoolness
•     MsgBox tOutputMessage
• End Sub

Let's focus on this bit:

• For sCurrentRecordNumber = 1 To sNumberOfRecordsInRange
•     sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)
•     sTotalCoolness = sTotalCoolness + rGoatDataTable.Cells(sCurrentRecordNumber, COOLNESS_FIELD)
• Next sNumberOfRecordsInRange

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

• For sCurrentRecordNumber = 1 To sNumberOfRecordsInRange
•     sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)
•     sTotalCoolness = sTotalCoolness + rGoatDataTable.Cells(sCurrentRecordNumber, COOLNESS_FIELD)
• Next sCurrentRecordNumber

... runs this...

•     sTotalHappiness = sTotalHappiness + rGoatDataTable.Cells(sCurrentRecordNumber, HAPPINESS_FIELD)
•     sTotalCoolness = sTotalCoolness + rGoatDataTable.Cells(sCurrentRecordNumber, COOLNESS_FIELD)

... 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:

• sNumberOfRecordsInRange = rGoatDataTable.Rows.Count

So:

• sAverageHappiness = sTotalHappiness / sNumberOfRecordsInRange
• sAverageCoolness = sTotalCoolness / sNumberOfRecordsInRange

M0000t!

Pattern

Loop over a data table. For each record, add a field to a total, count, or whatevs.

### Exercise

Exercise

Taste test stats

Some goats have taken a taste test, saying how much they like three different leafs. Write a VBA program to compute some basic statistics on the data, using a `For` loop. Download a start worksheet that has the data.

It starts like this:

The clicks the button, and your code runs. For the data you're given, it shows:

Write the code so that the number of records could change, as more data is gathered. That is, don't hard-wire your code so that it only works for 50 goats. So, if data for 20 more goats was added, your program would accept record numbers up to 70, without any of your code changing.

Upload your workbook. The usual coding standards apply.