Excel Battleship Game

This post shows how to create the Sink the Fleet Battleship game in Excel. It is a good example to work with VBA formatting, loops and conditions, having an interesting logic for ships placement. Play Excel Battleship against your computer and beat the code if you can!

The Layout

The “ClearLayout” subroutine consists of several blocks of With / End With statements to clear the board and set key formatting for Excel Battleship. The “SolutionLayout” subroutine clears the “Solution” sheet layout prior to randomly placing ships for Player B (Computer). Please note this sheet is hidden.

The table in the lower right corner shows your Excel Battleship fleet, the length of each ship, total available and placed units. “ClearLayout” will also reset those numbers. Above, there are two command buttons to start a new game and to place your ships upon selection of a cell or range.

The Placement

Placing ships in Excel Battleship will resume upon correct selection in Player A’s board. The following variables are used to capture the selection prior to placing the ship:

 

  r = Selection.Row

  c = Selection.Column

  rn = Selection.Rows.Count

  cn = Selection.Columns.Count

 

There is a meticulous set of conditions to ensure ships are placed correctly in the “PlacePlayerA” subroutine under the “Placement” module in Excel Battleship. The length unit value (“lu” variable) is added to the cells and the cell interior color is changed. Then, a shape representing the ship is added to the selection by assigning the class module “clsShip” as follows:

 

  Set myShip = New clsShip

  myShip.AddShip

 

Within the AddShip subroutine the coordinates of the shape are calculated based on the selected range and then the rounded rectangle shape is added (see below).

 

  Set ws = Sheets("Battleship")

  Set newPlace = ws.Range(Cells(r, c), Cells(r + rn - 1, c + cn - 1))

  x = newPlace.Left + 2

  y = newPlace.Top + 2

  w = newPlace.Width - 4

  q = newPlace.Height - 4

  Set newShip = ws.Shapes.AddShape(msoShapeRoundedRectangle, x, y, w, q)

 

In order to show the shape we need to add the following properties:

 

  With newShip

       With .Fill

              .Solid

              .ForeColor.RGB = RGB(0, 0, 0)

              .Transparency = 0.25

       End With

  End With

 

And this will make the border of the rectangle rounded by 10 degrees:

 

  newShip.Adjustments(1) = newShip.Adjustments(1) * 10

 

The Movement

Excel Battleship uses a VBA Event to trigger the “MoveA” subroutine upon clicking on any cell within the Player’s B display. That’s the SelectionChange Event of the Worksheet Object, which is written within the given worksheet VBA window, in this case, Sheet1 (Battleship).

The “MoveA” subroutine captures active row and column numbers with ActiveCell.Row and ActiveCell.Column respectively, and shows a message box for the player to confirm the selected coordinates in Excel Battleship. Then it goes through a number of IF statements to check if ship was hit by looking at the SolutionWS worksheet.

Movement of Player B (computer) in Excel Battleship occurs through random coordinates using the Randomize/Rnd functions and when hitting a ship will follow a more complicated logic to track ships’s position and length in order to sink the fleet.

Enjoy playing Excel Battleship and share any comments below.

 

 

Download Excel Battleship