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.
Enjoy playing Excel Battleship and share any comments below.
No comments:
Post a Comment