The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a version of the random numbers game we've used in previous lessons in this course:

You should be fairly familiar (or bored) with this game by now!

In the VBE you'll find a version of the Roll_Again procedure:

The entire Roll_Again procedure is shown here.

We'd like to move parts of this subroutine into separate procedures which we can then call.  The important thing is that when we call one of these separate procedures, it must be able to return a value to the Roll_Again subroutine.

Creating a Function

The first part of the Roll_Again subroutine that we want to move into a new procedure is that which calculates a new random score:

We want to replace the code shown highlighted here with a new procedure.

The procedure that we will create must be capable of returning a value when it is called.  This means that we can't use a subroutine and instead must create a new function.  Creating a function is similar to creating a subroutine except that you use the word Function rather than Sub.  Create a new function called New_Score in the same module:

Write the word Function followed by the name you want to use and then press Enter. The End Function line will be created automatically.

Specifying the Type of Value Returned

When you define a function, you can state what type of data it will return, in much the same way as stating the data type when you declare a variable.  Our New_Score function will return a value of the Double data type.  You can indicate this as shown in the diagram below:

After the function name and the parentheses which follow it, type the word As followed by the data type you want to use. Here we're using the Double data type.

Calculating the Value to Return

The next job is to write the code which calculates a value to return.  A function may need many lines of code in order to calculate its final result but in our example we need only a single line.  To return a value from a function, begin the instruction with the name of the function followed by an = operator:

Write the function's name followed by an = operator as shown here.

You can complete the instruction by writing the code which calculates a random number:

You can write this code or copy it from the Roll_Again subroutine.

Testing a Function

Before calling your function in another procedure you may wish to test that it returns the value you expect.  One way to do this is to use the Immediate window:

If you can't see this window choose View | Immediate Window from the menu or press Ctrl + G on the keyboard.

To test the function in the Immediate window, enter a question mark, followed by the function's name:

Type ?New_Score to call the function in the Immediate window.

Press Enter at the end of the line to execute the function and return a value to the Immediate window:

Press Enter to return a value. You can click at the end of the function name and press Enter to return a new value.

Calling a Function

Calling a function is very much like calling a subroutine.  The big difference is that you can make use of the value returned by a function in the procedure which calls it.  Return to the Roll_Again subroutine and find the two lines which generate new random numbers for the player scores:

Find these two instructions in the Roll_Again subroutine.

Edit the code so that it calls our New_Score function and returns the result to each of the two cells:

You can call the function simply by entering its name. You can also press Ctrl + Spacebar and find the function name in the IntelliSense list.

Return to Excel and click the button on the worksheet to check that the code still works:

Click the button to generate new random numbers using the New_Score function.

The advantage of structuring code in this way is that it makes it easier to make changes.  For example, if we want to change the way the score is calculated, we now have only a single place in which we need to change the code:

We can change the code in the function rather than in every instruction that used the same calculation.

To practise the techniques used in this part of the lesson we'll create a function which calculates a result message to appear in cell C4 on the worksheet:

1. In the same module, create a new function called Result_Message:

2. Set the return type of the function to String:

3. Declare a String variable called msg in the function:

4. Begin an If statement to check if the value of cell B2 is greater than that of D2:

5. If this is condition is satisfied, set the value of the msg variable to indicate that Player 1 is the winner:

6. Add an ElseIf statement and produce an appropriate message if Player 2 is the winner:

7. Add an Else clause which produces an appropriate message if neither player wins:

8. Add a line which instructs the function to return the value stored in the msg variable:

9. Return to the Roll_Again procedure and add a line at the end of the subroutine which sets the value of cell C4 by calling the Result_Message function:

10. Return to Excel and test that the function works by clicking the button on the worksheet:

You should see an appropriate message for each result.

11. Save and close the workbook.