MODULES
LESSONS
TOPICS
Creating functions which return Boolean values is a useful way to structure code which validates 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:
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.
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:
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:
- In Module1 create a new function called Date_Invalid:
Function Date_Invalid()
End Function
- Define a parameter called DateCell which accepts a reference to a Range object:
Function Date_Invalid(DateCell As Range)
End Function
- Set the return type of the function to Boolean:
Function Date_Invalid(DateCell As Range) As Boolean
End Function
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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.
- 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.
- 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.
- If the result of the function is True, exit from the subroutine:
Complete the If statement as shown here.
- 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.
- 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.
- 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
- 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.
- 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.
- Test that the code works and then save and close the workbook.