Module 4 - Variables, Parameters and Functions
Lesson 4.4 - Functions
Topic 4.4.1 - Basic Functions

Up to this point in the course, the only type of procedure that we have written is a subroutine.  As you know, a subroutine is a procedure which carries out a set of instructions and can be called by other procedures.  A function is also a procedure which can carry out a set of instructions and can be called by other procedures.  The major difference between a subroutine and a function is that a function can also return a value to the procedure which called it.

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 random numbers game we've used in previous lessons in this course:

Basic game

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:

Roll again

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:

New random number

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:

New function

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

 

Just as with subroutines, you can declare a function as Private so that it can be used only in a single module, or Public so that it can be used in every module in the project.  If you don't specify this, the function will be public by default.

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:

Define return type

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.

 

A function can return only one type of value or class of object.

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:

Function name

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:

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:

Immediate

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:

Test function

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:

Return value

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:

Existing code

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:

Call function

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 button

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:

Change function

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:

Function Result_Message()

 

End Function

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

Function Result_Message() As String

 

End Function

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

Function Result_Message() As String

 

Dim msg As String

 

End Function

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

Function Result_Message() As String

 

Dim msg As String

 

If Range("B2").Value > Range("D2").Value Then

 

End Function

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

Function Result_Message() As String

 

Dim msg As String

 

If Range("B2").Value > Range("D2").Value Then

msg = "Player 1 wins!"

 

End Function

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

Function Result_Message() As String

 

Dim msg As String

 

If Range("B2").Value > Range("D2").Value Then

msg = "Player 1 wins!"

ElseIf Range("B2").Value < Range("D2").Value Then

msg = "Player 2 wins!"

 

End Function

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

Function Result_Message() As String

 

Dim msg As String

 

If Range("B2").Value > Range("D2").Value Then

msg = "Player 1 wins!"

ElseIf Range("B2").Value < Range("D2").Value Then

msg = "Player 2 wins!"

Else

msg = "You're both losers!"

End If

 

End Function

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

Function Result_Message() As String

 

Dim msg As String

 

If Range("B2").Value > Range("D2").Value Then

msg = "Player 1 wins!"

ElseIf Range("B2").Value < Range("D2").Value Then

msg = "Player 2 wins!"

Else

msg = "You're both losers!"

End If

 

Result_Message = msg

 

End Function

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

Range("C4").Value = Result_Message

 

End Sub

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

You should see an appropriate message for each result.

 
  1. Save and close the workbook.
This page has 0 threads Add post