Home

Introduction to Functions

 

Introduction to Procedures

 

Procedures

Like a sub procedure, a function is used to perform an assignment. The main difference between a sub procedure and a function is that, after carrying its assignment, a function gives back a result. We also say that a function "returns a value". To distinguish both, there is a different syntax you use for a function.

Creating a Function

To create a function, you use the Function keyword followed by a name and parentheses. Unlike a sub procedure, because a function returns a value, you must specify the type of value the function will produce. To give this information, on the right side of the closing parenthesis, you can type the As keyword, followed by a data type. To indicate where a function stops, type End Function. Based on this, the minimum syntax used to create a function is:

AccessModifier Function FunctionName() As DataType
    
End Function

As seen for a sub procedure, a function can have an access modifier.

The Function keyword is required.

The name of a function follows the same rules and suggestions we reviewed for names of sub procedures.

The As keyword may be required (in the next sections, we will review the alternatives to the As DataType expression).

The DataType factor indicates the type of value that the function will return. If the function will produce a word or a group of words, you can create it as String. The other data types are also valid in the contexts we reviewed them in the previous lesson. Here is an example:

Function GetFullName() As String
        
End Function

Practical Learning: Introducing Functions

  1. Start Microsoft Excel
  2. To save the document, in the Quick Access Toolbar, click the Save button Save
  3. Type the name of the file as Exercise2 and click Save.
    If a message box comes up, read it and click Yes
  4. On the Ribbon, click Developer
  5. In the Code section, click the Visual Basic button Visual Basic
  6. To create a function, type the following code:
     
    Option Explicit
    
    Function GetCustomerName() As String
        
    End Function

Using a Type Character

As done with variables, you can also use a type character as the return type of a function and omit the As DataType expression. The type character is typed on the right side of the function name and before the opening parenthesis. An example would be GetFullName$(). As with the variables, you must use the appropriate type character for the function:

Character The function must return
$ A string
% An integral value between -32768 and  32767
& An integer of small or large scale
! A decimal number with single precision
# A decimal number with double precision
@ A monetary value

Here is an example:

Function GetFullName$()

End Function

As mentioned for a sub procedure, the section between the Function and the End Function lines is the body of the function. It is used to describe what the function does. As done on a sub procedure, one of the actions you can perform in a function is to declare a (local) variable and use it as you see fit. Here is an example:

Function CallMe() As String
    Dim Salute As String
    Salute = "You can call me Al"
End Function

Returning a Value From a Function

After performing an assignment in a function, to indicate the value it returns, somewhere after the assignment and before the End Function line, you can type the name of the function, followed by the = sign, followed by the value that the function returns. Here is an example in which a function returns a name:

Function GetFullName$()
    Dim FirstName As String, LastName As String

    FirstName = "Patricia"
    LastName = "Katts"

    GetFullName = LastName & ", " & FirstName
End Function

Practical Learning: Implementing a Function

  1. To implement the function, change its code as follows:
     
    Option Explicit
    
    Function GetCustomerName() As String
        GetCustomerName = "Paul Bertrand Yamaguchi"
    End Function
  2. Save all
  3. To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button View Microsoft Excel

Calling a Function

As done for the sub procedure, in order to use a function in your program, you must call it. Like a sub procedure, to call a function, you can simply type its name in the desired section of the program. Here is an example:

Function CallMe() As String
    Dim Salute As String
    Salute = "You can call me Al"
    
    CallMe = Salute
End Function

Sub Exercise()
    CallMe
End Sub

When calling the function, you can optionally type the parentheses on the right side of its name.

The primary purpose of a function is to return a value. To better take advantage of such a value, you can assign the name of a function to a variable in the section where you are calling the function. Here is an example:

Function GetFullName$()
    Dim FirstName As String, LastName As String

    FirstName = "Patricia"
    LastName = "Katts"

    GetFullName = LastName & ", " & FirstName
End Function

Sub Exercise()
    Dim FullName$

    FullName = GetFullName()
    ActiveCell.FormulaR1C1 = FullName
End Sub
 
 

Calling a Function in a Spreadsheet

By now, we have seen that the primary (if not the only) difference between a function and a sub procedure is that a function returns a value. Because a sub procedure does not return a value, it cannot be directly accessed from a spreadsheet and you cannot use it with the ActiveCell.FormulaR1C1 = Value we have been using since the previous lesson. On the other hand, since a function returns a value, you can retrieve that value and assign it to our ctiveCell.FormulaR1C1 routine. Here is an example:

Function GetFullName$()
    Dim FirstName As String, LastName As String

    FirstName = "Patricia"
    LastName = "Katts"

    GetFullName = LastName & ", " & FirstName
End Function

Sub Exercise()
    Dim FullName$

    FullName = GetFullName()
    ActiveCell.FormulaR1C1 = FullName
End Sub

Better yet, if/when possible, you do not have to first declare a variable that would hold the value returned by a function. You can directly assign the function to the 
ActiveCell.FormulaR1C1 routine. Here is an example:

Function GetFullName$()
    Dim FirstName As String, LastName As String

    FirstName = "Patricia"
    LastName = "Katts"

    GetFullName = LastName & ", " & FirstName
End Function

Sub Exercise()
    ActiveCell.FormulaR1C1 = GetFullName()
End Sub

In the same way, since a function returns a value, you can use it directly in your spreadsheet. To do this, click any box in the work area:

Cells

After clicking the box, type =, followed by the name of the function. As you are typing the name of the function, Microsoft Excel would present a list of functions that match that name. If you see the name of the function, you can double-click it, or you can just keep typing. After typing the name of the function, type its parentheses, and press Enter or click the Enter button Enter on the Formula Bar.

Practical Learning: Calling a Function

  1. In Microsoft Excel, click any box
  2. To call the function we had created, type =G and notice the suggested list of functions:
     
    Calling a Function  
  3. If you see GetCustomerName in the list, double-click it. Otherwise, complete it with =GetCustomerName() and, on the Formula Bar, click the Enter button Enter
     
    A Function Was Called
  4. On the Ribbon, click Visual Basic

A Function and a Procedure

Depending on an author, in the Visual Basic language, the word "procedure" means either a sub-procedure created with the Sub keyword, or a function created with the Function keyword. In the same way, for the rest of our lessons, the word procedure will be used to represent both types. Only when we want to be precise will we use the expression "a sub-procedure" to explicitly mean the type of procedure that does not return a value. When the word "function" is used in our lessons, it explicitly refers to the type of procedure that returns a value.

 

Previous Copyright © 2008-2010 FunctionX Next