Introduction to Procedures
|
|
Procedures
|
|
A
There are two categories of procedures you will use in your
spreadsheets: those that are already installed with
|
 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.
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:
|
on the Formula Bar.
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:
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
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.
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.
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
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.
|
No comments:
Post a Comment