Battleships – The First Excel Game I Built

Disclaimer:

I am not a VBA expert and some portions of this code may not follow the standard coding practices. It is what it is.

Assumptions:

I assume you have some experience with Excel VBA and its infrastructure (how to view project, general knowledge of the coding syntax etc…)

Other Notes:

I don’t plan to explain everything about this game (as it’s just too long). I hope to provide a sequence of events in order to help you replicate it and improve on it further for your interest and knowledge.

The entire game is free to download from this link. The gameplay can be viewed from this link. Please let me know your thoughts and comments on this article below.

 

I always believe the quickest way to learn something is to replicate the process of another person’s work. Hence, I am writing this article.

The code by itself is not too difficult to understand. It essentially consists of a bunch of FOR and WHILE loops with IF statements.

So this game consists of a fair number of sections. I will first try to break it down into the individual components, and then describe each portion as best as I can.

NOTE: The battleship grid area is a 12×12 grid (12 rows & 12 columns).

Player’s Ship – Setup and Initiating Battle

  1. Placement & orientation of Player’s Ship – Ensuring the ships do not exceed the boundaries of the grid/ do not intersect with other ships
  2. Check if all the ships have been placed inside the grid before starting the game.

Computer’s Ship – Setup

 

  1. Placement & orientation of Computer’s Ship – Ensuring the ships do not exceed boundaries of the grid/ do not intersect with other ships.

Gameplay

  1. Check for Hit/ Miss of Player’s Ship
  2. Identifying the next place to hit
  3. Check if the ship has been completely destroyed
  4. Adjusting for unusual hits (to some extent)
 

The final touches;

  1. Check if all the Ships have been destroyed (both PC & Player)
  2. Scoring

Players Ship – Setup and Initiating Battle

Placement & orientation of Player’s Ship

 

This was a fundamental part of the game as I had to determine a method of placing a ship and rotating it within the grid. One method would have been to have 2 versions of each ship. A Vertical Ship and a Horizontal ship. Select the preferred Ship and place it on the grid. The only reason I didn’t use this method was to limit the space on the spreadsheet.

Hence the orientation method was brought in. In order to place a Ship in a particular spot you would need to select an orientation which represents a Ship. Then select a location on the 12×12 grid to place the ship.

 

The orientations are defined as follows;

  • 90 – Right
  • 180 – Bottom
  • 270 – Left
  • 360 – Top
 

This naming convention just felt like something submarine operators would use 🙂

Let’s move to the coding section of the Worksheets (Sheet1)

If Not Intersect(Target, Range("N20")) Is Nothing Then
    Range("AW2") = "Ship1"
    Range("AZ4") = 90
    Range("N20:Q20").ClearContents
    ActiveCell.Value = "X"
ElseIf Not Intersect(Target, Range("O20")) Is Nothing Then
    Range("AW2") = "Ship1"
    Range("AZ4") = 180
    Range("N20:Q20").ClearContents
    ActiveCell.Value = "X"
ElseIf Not Intersect(Target, Range("P20")) Is Nothing Then
    Range("AW2") = "Ship1"
    Range("AZ4") = 270
    Range("N20:Q20").ClearContents
    ActiveCell.Value = "X"
ElseIf Not Intersect(Target, Range("Q20")) Is Nothing Then
    Range("AW2") = "Ship1"
    Range("AZ4") = 360
    Range("N20:Q20").ClearContents
    ActiveCell.Value = "X"

(A)

The first section (A) of this code checks if you have selected a particular orientation (Cell N20 is 90 degrees, O20 is 180 etc…). If an orientation has been selected, the information regarding which Ship was selected and which orientation was selected would be stored in a hidden section of the screen. So what’s happening here is, if you were to select cell N20, it notes that Ship1 was selected, and its selected orientation is 90 degrees.

Hidden Section

NOTE: Once an orientation and ship is noted, if you were to select anywhere outside the 12×12 grid, the ship details would be reset, and you would have to reselect the orientation again. This can be seen from the code below (B).

    Else
        Range("AW2") = 0
        Application.CutCopyMode = False
    End If

(B)

 

Since the initial portion of the program has noted which ship and the selected orientation, If you were to select any cell within the 12×12 grid (E4:P15), a new subroutine would be called (Setup.Placement) (C) .

ElseIf Not Intersect(Target, Range("E4:P15")) Is Nothing Then
    'POSITIONING THE SHIP IN THE BOX E4:P15 BASED ON ORIENTATION (90,180,270,360)
    Call Setup.Placement

(C)

 

Check if Player’s Ship, fits inside the grid area

 

The general idea behind this code is that once you have identified an orientation for your ship, it becomes a matter of where you would like to place it. Hence, this portion of the code checks if there is any intersection with another ship or border based on the orientation.

 

NOTE: In the hidden portion of the screen, I have also noted the Ships Lengths.

  • Ship1 has a length of 7 Cells
  • Ship2 has a length of 5 Cells
  • Ship3 has a length of 4 Cells
  • Ship4 has a length of 3 Cells
  • Ship5 has a length of 2 Cells

Based on this information let’s move to the code;

        If searchrange.Find(Range("AW2")) Is Nothing Then
        Else
            ShipSize = searchrange.Find(Range("AW2")).Offset(0, 1) - 1
            Orientation = searchrange.Find(Range("AW2")).Offset(0, 5)

            If Orientation = "90" Then

                For n = 0 To ShipSize
                    If ActiveCell.Offset(0, n) = "b" Or ActiveCell.Offset(0, n) = "1" Or ActiveCell.Offset(0, n) = "2" _
                    Or ActiveCell.Offset(0, n) = "3" Or ActiveCell.Offset(0, n) = "4" Or ActiveCell.Offset(0, n) = "5" Then
                        MsgBox ("Cannot Place Here")
                        Exit Sub
                    End If
                Next n

                For n = 0 To ShipSize
                    ActiveCell.Offset(0, n) = "1"
                Next n


                searchrange.Find(Range("AW2")).Offset(0, 2) = "X"
                searchrange.Find(Range("AW2")).Offset(0, 3) = ActiveCell.Row
                searchrange.Find(Range("AW2")).Offset(0, 4) = ActiveCell.Column
                searchrange.Find(Range("AW2")).Offset(0, 6) = Orientation
                Range("AW2") = 0

(D)

Section D of the code, searches for the ship in the hidden list, and identifies the ShipSize and Selected Orientation of the Ship (based on A).

If the orientation is 90 (as shown above), A FOR loop is used from 0 to ShipSize to check if the cells to the right of the selected cell is overriding the border or another ship. If that is NOT TRUE, the ship would be placed. Information indicating, the row and column would then be noted for future use. In addition to this, the status would be marked with an X to indicate the Ship has been placed.

If the Ship has been placed, the user has the choice to reposition his ship as seen by the code in Section E

If Range("AW2") = "Ship1" Then
    If Range("AW4") = "X" Then
        answer = MsgBox("Ship 1 has already been added, Do you want to change its position?", vbYesNo)
            If answer = vbYes Then
              Call NewPostion
            Else
                Exit Sub
            End If
        Exit Sub
    Else

(E)

 

Computer’s Ship – Setup

Placement & orientation of Computer’s Ship

 

Now comes the fun bit. Let’s move to the module “NewGame” and setup the Computer’s Ships.

For this, we should visit the subroutine NewGame.RandomNumbers

Random numbers would need to be generated to determine the Row, Column and an orientation for each Ship. Random numbers from 1-12 (F) would be generated for the Row and Column. Another random number from 1 – 4 (G) would be needed to decide on the orientation

Once this is generated, you would then need to determine if the coordinates and orientation would fit inside the Computer’s 12×12 grid area. If the Ship does not fit inside the grid, a new random number would need to be generated

The section of the code involved in checking if the ship can fit inside the grid can be found in the subroutine NewGame.NewGame

I think this portion of the code is self-explanatory. I am simply cycling through each Ship, checking if there are any intersections with another ship or border taking place. If this is NOT TRUE, the ship would be added to the grid. If it is true, the system would generate a new Random Number and do this whole process again.

m = 4
Do Until Range("AU" & m) = ""
    ShipSize = Range("AV" & m) - 1
    If Range("AU" & m) = "Ship1" Then
        ship = "1"
    ElseIf Range("AU" & m) = "Ship2" Then
        ship = "2"
    ElseIf Range("AU" & m) = "Ship3" Then
        ship = "3"
    ElseIf Range("AU" & m) = "Ship4" Then
        ship = "4"
    ElseIf Range("AU" & m) = "Ship5" Then
        ship = "5"
    End If

If Range("BE" & m) = "1" Then
    For n = 0 To ShipSize
        If Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = "b" Or Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = "1" _
        Or Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = "2" Or Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = "3" _
        Or Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = "4" Or Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = "5" Then
            Call RandomNumbers
            n = -1
        End If
    Next n

    For n = 0 To ShipSize
        Cells(Range("BC" & m) + 3, Range("BD" & m) + 23).Offset(0, n) = ship
    Next n

 

Now that the Computer’s ships have been placed, we can finally BATTLE. Let’s move to the “GamePlay” module.

 

 

Game Play

This to me was the most challenging and fun part of the entire coding process. The reason being, once a computer makes, a hit. The computer needs to decide if the Player’s ship orientation is 90, 180, 270 or 360. Another point to consider is if the computer hit’s the Player’s ship in the center, it would then need to reverse the orientation in order to completely destroy the ship. There is also a portion of unusual hits which can occur based on the Player’s Ship placement.

 

This section is fairly large, hence I will first attempt to break down the Computer Logic first.

 

COMPUTER LOGIC

  • First generate a Random Hit  
  • The result of the Random Hit would be: Hit/ Miss
  • If Random Hit makes a hit, then identify orientation of the ship (90,180, 270 or 360) in order for the computer to generate the next hit.
  • Once the orientation is established, computer continues to hit Ship until destroyed
  • If computer is unable to destroy ship, reverse the orientation (If the orientation is 90, reverse orientation to 270) and continue to hit until ship is destroyed.
  • Continue process until all the ships are destroyed.

Sequence of events based on the VBA

 

As this section of the program is fairly long, I don’t plan to display sections of the code as I did previously. Instead I will provide guidelines on how the sequence of events takes place. In order to follow the sequence of code. You can set a breakpoint on the GamePlay module and then step through the code using the F8 key in order to follow the sequence.

 

The following subroutines by the computer begin to take effect during gameplay;

  • RandomShot

This module provides a random shot. The result would either be a Hit/Miss

  • FirstHit

If the result is a hit, the FirstHit subroutine would run. This code determines the orientation in which the ship is placed. This subroutine would continue to run until the orientation is identified.

  • ORIENTATION90/ ORIENTATION180/ ORIENTATION270/ ORIENTATION360

Once an orientation is identified, it would continue to hit the ship in that particular orientation until the ship is destroyed

Unusual Circumstances

These circumstances would occur when the Player’s ship is placed in a similar orientation as shown above. What is happening here is; the computer’s random shot strikes Ship1 first and it has determined Ship1’s orientation to be 180. When it continues to hit Ship1, It unusually strikes Ship2. Ship2’s orientation is then determined and then it continues to destroy Ship2. Once Ship2 is destroyed, it returns back to completely destroy Ship1.

 

The subroutines behind this portion is not the most effective and could be improved on further. The sequence of subroutines is as follows.

  • UnusualHit
  • UNUSUALFirstHit
  • UNUSUALORIENTATION90/ UNUSUALORIENTATION180/ UNUSUALORIENTATION270/ UNUSUALORIENTATION360

The final touches

 
Two Game checks occur during the gameplay.

The first check occurs when the player plays whilst the second check occurs once the PC plays.

GameCheckPC Subroutine

This is a fairly simple check. Once a ship is completely destroyed, the status from the hidden portion of the screen changes to “Completed” indicating the ship has been fully destroyed. Hence I would only need to cycle through each completed status and conclude that the PC has won.

 

GameCheckPlayer Subroutine

 

This check is not as simple as the GameCheckPC subroutine. This cycles through each Ship’s colour and ensures the colour is red in all of them. If there is a grey region, the code exits the sub. Not the most effective method of performing a check as it needs to check each cell for each ship.

 

Once the Game checks have concluded, a point is awarded to the victor!

 

Leave a comment

Your email address will not be published. Required fields are marked *