The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a version of the system which allows you to add reviews of films to a list:

Clicking the button adds the data in column B to the table starting in column D.

In the VBE you'll find a subroutine called Add_To_List which is called when you click the button on the worksheet:

The subroutine uses multiple If statements to check that the data is valid before allowing it to be added to the list.

We'd like to replace the individual If statements with a set of functions which will determine if the data is valid.

Creating a New Function

For the first example, we'll create a function which checks if a value has been entered in a particular cell.  Start by creating a function called Value_Missing in Module1:

Create a simple function outline as shown here.

The function will need to know which cell to check the value of.  Define a Range parameter called CellToTest:

Define a parameter as shown here.

We'd like the function to return True or False depending on whether the cell is empty or not.  Set the return type of the function to Boolean:

Set the return type to Boolean.

We'd like the function to return True if the cell is empty and False if not.  Write an If statement which checks if the value of the CellToTest is an empty string and returns the correct response:

Construct an If statement to return the correct response.

Using the Result of the Function

We can already use the Value_Missing function to validate the film details by calling it from the Add_To_List subroutine.  Find and select the code shown highlighted below:

Find and select the If statement shown here in the Add_To_List subroutine.

Replace the highlighted code with an If statement which calls the Value_Missing function:

Call the Value_Missing function and open a set of parentheses.

Pass a reference to cell B2 into the CellToTest parameter of the function:

Refer to the cell which should contain the film title and then close the parentheses.

Complete the If statement by telling the subroutine to exit if the condition returns True:

You can write the entire If statement on a single line as shown here.

You can now return to Excel and test that the code works.  Delete the value in cell B2 and click the button on the worksheet:

If cell B2 doesn't contain a value the subroutine will end without copying any data.

Reusing the Function

We can reuse the Value_Missing function to test if the user has entered a value into cells B3 and B4.  Return to the VBE and add two more calls to the Value_Missing function below the first:

You can check that the user has filled in each of the film details by adding more If statements to the Add_To_List subroutine.

You can test that the code works by returning to Excel and deleting any of the values in cells B2, B3 and B4:

If any of the three values are missing, the film won't be added to the list.

You should also check that the code works when a complete set of details have been added:

When all details have been filled in, clicking the button adds the new film to the list.

Adding Code to the Function

From a user's perspective, it would be useful to see some information about why the film is not being added to the list.  We can add more code to the function to indicate to the user what has gone wrong.  We'll do three things if the user fails to add a value to a cell:

• Change the colour of the empty cell
• Select the empty cell
• Show a message in cell A5

Return to the Value_Missing function and add three instructions to the If statement:

Add three lines of code to the If statement as shown here.

We should also make sure that any changes are reversed if the user has entered a value into the cell.  Add two lines to the Else clause of the If statement:

Add two instructions to the Else clause as shown here.

You can test that the code works by returning to Excel and attempting to add a film with missing values:

It's now more obvious what the user has done wrong.

Check that the code works when all the film details have been entered:

When every detail has been entered, the formatting is cleared from the cells and the film is added to the list.

To practise returning a Boolean value from a function:

1. In Module1 create a new function called Date_Invalid:

2. Define a parameter called DateCell which accepts a reference to a Range object:

3. Set the return type of the function to Boolean:

4. Create an If statement which tests if the value of the DateCell is not a date:

5. Add code to the If statement to indicate to the user what has gone wrong:

6. Add an instruction to the If statement to make the function return True if the value of the cell is not a date:

7. Add an ElseIf statement to check if the date in the DateCell is later than the current system date:

8. Add code to indicate what the user has done wrong:

9. Add an instruction to make the function return True if the date in the cell is in the future:

10. Add an Else clause to reverse the formatting changes and return False if the previous two conditions are not met:

11. Return to the Add_To_List subroutine and select the code shown highlighted in the diagram below:

Select the two If statements shown highlighted here.

12. Replace the highlighted code with an If statement which calls the Date_Invalid function:

Call the Date_Invalid function and open a set of parentheses.

13. Pass a reference to the cell containing the date watched into the DateCell parameter of the function:

Refer to cell B3 and then close the parentheses.

14. If the result of the function is True, exit from the subroutine:

Complete the If statement as shown here.

15. Test the code by entering an invalid date in cell B3 and clicking the button on the worksheet:

Check that you see an appropriate indicator of the error when you enter an invalid date.

16. Check that the code works when you enter a valid date:

The formatting should be reset and the film added to the list when you enter a valid date and click the button.

17. You could create a function to test that the score is valid.  The code below shows one way to do this:

18. You can then select the section of code in the Add_To_List subroutine shown highlighted below:

You can replace the code shown highlighted here.

19. You can replace the selected code with a call to the Score_Invalid function:

Call the function, pass it a reference to cell B4, and exit from the subroutine if it returns True.

20. Test that the code works and then save and close the workbook.