Selecting locations for the computer's ships is a more challenging problem in that the randomly selected locations must be validated using the same rules as for the user's ships. To randomly generate a ship's location, I need two numbers that represent a single cell's row and column index. These two numbers will have to be bound such that the cell falls within the computer's grid. One additional random number (0 or 1) is required to determine the direction (0 = horizontal, 1 = vertical) the ship is placed. The location of a ship is only valid if all of its cells fall within the computer's grid. That is, it is possible for the random numbers to represent a valid cell, but one or more of the remaining cells may fall outside the range or overlap with another ship that was already placed. The program must not proceed to placing the next ship until the current ship is in a valid location; therefore, the process of placing a ship for the computer will require a loop that executes until the location is validated.
Private Sub PlaceComputerShips()
Dim rowIndex As Integer, colIndex As Integer Dim isRow As Boolean
Dim rangeStr As String, compSelection As Range numShipsPlaced = 0
'Loop through the placement of each ship. This loop
'iterates an unknown number of times depending on random numbers.
SetFirstCell rowIndex, colIndex, isRow If isRow Then rangeStr = Chr(colIndex + 64) & rowIndex & ":" & _ Chr(colIndex + 64) & _ (rowIndex + shipSize(numShipsPlaced) - 1)
If (colIndex + shipSize(numShipsPlaced) - 1) < 25 Then rangeStr = Chr(colIndex + 64) & rowIndex & ":" & _
Chr(colIndex + 64 + shipSize(numShipsPlaced) - 1) & _ rowIndex
'Columns after column Z cause problems.
rangeStr = Chr(colIndex + 64) & rowIndex & ":" & "Z" & rowIndex End If End If
Set compSelection = Range(rangeStr) If (AssignShipToLocation(compSelection)) Then _ numShipsPlaced = numShipsPlaced + 1 Loop While (numShipsPlaced < NUMSHIPS) End Sub
The PlaceComputerShips() sub procedure mostly consists of a Do-Loop that iterates until all five of the computer's ships are placed. The loop begins with a call to the SetFirstCell() sub procedure (listed next) which generates the random numbers for the cell's row and column index (rowIndex, colIndex) and the direction of the ship (isRow). Next, the large If/Then/Else code block builds a string in the form of an Excel range (for example, R6:U6). The number of cells represented in this string matches the length of the ship being placed.
The nested If/Then/Else structure is required to handle situations where the range extends past column Z on the worksheet. Excel labels the next column after Z with AA; therefore, the character returned by the Chr() function will not represent a valid column label and the rangeStr variable will not represent a syntactically correct Excel range. This will generate a run-time error when the range variable compSelection is set immediately following the If/Then/Else code block. To avoid this error, the second column reference in the rangeStr variable is set to Z in order to generate a syntactically correct range, albeit an invalid range for the game (the computer's grid ends at column Y).
The location of the computer's ships must be kept hidden from the user. To do this, the program can simply enter a value into the cells representing the location of a ship. The value is not important since the worksheet cells are formatted for the same font color as the background. I will use an X just to make testing the program easier. Later it should be replaced by a space so that the user can't cheat and highlight all cells in the computer's grid in order to see where the X's are. Figure 5.20 shows the Battlecell worksheet (with the computer's grid highlighted) immediately following the random placement of the computer's ships.
The Battlecell worksheet showing the location of the computer's ships.
The Battlecell worksheet showing the location of the computer's ships.
Misses and hits scored by the user against the computer are color coded for visual confirmation and to make it easy to validate new targets.
Finally, just before the Do-Loop ends, a call to the AssignShipToLocation() function procedure tests the selection and marks it with X's if it is valid before the variable numShipsPlaced is incremented by one.
Private Sub SetFirstCell(rIndex As Integer, cIndex As Integer, isRow As Boolean) 'Randomly select a row and column index within the computer's grid 'to place the first cell for a ship.
Dim lowerRow As Integer, upperRow As Integer Dim lowerCol As Integer, upperCol As Integer
'Initialize values for range of random numbers.
lowerRow = cRange.Row upperRow = cRange.Row + cRange.Rows.Count - 1
lowerCol = cRange.Column upperCol = cRange.Column + cRange.Columns.Count - 1
'Generate random numbers for cell location and direction of ship placement.
rIndex = Int((upperRow - lowerRow + 1) * Rnd + lowerRow) cIndex = Int((upperCol - lowerCol + 1) * Rnd + lowerCol) If (Int(2 * Rnd) = 0) Then isRow = True Else: isRow = False End Sub
The SetFirstCell() sub procedure is quite simple and is used to generate the random numbers for the initial cell and direction of the computer's ship. The bounds for the random numbers are set using the Row, Rows, Column, and Columns properties of the Range object. The values are effectively returned to the calling procedure (PlaceComputerShips()) by passing the variables rowIndex, colIndex, isRow by reference. Note that the value for isRow is converted to a Boolean from a random number generated between 0 and 1.
Private Function AssignShipToLocation(compSelection As Range) As Boolean
'Mark ship location if selection is valid.
Dim c As Range
If RangeValid(compSelection, "Computer") Then For Each c In compSelection c.Value = "X"
AssignShipToLocation = True End If End Function
The AssignShipToLocation() function procedure first validates the randomly generated range representing the computer's ship (passed as the range variable compSelection) and marks the cells with an X if the selection is valid. The procedure returns a Boolean value to the PlaceComputerShips() procedure indicating the validity of the range. Note that the same validation procedure is used here (RangeValid()) as was used to validate the user's ship locations.
The computer's target selection is also done randomly. This makes it easy for the user to win the game, but you can add an intelligent targeting algorithm later. Random numbers representing the cell row and column are generated for a target. If the target's background color is white then the computer scores a miss. If it's cyan, the computer scores a hit.
Private Sub ComputerFire()
Dim targetCell As String, targetRange As Range Static numTargetHits As Integer Dim tryAgain As Boolean
'Generate a random target, validate it, then test for hit or miss 'Also test for end of game.
Do targetCell = SetTargetCell
Set targetRange = Range(targetCell)
If TargetValid(targetRange, "Player") Then tryAgain = False
If targetRange.Interior.Color = RGB(0, 255, 255) Then Range("Output").Value = "I hit your ship!" targetRange.Interior.Color = RGB(255, 0, 0) numTargetHits = numTargetHits + 1 If (numTargetHits = 17) Then
Range("Output").Value = "I've sunk all of your ships!" PlayWav ActiveWorkbook.Path & "\Sounds\computerwins.wav" GameOver End If
Range("Output").Value = "I missed!" targetRange.Interior.Color = RGB(0, 255, 0) End If
Else tryAgain = True End If Loop While (tryAgain) End Sub
The ComputerFire() sub procedure is called from PlayerFire() and simulates the computer's return fire at the user's grid. The logic is essentially the same as the PlayerFire() and HitOrMiss() sub procedures listed earlier except that the target is now randomly generated using the SetTargetCell() function procedure. The target is validated using the same sub procedure that validated the user's target selection (TargetValid()).
Private Function SetTargetCell() As String Dim cIndex As Integer, rIndex As Integer Dim lowerRow As Integer, upperRow As Integer Dim lowerCol As Integer, upperCol As Integer
'Use random numbers for selecting a row and columns, 'then convert it to a string in A1 notation.
lowerRow = pRange.Row upperRow = pRange.Row + pRange.Rows.Count - 1
lowerCol = pRange.Column upperCol = pRange.Column + pRange.Columns.Count - 1 Randomize rIndex = Int((upperRow - lowerRow + 1) * Rnd + lowerRow) cIndex = Int((upperCol - lowerCol + 1) * Rnd + lowerCol)
SetTargetCell = Chr(cIndex + 64) & rIndex End Function Public Sub GameOver()
cmdStart.Enabled = True End End Sub
The game ends when either the user or computer scores 17 hits. The Command Button control is enabled and the End keyword is used to terminate the program and clear all variables.
Was this article helpful?