Range validation

Where are we?

In the last lesson, we had some data about goats:

Goat data

Here's code that uses a Range to analyze the data.

  • '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

Create the Range object, then loop over all its rows.

Fashion, normalizing, and errors

Cthulhu suspects that happiness, coolness, and fashion are related. It tells its minions to add data on goats' fave fashion houses. Turn out goats like just three:

  • Louis Vuitton (code: lv)
  • Prada (code: pr)
  • Yves Saint Laurent (code: ysl)

The minions sample a new bunch of goats, and come up with data like this:

There's a problem, though. Much of the data was gathered during the Goat Annual Gala (GAG). Some of the goats, the minions, and Cthulhu itself, had been consuming certain substances, that might have impaired their accuracy. Some of the data could be invalid. Words where there should be numbers, fashion houses that don't exist, you get the idea.

Let's add to the data analysis program, so that it can deal.