Introduction to Procedures

Procedures

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

1. Start Microsoft Excel
2. To save the document, in the Quick Access Toolbar, click the Save button
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
6. To create a function, type the following code:
    

   Option Explicit Function GetCustomerName() As String End Function

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:

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

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

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

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