Indexes

Cell references can be expressions

You’ve seen code like this:

weight = Cells(3, 2)

Excel takes the value in row 3, column 2, and puts into the variable weight.

3 and 2 are constants. But in fact, you can use numeric expressions for row and column. For example:

column = 2
row = column + 1
weight = Cells(row, column)

row, and column are indexes. Indexes are values that let you look up other values. An index isn’t the value you want, but it lets you find the value you want.

When you have a large data set, it’s impractical to have a variable for each value. But you can access any value you want, by using indexes.

Example: Daily sales

Let’s say we have a spreadsheet of sales data, with one cell each day of the year. Like this:

Sales data

There’s a row for each month. Row 10 is sales for January, row 11 is for February, etc.

There’s a column for each day. Column 1 is for the first day of the month, column 2 for the second day, etc.

Sales for March 9 is in row 12, column 9. Sales for July 12 is in row 16, column 9.

Suppose we want a program that will lookup sales for a given month and day. For example:

Lookup example

Here’s the code:

  1. Private Sub cmdLookup_Click()
  2.     Dim month As Integer
  3.     Dim day As Integer
  4.     Dim sales As Integer
  5.     'Input
  6.     month = Cells(3, 2)
  7.     day = Cells(4, 2)
  8.     'Lookup
  9.     sales = Cells(month + 9, day)
  10.     'Output
  11.     Cells(7, 2) = sales
  12. End Sub
This code…

sales = Cells(month + 9, day)

… doesn’t compute sales, not really. It computes which cell has the sales.

That’s the essence of indexes. They let you find the data you want in a large data set, by using expressions that give the data’s location.

Sales change

Let’s add something. As well as showing sales for the day, the program will show how much the sales changed from the previous day. That is, the day’s sales minus the previous day’s sales.

Sales change

Here’s the code:

  1. Private Sub cmdLookup_Click()
  2.     Dim month As Integer
  3.     Dim day As Integer
  4.     Dim sales As Integer
  5.     Dim prevSales As Integer
  6.     Dim salesChange As Integer
  7.     'Input
  8.     month = Cells(3, 2)
  9.     day = Cells(4, 2)
  10.     'Process
  11.     sales = Cells(month + 9, day)
  12.     prevSales = Cells(month + 9, day - 1)
  13.     salesChange = sales - prevSales
  14.     'Output
  15.     Cells(7, 2) = sales
  16.     Cells(8, 2) = salesChange
  17. End Sub
day is the index of the sales day we want, so day - 1 is the index of the previous day. Doing computations on an index lets us grab related data.

Example: Animal data

More lookup. Here’s some data about animals. Number of legs, niceness (5 to 1), and whether we eat it.

Animal data

The data is for dogs, cats, chickens, and cows. The dog data starts at row 10, the cat data at row 13, etc.

Let’s write a program that looks up data about an animal. Users type the animal’s name, click a button, and the data is shown:

Output

Some notes:

  • Input validation. If the program doesn’t know the animal, it will tell the user that.
  • Input flexibility. The user can type “DOG”, “doG”, “Dog”, “ dog “, and the program will work.
  • Output formatting. The value for “Eat it?” will start with an uppercase character. For example, “Yes”, not “yes”.

Here’s the main program:

  1. Private Sub cmdLookup_Click()
  2.     Dim animal As String
  3.     Dim legs As Integer
  4.     Dim niceness As Integer
  5.     Dim eatIt As String
  6.     Dim dataOK As Boolean
  7.     'Input
  8.     getAnimalType animal, dataOK
  9.     If Not dataOK Then
  10.         End
  11.     End If
  12.     'Processing
  13.     lookupData animal, legs, niceness, eatIt
  14.     'Output
  15.     showData legs, niceness, eatIt
  16. End Sub
getAnimalType returns a flag, showing whether the user gave good data or not.

Here’s the input routine:

  1. 'Get the animal the user wants to know about.
  2. 'Params:
  3. '  animal: name of the animal type, e.g., dog.
  4. '  dataOK: whether that animal type is OK.
  5. Sub getAnimalType(animal As String, dataOK As Boolean)
  6.     'Get user input
  7.     animal = LCase(Trim(Cells(1, 2)))
  8.     dataOK = False
  9.     If animal = "dog" Then
  10.         dataOK = True
  11.     ElseIf animal = "cat" Then
  12.         dataOK = True
  13.     ElseIf animal = "chicken" Then
  14.         dataOK = True
  15.     ElseIf animal = "cow" Then
  16.         dataOK = True
  17.     End If
  18.     If Not dataOK Then
  19.         MsgBox "Sorry, I don't know that animal."
  20.     End If
  21. End Sub
Other programs have initialized the dataOK flag to True, and then flipped it if the code detected an error. This code inits the flag to False, and then flips it if the data is good. Do whatever is more convenient. In this program, only a few data values are OK, so this approach makes sense. But, whatevs, do what makes the most sense to you.

Here’s the processing code:

  1. 'Lookup the characteristics of the animal.
  2. 'Params:
  3. '  animal: name of the animal type, e.g., dog.
  4. '  legs: number of legs that animal has.
  5. '  niceness: how nice the animal is, 5 to 1.
  6. '  eatIt: whether we eat is. yes or no.
  7. Sub lookupData(animal As String, legs As Integer, niceness As Integer, eatIt As String)
  8.     Dim startRow As Integer
  9.     'Compute the location of that animal's data.
  10.     If animal = "dog" Then
  11.         startRow = 10
  12.     ElseIf animal = "cat" Then
  13.         startRow = 13
  14.     ElseIf animal = "chicken" Then
  15.         startRow = 16
  16.     ElseIf animal = "cow" Then
  17.         startRow = 19
  18.     Else
  19.         MsgBox "System error. Unknown animal: " & animal
  20.         End
  21.     End If
  22.     'Extract the data.
  23.     legs = Cells(startRow, 1)
  24.     niceness = Cells(startRow + 1, 1)
  25.     eatIt = Cells(startRow + 2, 1)
  26. End Sub
The data format repeats. It’s always legs, niceness, then eating. The code uses the animal type to figure out which row the data starts in, and computes the individual cell positions from there. Again, it’s using a variable to figure out where the data it needs is stored. Then it grabs the data from that location.

This is what indexes are all about. They tell you where data is.

Finally, the output.

  1. 'Show animal data.
  2. 'Params:
  3. '  animal: name of the animal type, e.g., dog.
  4. '  legs: number of legs that animal has.
  5. '  niceness: how nice the animal is, 5 to 1.
  6. '  eatIt: whether we eat is. yes or no.
  7. Sub showData(legs As Integer, niceness As Integer, eatIt As String)
  8.     Cells(5, 2) = legs
  9.     Cells(6, 2) = niceness
  10.     'Capitalize the first letter.
  11.     eatIt = UCase(Left(eatIt, 1)) & Mid(eatIt, 2)
  12.     Cells(7, 2) = eatIt
  13. End Sub
Look at eatIt, in line 11. Strip off the first character [ Left(eatIt, 1) ]. Make it uppercase [ UCase(Left(eatIt, 1)) ]. Append to it the data starting at the second character [ Mid(eatIt, 2) ].

This program relies on the data for each animal having a regular structure. For every animal, it’s the same three data elements, in the same order. Legs, niceness, eating, legs, niceness, eating, legs, niceness, eating, etc. Repeat for as many animals as there are.

Most business data is structured. For example, each sale has a date, a customer number, a total, etc. Each customer has a first name, last name, address, etc.

Here’s more code that uses indexes.

Example: Tic tac toe

Here’s a program that lets two people play tic tac toe. They type in row, column, and symbol, and the program updates the board.

Tic tac toe

Code:

  1. Private Sub cmdMark_Click()
  2.     Dim row As Integer
  3.     Dim column As Integer
  4.     Dim symbol As String
  5.     'Input
  6.     row = Cells(3, 2)
  7.     column = Cells(4, 2)
  8.     symbol = Cells(5, 2)
  9.     'Place
  10.     Cells(row + 8, column + 2) = symbol
  11. End Sub
Now let’s add code that detects whether the person who just played has won. There are three ways you can win. First, you can complete one of the three rows:

Winner

Winner

Winner

Let’s say the player has just placed an X on a cell. How would you check whether the player had won by completing a row?

  • If the player put an X on row 1, you can check (row 1, cell 1), (row 1, cell 2), and (row 1, cell 3). If they are all Xs, winner!
  • If the player put an X on row 2, you can check (row 2, cell 1), (row 2, cell 2), and (row 2, cell 3). If they are all Xs, winner!
  • If the player put an X on row 3, you can check (row 3, cell 1), (row 3, cell 2), and (row 3, cell 3). If they are all Xs, winner!

Let’s generalize:

  • If the player put an X on row Row, you can check (row Row, cell 1), (row Row, cell 2), and (row Row, cell 3). If they are all Xs, winner!

No matter what row the player used, check all the cells in that row for an X.

What about the other player, using Os? Let’s add a rule:

  • If the player put an X on row Row, you can check (row Row, cell 1), (row Row, cell 2), and (row Row, cell 3). If they are all Xs, winner!
  • If the player put an O on row Row, you can check (row Row, cell 1), (row Row, cell 2), and (row Row, cell 3). If they are all Os, winner!

Let’s generalize:

  • If the player put Symbol on row Row, you can check (row Row, cell 1), (row Row, cell 2), and (row Row, cell 3). If they are all Symbols, winner!

Take the row and the symbol the player used. Check all the cells in that row for that symbol.

“Check for a winner” sounds like a nice chunk of code, so let’s rewrite to this:

  1.     'Place
  2.     Cells(row + 8, column + 2) = symbol
  3.     'Check for winner
  4.     checkWinner row, column, symbol, winner
  5.     If winner Then
  6.         MsgBox "We have a winner!"
  7.     End If
  8. End Sub
  9. Sub checkWinner(row As Integer, column As Integer, _
  10.         symbol As String, winner As Boolean)
  11.     winner = False
  12.     'Check for a row win.
  13.     If Cells(row + 8, 3) = symbol _
  14.       And Cells(row + 8, 4) = symbol _
  15.       And Cells(row + 8, 5) = symbol Then
  16.         winner = True
  17.     End If
  18. End Sub
The If statement implements the rule: Check all the cells in the row where the player just placed a symbol. If all of the cells contain the symbol, winner!

winner is a flag. You saw flags earlier, with input validation. But the flag pattern has many uses.

Pattern

Flag

Situation:
You want to keep track of whether something happened. For example, whether one of several input errors happened.
Actions:
Use a variable as a flag. Write code that initializes it, sets it if some condition occurs, then checks it. See this pattern’s Explanation for an example.

Exercises

Exercise: Teleport price
The cargo teleporter from Earth to Mars operates between 3 am and 11 am every day. Write a program that computes the price of sending cargo at a particular time. The price per kilo changes every hour. Teleports from Earth to Mars have different prices than teleports from Mars to Earth.

Here’s a sample:

Sample

The user enters the time of the transport, kilos, and whether the destination is Earth or Mars. The program computes the price. The PPK (price per kilo) table is shown.

You can assume that users don’t make any typing mistakes.

Hint: Time is entered as hour, then a space, then minutes. Use the InStr function to find the space. The characters to the left of that give you the hour.

Hint

Upload your spreadsheet.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Tic tac toe winner
Download a worksheet with the tic tac toe code. It includes a sub to check whether the move a player just made is a winner. The sub only checks for complete rows. Add code to check for column wins, and diagonal wins.

Upload your Excel file.

Attachments

tic-tac-toe.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

Indexes are values that let you look up other values. An index isn’t the value you want, but it lets you find the value you want.

When you have a large data set, it’s impractical to have a variable for each value. But you can access any value you want, by using indexes.

Most business data has a regular structure. For example, each sale has a date, a customer number, a total, etc. Each customer has a first name, last name, address, etc.