VBA For Microsoft Excel - Lesson 05: Introduction to Procedures

	Introduction to Procedures and Functions

Introduction to Procedures

Procedures

A procedure is a section of code created to carry an assignment, separate from a spreadsheet, whose action can be used to complement a spreasheet. You create the procedure by writing code. One of the advantages of a procedure is that, once it exists, you can access it when necessary and as many times as you want.

There are two categories of procedures you will use in your spreadsheets: those that are already installed with Microsoft Excel and those you will create.

Practical Learning: Introducing Procedures

Start Microsoft Excel
On the Ribbon, click Developer
In the Code section, click the Visual Basic button
To create a module, on the main menu, click Insert -> Module
If the Properties window is not available, on the main menu, click View -> Properties Windows.
In the Properties window, click (Name)
Type Procedures and press Enter

In the Visual Basic language, like most other languages, there are two types of procedures: functions and sub procedures.

Introduction to Sub-Procedures

A sub procedure is an assignment that is carried but does not give back a result. To create a sub procedure, start with the Sub keyword followed by a name (like everything else, a procedure must have a name). The name of a procedure is always followed by parentheses. At the end of the sub procedure, you must type End Sub. Therefore, the primary formula to create a sub procedure is:

Sub ProcedureName()

End Sub

The name of a procedure should follow the same rules we learned to name the variables. In addition:

If the procedure performs an action that can be represented with a verb, you can use that verb to name it. Here are examples: show, display
To make the name of a procedure stand, you should start it in uppercase. Examples are Show, Play, Dispose, Close
You should use explicit names that identify the purpose of the procedure. If a procedure would be used as a result of another procedure or a control's event, reflect it on the name of the sub procedure. Examples would be: afterupdate, longbefore.
If the name of a procedure is a combination of words, you should start each word in uppercase. An example is AfterUpdate

The section between the Sub and the End Sub lines is referred to as the body of the procedure. Here is an example:

Sub CreateCustomer()

End Sub

In the body of the procedure, you carry the assignment of the procedure. It is also said that you define the procedure or you implement the procedure.

One of the actions you can in the body of a procedure consists of declaring a variable. There is no restriction on the type of variable you can declare in a procedure. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String
End Sub

In the same way, you can declare as many variables as you need inside of a procedure. The actions you perform inside of a procedure depend on what you are trying to accomplish. For example, a procedure can simply be used to create a string. The above procedure can be changed as follows:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

Calling a Sub Procedure

Once you have a procedure, whether you created it or it is part of the Visual Basic language, you can use it. Using a procedure is also referred to as calling it.

Before calling a procedure, you should first locate the section of code in which you want to use it. To call a simple procedure, type its name. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

Sub Exercise()
    CreateCustomer
End Sub

Besides using the name of a procedure to call it, you can also precede it with the Call keyword. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

Sub Exercise()
    Call CreateCustomer
End Sub

When calling a procedure, without or without the Call keyword, you can optionally type an opening and a closing parentheses on the right side of its name. Here is an example:

Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

Sub Exercise()
    CreateCustomer()
End Sub

Procedures and Access Levels

Like a variable access, the access to a procedure can be controlled by an access level. A procedure can be made private or public. To specify the access level of a procedure, precede it with the Private or the Public keyword. Here is an example:

Private Sub CreateCustomer()
    Dim strFullName As String

    strFullName = "Paul Bertrand Yamaguchi"
End Sub

The rules that were applied to global variables are the same:

Private: If a procedure is made private, it can be called by other procedures of the same module. Procedures of outside modules cannot access such a procedure.
Also, when a procedure is private, its name does not appear in the Macros dialog box
Public: A procedure created as public can be called by procedures of the same module and by procedures of other modules.
Also, if a procedure was created as public, when you access the Macros dialog box, its name appears and you can run it from there

Introduction to Functions

Introduction

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: Creating a Function

Click an empty area in the Code editor and, 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

To implement the function, change its code as follows:

Option Explicit

Function GetCustomerName() As String
    GetCustomerName = "Paul Bertrand Yamaguchi"
End Function

To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button

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. 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 on the Formula Bar.

Practical Learning: Calling a Function

In Microsoft Excel, click any box
To call the function we had created, type =G and notice the suggested list of functions:
If you see GetCustomerName in the list, double-click it. Otherwise, complete it with =GetCustomerName() and, on the Formula Bar, click the Enter button
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.

Arguments and Parameters

A Review of Local and Global Variables

In the previous lesson, we saw that you could declare a global variable outside of any procedure. When using various procedures in a module, one of the characteristics of a global variable is that it is automatically accessible to other procedures:

Private: A private global variable can be accessed by any procedure of the same module. No procedure of another module, even of the same program, can access it
Public: A public global variable can be accessed by any procedure of its module and any procedure of another module

Based on this characteristic of the procedures of a module having access to global variables of the same module, you can declare such variables and initialize or modify them in any procedure of the same code file.

Here is an example:

Option Explicit

Private Length As Double
Private Width As Double
    
Private Sub GetLength()
    Length = 48.24
End Sub

Private Sub GetWidth()
    Width = 25.82
End Sub
   
Private Function CalculatePerimeter() As Double
    GetLength
    GetWidth
    CalculatePerimeter = (Length + Width) * 2
End Function

Introduction to Arguments

So far, to use a value in a procedure, we had to declare it. In some cases, a procedure may need an external value in order to carry its assignment. A value that is supplied to a procedure is called an argument.

When creating a procedure that will use an external value, declare the argument that represents that value between the parentheses of the procedure. For a sub procedure, the syntax you use would be:

Sub ProcedureName(Argument)
      
End Sub

If you are creating a function, the syntax would be:

Function ProcedureName(Argument) As DataType
      
Function Sub

The argument must be declared as a normal variable, omitting the Dim keyword. Here is an example that creates a function that takes a string as argument:

Function CalculatePayroll(strName As String) As Double
      
Function Sub

While a certain procedure can take one argument, another procedure can take more than one argument. In this case, in the parentheses of the procedure, separate the arguments with a comma. Here is an example of a sub procedure that takes two arguments:

Sub EvaluateInvoice(EmplName As String, HourlySalary As Currency)
      
End Sub

In the body of a procedure that takes one or more arguments, use the argument(s) as you see fit as if they were locally declared variables. For example, you can involve them with values inside of the procedure. You can also exclusively use the values of the arguments to perform the assignment.

Practical Learning: Creating a Function With Arguments

To create functions that take arguments, type the following

Option Explicit

Public Function CalculatePerimeter(Length As Double, _
				   Width As Double) As Double
    Dim Perimeter As Double
    
    Perimeter = (Length + Width) * 2
    CalculatePerimeter = Perimeter
End Function
    
Public Function CalculateArea(Length As Double, Width As Double) As Double
    Dim Area As Double
    
    Area = Length * Width
    CalculateArea = Area
End Function

Calling a Procedure With Argument

The value provided for an argument is also called a parameter. To call a procedure that takes an argument, type its name. Then you have various options to access its argument(s).

Earlier, we saw that, to call a procedure, you could just use its name. After the name of the procedure, you can type the opening parenthesis "(", followed by the name of the argument, followed by =, and the value of the argument. If the procedure takes more than one argument, separate them with commas. Here is an example:

Private Function GetFullName$(First As String, Last As String)
    Dim FName As String

    FName = First & Last
    GetFullName = FName
End Function

Sub Exercise()
    Dim FirstName As String, LastName As String
    Dim FullName As String

    FirstName = "Patricia "
    LastName = "Katts"

    FullName = GetFullName(FirstName, LastName)
    
    ActiveCell.FormulaR1C1 = FullName
End Sub

As mentioned previously, you can also use the Call keyword to call a procedure.

When you call a procedure that takes more than one argument, you must provide the values of the arguments in the order they are listed inside of the parentheses. Fortunately, you don't have to. If you know the names of the arguments, you can type them in any order and provide a value for each. To do this, in the parrentheses of the procedure you are calling, type the name of the argument whose value you want to specify, followed by the := operator, and followed by the desired value for the argument. Here is an example:

Private Function GetFullName$(First As String, Last As String)
    Dim FName As String

    FName = First & Last
    GetFullName = FName
End Function

Sub Exercise()
    Dim FullName$

    FullName$ = GetFullName(Last:="Roberts", First:="Alan ")
    
    ActiveCell.FormulaR1C1 = FullName
End Sub

The above technique we have just seen for using the parentheses is valid for sub procedures and functions. If the procedure you are calling is a sub, you can omit the parentheses. If calling a sub procedure, after the name of the procedure, put an empty space, followed by the name of the argument assigned the desired value. Here is an example:

Private Sub ShowResult(ByVal Result As Double)
    Result = 145.85
End Sub
    
Public Sub Exercise()
    Dim Number As Double
    
    ShowResult Number
End Sub

If the sub procedure is taking more than one argument, separate them with commas.

Practical Learning: Calling a Procedure With Argument

In Microsoft Excel, click any box
To call the function we had created, type =C and notice the suggested list of functions:
In the list of suggested functions, double-click CalculatePerimeter. If you don't see it, complete the typing with =CalculatePerimeter(
After the opening parenthesis, type 48.26, 25.42 as the arguments, then type the closing parenthesis ")"
On the Formula Bar, click the Enter button
Press Enter
Type =CalculateArea(48.26, 25.26) and press Enter
On the Ribbon, click Visual Basic

Techniques of Passing Arguments

Passing Arguments By Value

When calling a procedure that takes an argument, we were supplying a value for that argument. When this is done, the procedure that is called makes a copy of the value of the argument and makes that copy available to the calling procedure. That way, the argument itself is not accessed. This is referred to as passing an argument by value. To show this, type the ByVal keyword on the left side of the argument. Here are examples:

Private Function GetFullName$(ByVal First As String, ByVal Last As String)
    Dim FName As String

    FName = First & Last
    GetFullName$ = FName
End Function

If you create a procedure that takes an argument by value and you have used the ByVal keyword on the argument, when calling the procedure, you do not need to use the ByVal keyword; just the name of the argument is enough, as done in the examples on arguments so far. Here is an example:

Private Function GetFullName$(ByVal First As String, ByVal Last As String)
    Dim FName As String

    FName = First & Last
    GetFullName$ = FName
End Function

Sub Exercise()
    Dim FirstName As String, LastName As String
    Dim FullName As String

    FirstName = "Raymond "
    LastName = "Kouma"

    FullName = GetFullName(FirstName, LastName)
    
    ActiveCell.FormulaR1C1 = FullName
End Sub

Practical Learning: Passing Arguments By Value

To specify that the arguments are passed by value, change the functions as follows:

Public Function CalculatePerimeter(ByVal Length As Double, _
				   ByVal Width As Double) As Double
    Dim Perimeter As Double
    
    Perimeter = (Length + Width) * 2
    CalculatePerimeter = Perimeter
End Function
    
Public Function CalculateArea(ByVal Length As Double, _
			      ByVal Width As Double) As Double
    Dim Area As Double
    
    Area = Length * Width
    CalculateArea = Area
End Function

To return to Microsoft Excel, on the toolbar, click the View Microsoft Excel button

Passing Arguments By Reference

An alternative to passing arguments as done so far is to pass the address of the argument to the called procedure. When this is done, the called procedure does not receive a simple copy of the value of the argument: the argument is accessed by its address; that is, at its memory address. With this technique, any action carried on the argument will be kept by the argument when the procedure ends. If the value of the argument is modified, the argument would now have the new value, dismissing or losing the original value it had. This technique is referred to as passing an argument by reference. Consider the following code:

Private Sub ShowResult(ByVal Result As Double)
    Result = 145.85
End Sub
    
Public Sub Exercise()
    Dim Number As Double
    
    ShowResult Number
    
    ActiveCell.FormulaR1C1 = Number
End Sub

When the Exercise() procedure starts, a variable named Number is declared and its value is set to 0 (the default value of a newly declared Double variable). When the ShowResult variable is called, it assigns a value to the variable but since the variable is declared by value, when the procedure exits, the variable comes back with its original value, which was 0. As a result, when this code is run, the Number variable keeps its 0 value.

If you want a procedure to change the value of an argument, you can pass the argument by reference. To pass an argument by reference, on its left, type the ByRef keyword. This is done only when creating the procedure. When you call the procedure, don't include the ByRef keyword. When the called procedure finishes with the argument, the argument would keep whatever modification was made on its value. Now consider the same program as above but with arguments passed by reference:

Private Sub ShowResult(ByRef Result As Double)
    Result = 145.85
End Sub

Public Sub Exercise()
    Dim Number As Double
    
    ShowResult Number
    
    ActiveCell.FormulaR1C1 = Number
End Sub

When the Exercise() procedure starts, the Number variable is declared and its value is set to 0. When the ShowResult variable is called, it assigns a value to the variable. Since the variable is declared by reference, when the procedure exits, the variable comes back with the new value it was given. As a result, when this code runs, the Number variable has a new value.

Using this technique, you can pass as many arguments by reference and as many arguments by value as you want. As you may guess already, this technique can be used to make a sub procedure return a value, which a regular sub routine cannot do. Furthermore, passing arguments by reference allows a procedure to return as many values as possible while a regular function can return only one value.

Practical Learning: Closing Microsoft Excel

To close Microsoft Excel, click the Office Button and click Exit Excel
When asked whether you want to save the file, click No


Previous	Copyright © 2008-2014, FunctionX	Next