Functions of date and time
1.16. User-defined procedures
1.16. User-defined procedures
A user-defined procedure allows executing an operator block in different places of our program.
According to this VB construct:
1) we have to write the block, intended for multiple executions, only once;
2) a name with formal parameters or without them must be written in the header of the block.
This expanded block is called a procedure declaration.
Generally, the user-defined procedure has:
input parameters, which are considered given;
output parameters, which are determined while executing the block.
After forming the procedure declaration, we locate the calls of this procedure with the actual parameters in those places of the program, which should be occu-pied by the block being the procedure prototype.
The procedures are divided into functions and subroutines (subprograms).
The function, returning a value, is intended for usage in arithmetic and logical expressions and in strings. The subroutine usage in expressions and strings is impossible. This is the main difference between the function and subroutine.
The declaration of the user-defined function has the following syntax:
Function name([formal_parameters]) [As type]
statements End Function
where name is the function name, type is the data type (Appendix 1) of name, i.e., of the function value, formal_parameters are the parameter (argument) names listed through a comma, statements is the operator block.
The parameter names can be accompanied by keywords.
Block statements must include at least one assignment operator whose left-hand side (on the left of sign =) is the function name.
The function call looks like name([actual_parameters])
where actual_parameters are variables, arrays, expressions (arithmetic, logical) and/or strings listed through a comma.
As a result of calling the name function, the function value, corresponding to actual_parameters, is returned into the program.
Records and arrays of records may be among formal and actual parameters.
We will review the record construct in Section 1.18.
Let us consider the following code:
Sub Program1() Dim L As Long Dim W As Double L = Fact(12)
W = 4.2 + Fact(10) / 2 End Sub
Function Fact(N) As Long 'N < 13 Dim I As Byte
Dim J As Long J = 1
For I = 1 To N J = J * I Next I
Fact = J End Function
The first group of operators is program Program1; the second group is the declaration of function Fact. They are in one or different modules of the same Excel workbook, i.e., we can type the program and the function declaration in one or different code windows.
The Fact function calculates N!, i.e., factorial of natural number N, which is the formal parameter.
We see two calls of the Fact function in the program, with 12 and 10 as the actual parameter.
1. The Fact function call is located in the right-hand side of assignment operator
L = Fact(12)
As a result of the operator execution, the Fact function value (that is, the value returned by the Fact function into the program when N is equal to 12) is assigned to the L variable.
1.16. User-defined procedures
2. The Fact function call is figured in arithmetic expression 4.2 + Fact(10) / 2
The value of this arithmetic expression is assigned to the W variable.
The declaration of the user-defined subroutine has the following syntax:
Sub name([formal_parameters]) statements
End Sub
where name is the subroutine name, formal_parameters are the parameter names listed through a comma, as in the above declaration of the user-defined function, statements is the operator block.
Keyword Sub occurs from word “subroutine”. It is in the beginning and end not only of the subroutine declaration (or simply of the subroutine), but also of the program because “main subroutine” is synonym of “program”.
There are two equivalent operators of calling the subroutine:
Call name([actual_parameters]) name [actual_parameters]
where actual_parameters is the list of actual parameters, as in the above call of the user-defined function. In the presence of the Call keyword, the actual parameters are in parentheses; in the absence of Call, the parentheses are not used.
The example program and subroutine follow:
Sub Program2()
Dim aa As Single, bb As Single
Dim cc1 As Single, cc2 As Single, cc3 As Single aa = 3
bb = 4
Call Hypotenuse(aa, bb, cc1)
'1st call of subroutine Call Hypotenuse(3, 4, cc2)
'2nd call of subroutine Hypotenuse aa, bb, cc3
'3rd call of subroutine End Sub
Sub Hypotenuse(ByVal A, ByVal B, ByRef C) C = Sqr(A ^ 2 + B ^ 2)
End Sub
The first group of operators is program Program2, the second group is the declaration of subroutine Hypotenuse for calculating the length of the hypote-nuse of a right-angled triangle. They are located in one or different modules.
We see three operators of calling the Hypotenuse subroutine in program Program2, and two of them contain the Call keyword.
Formal parameters A and B (in the subroutine declaration) are the input pa-rameters, lengths of the legs. The ByVal keyword in front of A and B in the first line of the subroutine declaration (i.e., in the header of the operator block) means that these parameters must be passed by value (when calling Hypotenuse). In this case, the values of parameters A and B (3 and 4, respectively) are transferred to the Hypotenuse subroutine at all three calls.
Formal parameter C is the output parameter, i.e., the hypotenuse length. The ByRef keyword in front of C means that this parameter must be passed by reference (when calling Hypotenuse). In this case, the address of the memory cell, corresponding to variable cc1 (at the first call), cc2 (at the second call) or cc3 (at the third call), is transferred to the Hypotenuse subroutine.
Keyword ByRef may be omitted.
In the code under consideration, the parameters of the Hypotenuse subroutine are simple variables (not arrays). If the parameters are arrays, both input and output parameters must be passed by reference.
Let us consider the following code located in one module:
Dim N1 As Integer
Sub XSINX(ByVal N2, ByRef X() As Double, _ ByRef F() As Double)
1.16. User-defined procedures
In addition to usage of arrays as the subroutine parameters, we see something new in the last code: the N1 variable is declared above the first line of the pro-gram. This is done for making N1 visible (in respect of the usage possibility) in both the program and subroutine. Let us return to the variable declaration.
So far, we have been speaking about how to declare variables, but have not mentioned where to declare. We can declare them in two places:
inside the program or user-defined procedure;
in the general declarations area occupying the top part of the code window.
The place of the variable declaration defines the area of the variable usage.
For example, if a variable is declared in the user-defined procedure (as variable j in the last code), only this procedure “sees” this variable. Other procedures (if they exist) and the program cannot use this variable’s value and change it.
Such variable is called a local variable. We can also say that the variable is visible at the procedure level.
For letting a variable’s value be accessible to all user-defined procedures of the given module, we have to declare this variable in the general declarations area (as variable N1 in the last code). In this case, the program and all user-defined procedures, declared in the given module, can use this variable’s value and change it.
Such variable is called a module variable. The Dim keyword in front of N1 may be replaced by the Private keyword:
Private N1 As Integer
All that was said about variables also concerns the user-defined constants, but the constant’s value, naturally, cannot be changed.
If the declaration of the XSINX subroutine is located in the separate module, the first line of the last code should be as follows:
Public N1 As Integer
Such variable is called a public variable. The declaration of public variable N1 can be in each of two modules or only in one, the first (with text of Program3) or second (with text of XSINX).
At the similar declaration of a constant, instead of a variable, the correspond-ing line of the general declarations area should begin with keyword combination PublicConst.
The debugger command, being fulfilled by pressing the F8 key, is called Step Into: during the step-by-step program execution by means of the F8 key, the entrance into the user-defined procedure takes place. If we do not need the step-by-step execution inside the user-defined procedure, we use the following two commands of the Debug menu.
1. Step Over — the step-by-step program execution without entrance into the user-defined procedure. This command can also be performed by pressing Shift + F8.
2. Step Out — exit from the user-defined procedure. It is used when the pro-cedure remainder should be executed in the automatic mode. This command can also be performed by pressing Ctrl + Shift + F8.
We advise the reader to execute programs Program1, Program2 and Program3 by pressing F8, Ctrl + F8, Shift + F8 and Ctrl + Shift + F8. Before the execution, the blinking cursor must be located inside the program text (not in the procedure declaration and not in the general declarations area).
One or several last parameters of the list of parameters of the user-defined procedure (function or subroutine) can be optional, i.e., they can be absent in the procedure call.
If the parameter is not obligatory, the Optional keyword must be in front of this parameter’s name in the first line of the procedure declaration. The optional parameter must have the Variant data type (Appendix 1).
Let us consider the following example code:
Sub Program4()
Function Apt(ByVal a, Optional b As Variant) If Not IsMissing(b) Then b = a + 1
Apt = a ^ 4 End Function
1.16. User-defined procedures
In this example, the b parameter of the Apt function is optional; keyword Optional in front of this parameter tells about it. Keyword combination AsVariant behind b may be omitted.
Other features of the Apt function declaration:
Not is the logical negation;
IsMissing is the following function.
The value of IsMissing(b) is equal to True or False as follows:
True in the absence of the optional parameter in the Apt function call;
False in the presence of the optional parameter.
We advise the reader to execute the Program4 program step-by-step, watching the values of variables bytB and intC.
We can specify a value of the optional parameter in the absence of this parameter in the call of the user-defined procedure.
The example program and procedure follow:
Sub Program5()
the b parameter of the Ept subroutine is optional;
if the call of Ept contains only two actual parameters, b=3 is used when executing operator c=a+b.
We advise the reader to make the step-by-step execution of the Program5 program, watching the value of bytC.
Operators Exit Sub Exit Function
are intended for immediate terminating the procedure execution. The first opera-tor should be in the subroutine declaration, the second — in the function declara-tion. At their execution, the jump is being performed to the same point, as upon the normal terminating the procedure execution.
The programs in this section use only one user-defined procedure; for exam-ple, program Program1 uses only the Fact function. However, several user-defined procedures may be used by one program.
According to the computer terminology, as a program, we can consider the program itself (the main subroutine) together with the declarations of the user-defined procedures (used by the program) and/or together with the general decla-rations area. For example, program Program3 together with operator
Dim N1 As Integer
can also be called program Program3. The following can also be called pro-gram Propro-gram3: the propro-gram itself, the XSINX subroutine declaration and the general declarations area. Sometimes term “program” is used in the extended sense, which is equivalent to “code”.