Module 4 - Variables, Parameters and Functions
Lesson 4.4 - Functions
Topic 4.4.4 - Returning Boolean Values

Creating functions which return Boolean values is a useful way to structure code which validates data.

Validating data

There are many conditions to test to ensure data is valid before accepting it. Using functions helps in terms of organising the code required to do this.

 

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

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:

Basic system

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:

Main procedure

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:

Define function

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 parameter

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:

Return type

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:

If statement

Construct an If statement to return the correct response.

If this is all we want the function to do, we can return the correct response in a more concise way like so:

Value_Missing = (CellToTest.Value = "")

We're using an If statement as we want the function to do more work later!

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:

Select code

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 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:

Pass reference

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:

Exit sub

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:

Test

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:

Reuse function

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:

Test function

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:

Working code

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 code

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:

Reverse changes

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:

Test code

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

 

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

Code working

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:

Function Date_Invalid()

 

End Function

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

Function Date_Invalid(DateCell As Range)

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

End If

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Not a date"

 

End If

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Not a date"

 

Date_Invalid = True

 

End If

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Not a date"

 

Date_Invalid = True

 

ElseIf DateCell.Value > Date Then

 

End If

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Not a date"

 

Date_Invalid = True

 

ElseIf DateCell.Value > Date Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Date in the future"

 

End If

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Not a date"

 

Date_Invalid = True

 

ElseIf DateCell.Value > Date Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Date in the future"

 

Date_Invalid = True

 

End If

 

End Function

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

Function Date_Invalid(DateCell As Range) As Boolean

 

If Not IsDate(DateCell.Value) Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Not a date"

 

Date_Invalid = True

 

ElseIf DateCell.Value > Date Then

 

DateCell.Interior.Color = rgbPink

DateCell.Select

Range("A5").Value = "Date in the future"

 

Date_Invalid = True

 

Else

 

DateCell.Interior.ColorIndex = xlNone

Range("A5").ClearContents

 

Date_Invalid = False

 

End If

 

End Function

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

Select the two If statements shown highlighted here.

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

Call the Date_Invalid function and open a set of parentheses.

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

Refer to cell B3 and then close the parentheses.

 
  1. If the result of the function is True, exit from the subroutine:
Exit sub

Complete the If statement as shown here.

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

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

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

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

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

Function Score_Invalid(ScoreCell As Range) As Boolean

 

If Not IsNumeric(ScoreCell.Value) Then

 

ScoreCell.Interior.Color = rgbPink

ScoreCell.Select

Range("A5").Value = "Not a number"

 

Score_Invalid = True

 

ElseIf ScoreCell.Value < 0 Or _

ScoreCell.Value > 10 Then

 

ScoreCell.Interior.Color = rgbPink

ScoreCell.Select

Range("A5").Value = "Score out of range"

 

Score_Invalid = True

 

Else

 

ScoreCell.Interior.ColorIndex = xlNone

Range("A5").ClearContents

 

Score_Invalid = False

 

End If

 

End Function

  1. You can then select the section of code in the Add_To_List subroutine shown highlighted below:
Code to replace

You can replace the code shown highlighted here.

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

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

 
  1. Test that the code works and then save and close the workbook.
This page has 0 threads Add post