Solution - Experienced Challenge 10: Don’t Get Ten of Those Little Red Cells
You know, if we were a little smarter we would have made Challenge 10 an easy one; after 2 solid weeks of copying Visual Basic for Applications (VBA) subroutines from emails, pasting those subroutines into a Microsoft Office application, and then running that subroutine to make sure it actually worked, well, we definitely could have used a break here. But did we take the easy way out? Heck no; instead, Challenge 10 was the most difficult one to test, if for no other reason than the fact that it required at least two subroutines, subroutines which had to be stashed in two totally different places. (One, the NewGame subroutine, had to go in Module1; the other subroutine had to be attached to Sheet1.) What were we thinking?
Note. Well, obviously we weren’t thinking . After all, if we’d done even a little thinking we never would have agreed to do OfficePalooza in our spare time, something which meant we’d work all day at our real jobs and then go home and work all night on OfficePalooza. But, then again, OfficePalooza was a lot of fun, and we enjoyed seeing the different ways in which people approached the challenges. Maybe it’s a good thing that we don’t do much thinking after all! Something to think about, eh?
We also made our lives a bit more complicated by deciding to solve Challenge 10 without using global variables. Admittedly, we could have used global variables; there’s nothing in the instructions that say otherwise, and we happily accepted all the submissions that took that approach. However, global variables meant yet another piece of code that had to be placed in a specific spot in the workbook; that complicates our testing process. Had we been thinking, we would have said, “Do not use global variables in your submission.” But – well, you already know how we are when it comes to thinking.
Note. On top of that, we had a bad habit of posting the challenge and only then trying to determine if the challenge could actually be solved. We couldn’t rule out the use of global variables simply because, at the time the challenge was unveiled, we didn’t know for sure if you could solve the problem without using global variables. Wouldn’t it have been better to think about that before we posted the challenge?
You already know the answer to that question.
At any rate, global variables or no global variables, we eventually came up with a pair of subroutines that could play a rudimentary game of Hangman. And just what are those subroutines? Well, here’s one of them, the NewGame subroutine (the one we put in Module1):
Sub NewGame()
Sheet1.Unprotect
Range("B5:N6").Cells.Interior.ColorIndex = 2
Range("S5:S14").Cells.Interior.ColorIndex = 2
Range("B8:N8").Cells.Interior.ColorIndex = 2
Range("B8:N8").Cells.Value = ""
Do While True
intWordRow = Int((Rnd * 32154) + 1)
strNewWord = Sheet2.Cells(intWordRow, 1).Value
intLength = Len(strNewWord)
If intLength > 6 And intLength < 14 Then
For i = 1 To intLength
Application.Cells(8, i + 1).Interior.ColorIndex = 6
Application.Cells(8, i + 1).Font.ColorIndex = 6
Application.Cells(8, i + 1).Value = UCase(Mid(strNewWord, i, 1))
Next
Sheet1.Protect
Exit Do
End If
Loop
End Sub
The NewGame subroutine, as you can probably guess, is all about getting the worksheet ready for a new game. In order to play the game, you need to click on the list of available letters (cells B5 through N6). When you click on a letter, that particular cell should turn gray (indicating the letter has been chosen) and the value of that cell needs to be immediately available for use. The only way we can do that (well, that we know of anyway) is to protect the worksheet; if the worksheet is unprotected then clicking on a cell will simply select that cell. That’s not what we want. In addition to that, by protecting the worksheet we can keep people from clicking where they shouldn’t. (For example, people can’t select and delete any cells on the worksheet, something which could definitely wreak a little havoc on game play.)
OK, so if we need to have the worksheet protected then why does our very first line of code call the Unprotect method? Good question. In order to set up a new game we need to make some changes to the worksheet; for example, we need to reset the background colors of some of our cells. We can’t do that if the worksheet is protected; when that’s the case you can’t make any changes to the worksheet, even using VBA. The only way we can make changes is to unprotect the sheet. But don’t worry: the change in protection status will only be temporary.
Next we execute the following block of code:
Range("B5:N6").Cells.Interior.ColorIndex = 2
Range("S5:S14").Cells.Interior.ColorIndex = 2
Range("B8:N8").Cells.Interior.ColorIndex = 2
Range("B8:N8").Cells.Value = ""
All we’re doing here (for the most part) is resetting the background colors of selected cells on the worksheet. For example, cells B5 through N6 are the letters you can use while playing the game; cells S5 through S14 represent the “scoreboard,” the portion of the game where we keep track of your incorrect guesses. In both cases, we set the ColorIndex property of each cell in the range to 2 (which just happens to be white). We also do the same thing with cells B8 through N8, the place where our “secret word” is displayed. In addition to changing the background color of these cells we also set the Value of each cell to an empty string; that simply clears the cell so that we can enter a new letter in there.
At this point we’re ready to select a new secret word. To carry out that chore, the first thing we do is set up a Do loop designed to run forever (or at least for as long as True is equal to True). Why do we need a loop here? Well, we put a couple stipulations on our word selection; in particular, we said that a word must have at least 7 letters but no more than 13 letters. We need this loop just in case, the first time around, we randomly choose a word that only has, say, 5 letters.
Note. As it turns out, our list of words – which we downloaded off the Internet – includes some two-word “words” (words separated by a blank space), plus words that have hyphens or other non-letter characters in them. Ideally, we would have written code that tossed out any words that included anything other than the letters A through Z; in the interest of keeping this subroutine short and sweet we opted not to do that. At least some of you did include code that guarded against selecting a secret word that included a blank space or a hyphen. All we can say is this: good for you!
Inside the loop we first call the Rnd function to select a random number between 1 and 32154. (Why 32154? Because we have that many words listed on Sheet2 of the workbook.) This line of code selects a value between 1 and 32154, then uses the Int function to convert that value to an integer:
intWordRow = Int((Rnd * 32154) + 1)
The selected value actually represents a row on Sheet2 of our workbook. Now that we have a row number we can use this line of code to grab the value of the word listed on row intWordRow, column 1:
strNewWord = Sheet2.Cells(intWordRow, 1).Value
After we’ve selected a word we can next use the Len function to determine the total number of characters in that word. Once that’s done we use this line of code to verify that the word has at least 7 (but no more than 13) characters in it:
If intLength > 6 And intLength < 14 Then
If the secret word doesn’t meet these criteria we simply pop back to the top of the loop and try again. If the secret word does meet these criteria then we execute the following block of code:
For i = 1 To intLength
Application.Cells(8, i + 1).Interior.ColorIndex = 6
Application.Cells(8, i + 1).Font.ColorIndex = 6
Application.Cells(8, i + 1).Value = UCase(Mid(strNewWord, i, 1))
Next
All we’ve done here is set up a loop that runs from 1 (representing the first character in the secret word) to the length of that secret word (representing the last character in the word.) Inside the loop we start with the first cell in the range B8 through N8; as you no doubt figured out, cell row 8, column 2 (i + 1) is the same thing as cell B8. For this first cell we set the background color of the cell to yellow (6); we also set the Font color of the cell to yellow. Why do we bother with the font color? Well, as we noted earlier, we wanted to be able to play this game without needing to use global variables. One simple way of doing this (as some of you discovered) is to make the font color the same color as the background color of the cell. When the two colors are identical, we can then enter the correct letter in the cell; no one will be able to see it, but it will be there. This line of code (the first time through the loop) simply takes the first letter in our secret word and types it into cell B8.
We then loop around and repeat the process with the next cell in the range B8 through N8 and the next letter in the secret word.
And once that’s done we simply call the Protect method to reprotect our worksheet, then call Exit Do to exit the NewGame subroutine:
Sheet1.Protect
Exit Do
All things considered, that wasn’t too terribly bad, was it?
Next we need to create a single subroutine for actually playing the game. The best way to do that? Well, we’re not exactly sure if this is the best way to do that. But what worked for us was to add the following subroutine to Sheet1 in the workbook:
Public Sub Worksheet_SelectionChange(ByVal objTarget As Range)
Set objRange = Range(objTarget.Address)
Set objRange2 = Range("B5:N6")
Set isect = Application.Intersect(objRange2, objRange)
If Not isect Is Nothing Then
Sheet1.Unprotect
objTarget.Cells.Interior.ColorIndex = 16
x = 0
For Each objCell In Range("B8:N8").Cells
If objCell.Value = objTarget.Cells.Value Then
objCell.Font.ColorIndex = 1
objCell.Interior.ColorIndex = 2
x = 1
End If
Next
If x = 0 Then
For Each objCell In Range("S5:S14").Cells
If objCell.Interior.ColorIndex <> 3 Then
objCell.Interior.ColorIndex = 3
Exit For
End If
Next
End If
Sheet1.Protect
End If
If Application.Cells(14, 19).Interior.ColorIndex = 3 Then
strValue = ""
For Each objCell In Range("B8:N8").Cells
strValue = strValue & objCell.Value
Next
MsgBox "Sorry; you lose. The word was " & strValue & "."
Sheet1.Protect
Exit Sub
End If
intWordLength = 0
For Each objCell In Range("B8:N8").Cells
If objCell.Interior.ColorIndex = 6 Then
intWordLength = intWordLength + 1
End If
Next
If intWordLength = 0 Then
MsgBox "Congratulations; you win!"
Sheet1.Protect
Exit Sub
End If
End Sub
As you can see, this subroutine is designed to respond any time the selection changes on the worksheet (hence the reference to the Worksheet_SelectionChange event). Any time a player clicks a cell on Sheet1 (any cell on Sheet1) the SelectionChange event will be triggered, and this subroutine will run.
So what will this subroutine do when it does run? Well, for starters, it creates a pair of Range objects:
Set objRange = Range(objTarget.Address)
Set objRange2 = Range("B5:N6")
In the first line of code, we create a Range object that represents the cell that the player clicked; that’s what the objTarget.Address is for. And in line 2? In that line we create a range object that represents the collection of available letters (that is, cells B5 through N6).
That brings us to this line of code:
Set isect = Application.Intersect(objRange2, objRange)
Here we’re using the Intersect method to see if the cell that the user clicked intersects with the collection of available letters; in other words, did we click on a cell located in the range B5 through N6? If we did, then the object reference isect will be a valid object. And if we didn’t click on a cell located in range B5 through N6? In that case, isect will not point to a valid object.
Which explains why we next execute this line of code:
If Not isect Is Nothing Then
What we’re doing here is checking to make sure isect represents a valid object. If we don’t have a valid object (that is, if isect is equal to Nothing) then we simply exit the subroutine and wait for the player to click a new cell on the spreadsheet. And if we have a valid object? Well, in that case the first thing we do is run these two lines of code:
Sheet1.Unprotect
objTarget.Cells.Interior.ColorIndex = 16
In line 1 we, again, unprotect our worksheet. (Why? Because we need to make some changes to that worksheet.) We then set the ColorIndex of the selected cell to a dark gray (16); that way the player will be able to tell, at a glance, that he or she has already selected that particular letter.
After setting the value of the counter variable x to 0 (more on that in a minute) we execute this block of code:
For Each objCell In Range("B8:N8").Cells
If objCell.Value = objTarget.Cells.Value Then
objCell.Font.ColorIndex = 1
objCell.Interior.ColorIndex = 2
x = 1
End If
Next
Admittedly, this is a tiny bit clunky; that’s because (we we mentioned earlier) we wanted to come up with a solution that required just two subroutines and no global variables. What we’re doing here is simply checking to see if the selected letter appears anywhere in the secret word. There are more elegant ways to do this; most of those ways would require the use of a global variable, however. Consequently, we do this the old-fashioned, brute force way: by setting up a For Each loop that checks each cell in the range B8 through N8 to see if the Value of any of those cells matches the value of the selected cell (that is, the letter that the player clicked on). This line of code checks to see if the values match:
If objCell.Value = objTarget.Cells.Value Then
If the values don’t match then we don’t do anything; if the values do match we run these 3 lines of code:
objCell.Font.ColorIndex = 1
objCell.Interior.ColorIndex = 2
x = 1
What are we doing here? Well, to begin with, we’re setting the font color for the cell to black (1); that’s going to allow the hidden value to “magically” appear on screen. After changing the font color we then change the background color of the cell to white; we do that so the player knows that he or she has correctly identified the letter that goes in that space. Finally, we set the value of x to 1; that helps us keep track of the fact that the selected letter does appear somewhere in the secret word.
Got all that? Good. Then here’s what we do next:
If x = 0 Then
For Each objCell In Range("S5:S14").Cells
If objCell.Interior.ColorIndex <> 3 Then
objCell.Interior.ColorIndex = 3
Exit For
End If
Next
End If
Remember how we set the value of x to 1 if the selected letter appears somewhere in the secret word? Well, if the letter doesn’t appear anywhere in the word that means that x will still be equal to 0 (and that the user made a wrong guess). In that case we need to change one of the cells in our scoreboard to red.
Ah, yet another good question: which cell do we need to change to red? To tell you the truth, we don’t know; this is another case where a global variable would come in handy. But even without a global variable we can still figure out which cell needs to be updated. How? By looping through the cells in range S5 through S14 and checking to see if the background color is red. If it is, then we go back to the top of the loop and try again with the next cell in the range. If it isn’t? Then we use this line of code to change the background color to red:
objCell.Interior.ColorIndex = 3
Like we said, not particularly elegant. But it works.
OK, so the user has selected a letter, we’ve determined whether or not that letter can be found in the secret word, and, if need be, we’ve updated our scoreboard. Now what do we do?
Well, first we need to check to see if the player has lost the game. How can we determine if the player has lost the game? Here’s one way. If the player has made 10 incorrect guesses then cell S14 will have a background color of red. We can check the background color of that cell using this line of code:
If Application.Cells(14, 19).Interior.ColorIndex = 3 Then
If the color is red then we run this block of code:
strValue = ""
For Each objCell In Range("B8:N8").Cells
strValue = strValue & objCell.Value
Next
MsgBox "Sorry; you lose. The word was " & strValue & "."
Sheet1.Protect
Exit Sub
In the first four lines we simply loop through the values in cells B8 through N8 and use the letters found in each cell to reconstruct our secret word. (Needless to say, if we were using global variables we wouldn’t have to do this.) After the secret word has been stashed in the variable strValue we use this line of code to echo back the fact that the player has lost and, just to be nice, let him or her know what the secret word actually was:
MsgBox "Sorry; you lose. The word was " & strValue & "."
We then reprotect the worksheet and exit the subroutine.
But suppose the player hasn’t lost; does that mean the he or she has won? To be honest, we don’t know; however, we can figure that out by looping through the cells in range B8 through N8 and checking to see if the background color of any of those cells is yellow:
intWordLength = 0
For Each objCell In Range("B8:N8").Cells
If objCell.Interior.ColorIndex = 6 Then
intWordLength = intWordLength + 1
End If
Next
If we happen to encounter a yellow cell we increment the value of the counter variable intWordLength by 1. When we’re done we’ll check the value of intWordLength. If intWordLength is equal to 0 that means we didn’t find any yellow cells; in turn, that means that the player has won. If intWordLength is anything but 0 that means that the player hasn’t guessed all the letters correctly and the game is still on. In that case, we don’t do much of anything except wait for the player to select another letter.
If our player has won then we congratulate them, reprotect the spreadsheet, and exit the subroutine; that’s what this hunk of code is for:
MsgBox "Congratulations; you win!"
Sheet1.Protect
Exit Sub
And, believe it or not, that’s all we have to do. Game over!
Or so we think.