Processing a Range

Multiple choice

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

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

Ask a nearby goat.

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:

Start

When you're processing the next record...

The next record

Then...

The next record

Then...

The next record

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.

Data

How would you figure out the average happiness?

Georgina
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

Accumulator

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.

Marcus
Marcus

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.

Record 1

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

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.

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

Data

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

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

You can try it, and see.

Try it for different values.

Adela
Adela

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.

Goat

​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

Accumulator (Range)

Use a For loop to run through a range, adding (or counting) as you go.

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:

Start

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

Output

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.

Up next

Now that you know how to process Ranges, let's use the same ideas to process CSV files.