MODULES
LESSONS
TOPICS
In previous parts of this lesson you've learnt how to write functions to return simple values such as strings and numbers. You can also write functions which return references to objects, as this part of the lesson shows.
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 completed 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 new films to one of several worksheets:
Clicking the button will add the details to one of three worksheets based on the score assigned to cell B4.
In the VBE you can find a subroutine called Add_To_List:
This subroutine uses a Worksheet and a Range object variable to work out where to paste the details of the new film.
We'd like to move the code which determines which worksheet and range to use into separate functions. This means that the functions must be capable of returning references to objects rather than simple values.
Creating a Function to Return an Object Reference
We'll start by creating a function to determine which worksheet the new fill will be pasted into. Create a new function called Movie_Sheet in Module1:
Create a new function and give it a sensible name.
The function will need to know the score assigned to the movie in order to work out which worksheet to use. Define a parameter called Score which uses the Integer data type to allow the function to accept a value when it is called:
Define the parameter in the parentheses after the function name.
Now we can specify the return type of the function. In this case we want the function to return a reference to a worksheet:
After the parentheses type As followed by the class of object that the function will return a reference to.
Returning a Reference to an Object
To return a reference to the correct worksheet, the function first needs to test the value of the Score parameter. Start writing an If statement to check if the value of the Score parameter is less than or equal to 4:
You can write this code or copy it from the Add_To_List subroutine and edit it.
If the condition is satisfied we want the function to return a reference to the Rubbish worksheet. Just as you must use the Set keyword to set a reference in an object variable, you must also use Set to set the reference returned by the function:
Set the reference returned by the function as shown here.
You can complete the If statement to return a reference to the OK worksheet when the score is less than or equal to 8, or a reference to the Great worksheet for any other score:
Complete the If statement as shown here. You may find it easier to copy the code from the Add_To_List procedure and edit it to match the version shown here.
Calling the Function
We can now call the function from the Add_To_List subroutine. Return to the subroutine and find the section of code shown highlighted below:
Find and select the code shown highlighted here.
Replace the code shown above with an instruction which sets the reference of the DestinationSheet variable:
Begin the instruction as shown here.
At this point we can call the Movie_Sheet function and open a set of parentheses:
Open the parentheses to see the tooltip which describes the function's parameters.
We can pass the value of the MovieScore variable to the Score parameter of the function:
Refer to the MovieScore variable and then close the parentheses.
Testing the Code
We can now return to Excel and click the button on the worksheet to test that the code works:
Click the button on the worksheet to execute the Add_To_List subroutine.
As the subroutine doesn't move to a different worksheet you'll need to select one manually if you want to see the result:
Click the relevant worksheet tab at the bottom of the screen to check that the film appears on the worksheet you expected.
To practise returning object references from a function we'll create a function to return a reference to the next blank cell in column A on the relevant worksheet:
- Create a new function called Next_Blank_Cell:
Function Next_Blank_Cell()
End Function
- Set the return type of the function to the Range class:
Function Next_Blank_Cell() As Range
End Function
- Define a parameter called SheetToUse which will accept a reference to a Worksheet object:
Function Next_Blank_Cell(SheetToUse As Worksheet) As Range
End Function
- Begin writing an instruction which sets the reference returned by the function:
Function Next_Blank_Cell(SheetToUse As Worksheet) As Range
Set Next_Blank_Cell =
End Function
- Complete the instruction by referencing the next blank cell in column A:
Function Next_Blank_Cell(SheetToUse As Worksheet) As Range
Set Next_Blank_Cell = _
SheetToUse.Range("A1048576").End(xlUp).Offset(1, 0)
End Function
- Return to the Add_To_List subroutine and find the section of code shown highlighted below:
Find and select the code shown highlighted here.
- Replace the highlighted code with a call to the Next_Blank_Cell function:
Write the function name followed by an open parenthesis.
- Pass a reference to the DestinationSheet variable to the SheetToUse parameter:
Enter the name of the variable and then close the parentheses.
- Return to Excel and test that the code still works when you click the button on Sheet1:
Click the button and then select the relevant worksheet to check that the details of the film have been copied to the correct location.
- Save and close the workbook.