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:

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:

Here's the code:

1. Private Sub cmdLookup_Click()
2.     Dim month As Integer
3.     Dim day As Integer
4.     Dim sales As Integer
6.     'Input
7.     month = Cells(3, 2)
8.     day = Cells(4, 2)
10.     'Lookup
11.     sales = Cells(month + 9, day)
13.     'Output
14.     Cells(7, 2) = sales
15. 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.

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
8.     'Input
9.     month = Cells(3, 2)
10.     day = Cells(4, 2)
12.     'Process
13.     sales = Cells(month + 9, day)
14.     prevSales = Cells(month + 9, day - 1)
15.     salesChange = sales - prevSales
17.     'Output
18.     Cells(7, 2) = sales
19.     Cells(8, 2) = salesChange
20. 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.

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:

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
8.     'Input
9.     getAnimalType animal, dataOK
10.     If Not dataOK Then
11.         End
12.     End If
14.     'Processing
15.     lookupData animal, legs, niceness, eatIt
17.     'Output
18.     showData legs, niceness, eatIt
19. 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
10.     'Compute the location of that animal's data.
11.     If animal = "dog" Then
12.         startRow = 10
13.     ElseIf animal = "cat" Then
14.         startRow = 13
15.     ElseIf animal = "chicken" Then
16.         startRow = 16
17.     ElseIf animal = "cow" Then
18.         startRow = 19
19.     Else
20.         MsgBox "System error. Unknown animal: " & animal
21.         End
22.     End If
24.     'Extract the data.
25.     legs = Cells(startRow, 1)
26.     niceness = Cells(startRow + 1, 1)
27.     eatIt = Cells(startRow + 2, 1)
28. 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.

Code:

1. Private Sub cmdMark_Click()
2.     Dim row As Integer
3.     Dim column As Integer
4.     Dim symbol As String
6.     'Input
7.     row = Cells(3, 2)
8.     column = Cells(4, 2)
9.     symbol = Cells(5, 2)
11.     'Place
12.     Cells(row + 8, column + 2) = symbol
13. 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:

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
4.     'Check for winner
5.     checkWinner row, column, symbol, winner
6.     If winner Then
7.         MsgBox "We have a winner!"
8.     End If
10. End Sub
11. Sub checkWinner(row As Integer, column As Integer, _
12.         symbol As String, winner As Boolean)
13.     winner = False
14.     'Check for a row win.
15.     If Cells(row + 8, 3) = symbol _
16.       And Cells(row + 8, 4) = symbol _
17.       And Cells(row + 8, 5) = symbol Then
18.         winner = True
19.     End If
20. 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.

More…

Exercises

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.