Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Conditions and Loops

Exercise 3.01

The aim of this exercise is to play a game of Battleships by testing if the user has selected a cell which contains a "ship".

Click here to download the file needed for this exercise.

Extract and open the Battleships Game.xlsm workbook. Some of the cells in the grid on the worksheet contain letters. The font colour has been set to white so that you can't see them.
Enter a whole number between 1 and 10 in cells L2 and L3 then click the Fire At Target button:

Clicking the Fire At Target button will select the cell in the specified row and column of the worksheet.

In the VBE find the subroutine called Fire_At_Target and add an If statement which tests if the active cell is empty. One way to do this is shown below:

If ActiveCell.Value = "" Then

This is what the user should see if they enter the row and column number of a blank cell.

Add an Else clause to the If statement which colours the active cell in red and displays a message indicating that the user scored a hit:

The worksheet should look like this if the user scores a hit.

Add an If statement at the start of the Fire_At_Target subroutine which displays an error message in cell L6 and exits from the procedure if the user leaves either cell L2 or L3 empty:

Your error message could look like this.

Add an If statement which displays an error message in cell L6 and exits from the procedure if the user enters a value which is not a number into cell L2:

You can use the IsNumeric function to do this:

If Not IsNumeric(Range("L2").Value) Then

Add another If statement which checks that the user has entered a number into cell L3. If not, display an error message in cell L6 and exit from the subroutine.
Add an If statement which checks if the value that the user has entered into cell L2 is less than 1 or greater then 10. If so, display an error message in cell L6 and exit from the subroutine.
Add a similar If statement to check that the value entered into cell L3 is between 1 and 10. Show an error message and exit from the subroutine if not.
Test that the game works as expected then save and close the workbook.