Étiquette : first_program

When calling a procedure, you pass certain parameters to it. Inside the procedure, these parameters can be assigned new values, which will persist after the procedure finishes.

Thus, by default, when variables are passed as parameters, their physical addresses are passed into the procedure. Therefore, the contents of these variables can be modified inside the procedure. To explicitly specify passing parameters by reference, the keyword ByRef is used.

Another way of passing parameters to a procedure is by value. In this case, not the variable itself, but only its value is passed into the procedure. Passing a parameter by value is specified with the keyword ByVal.

The following example demonstrates the difference between passing a parameter by reference and by value.

Passing parameters by reference and by value

Sub DemoByValByRef(ByVal a, b, ByRef c)
    ' a is passed by value
    ' b is passed by reference by default
    ' c is explicitly passed by reference
    a = a + 1
    b = b + a
    c = c + a
End Sub

Sub Test()
    a = 1 : b = 10 : c = 100
    DemoByValByRef a, b, c
    MsgBox a
    ' Displays 1
    MsgBox b
    ' Displays 12
    MsgBox c
    ' Displays 102
End Sub

In VBA, it is possible to use an array as a procedure parameter. In this case, the array used as a parameter must be declared as dynamic in the procedure definition.

In the following example, the procedure SumM returns the sum of the elements of the array, which is passed as its first parameter.

Using an array as a procedure parameter

Option Base 1
Sub SumM(ByRef A() As Double, ByRef s As Double)
    Dim x As Variant
    s = 0
    For Each x In A
        s = s + x
    Next
End Sub

Sub DemoSumM()
    Dim B(2, 2) As Double
    Dim s As Double
    B(1, 1) = 1 : B(1, 2) = 5
    B(2, 1) = 4 : B(2, 2) = 5
    SumM B, s
    MsgBox s
    ' Result: 15
End Sub

As a rule, the number of arguments passed to a procedure matches the number of parameters defined for that procedure. However, the keyword ParamArray allows you to pass an arbitrary, unspecified number of arguments to a procedure (similar to how the worksheet function SUM() in MS Excel works).

As an example, consider the following Function procedure, which concatenates all strings passed as parameters into a single string.

Example of a procedure with an indefinite number of parameters

Function PutTogether(FirstWord As String, _
                     ParamArray Words() As Variant) As String
    Dim s As String
    Dim a As Variant
    s = FirstWord
    For Each a In Words
        s = s & a
    Next a
    PutTogether = s
End Function

Sub DemoPutTogether()
    Dim a As String, b As String, c As String
    a = "Drop " : b = "wears " : c = "away stone"
    MsgBox PutTogether("Proverb: ", a, b, c)
    ' Displays: Proverb: Drop wears away stone
    MsgBox PutTogether("Saying: ", "Perseverance ", "and hard work", _
                       " overcome ", "everything")
    ' Displays: Saying: Perseverance and hard work overcome everything
End Sub

For an optional parameter, you can specify a default value.

In the following example, default intensities are set for the red, green, and blue components of the color of the selected cell range.

Specifying a default value for an optional parameter

Sub Color(Optional Red As Integer = 0, Optional Green As Integer = 255, _
          Optional Blue As Integer = 0)
    Selection.Interior.Color = RGB(Red, Green, Blue)
End Sub

Sub DemoDefaultValues()
    Color Blue:=175
End Sub

The UserName() function returns a string consisting of the first name and last name specified as its parameter values. If one of the parameters is omitted, it returns an incomplete name.

When working with optional parameters, you must use the IsMissing() function, which returns True if the corresponding parameter was not passed to the procedure, and False otherwise.

Function procedure with optional parameters

Function UserName(Optional LastName As String, _
                  Optional FirstName As String) As String
    If Not (IsMissing(LastName)) And Not (IsMissing(FirstName)) Then
        UserName = LastName & Space(1) & FirstName
    ElseIf IsMissing(LastName) And Not (IsMissing(FirstName)) Then
        UserName = FirstName
    ElseIf Not IsMissing(LastName) And IsMissing(FirstName) Then
        UserName = LastName
    End If
End Function

Demonstration of using the function:

Sub DemoUserName()
    MsgBox UserName(LastName:="Bond", FirstName:="James")
    ' Bond James
    MsgBox UserName("James", "Bond")
    ' James Bond
    MsgBox UserName("Bond")
    ' Bond
    MsgBox UserName(, "James")
    ' James
    MsgBox UserName()
    ' Nothing
End Sub

A program in VBA consists of one or more modules. Usually, the text of a program in a module begins with directives that control the description of variables, the method of string comparison, and so on. Then come the declarations of module-level or project-level variables and constants, i.e., variables and constants that can be used in all procedures of either the module or the project. After that, the code of Sub and Function procedures, which make up the actual program, is placed.

Below is an example of module organization

Example of a module organization

Option Base 1
Option Explicit

Private Const Pi = 3.14159265358979
Private Out(2) As Double

Private Function CircleLength(r As Double) As Double
    CircleLength = 2 * Pi * r
End Function

Private Function AreaDisc(r As Double) As Double
    AreaDisc = 4 * Pi * r ^ 2
End Function

Private Sub JointResult(r As Double)
    Out(1) = CircleLength(r)
    Out(2) = AreaDisc(r)
    MsgBox Out(1) & vbCr & Out(2)
End Sub

Private Sub ShowResults()
    JointResult 1
End Sub

NOTE
To test the program shown in Listing 2.29, do not forget to first set the Require Variable Declaration parameter on the Editor tab of the Options dialog box in the VBA editor (to open the Options dialog box, use the Tools | Options command in the Visual Basic integrated development environment).

A Sub procedure can be called from another procedure in several ways.

• First method of calling a Sub procedure:

Name <arguments>

Here:

• Name — the name of the called procedure.
• — the list of actual parameters, i.e., the list of values passed to the procedure. This list must match, in number and type, the list of parameters defined in the procedure declaration.
• Second method of calling a Sub procedure — using the Call statement:

Call Name (arguments)

Note that in this case the list of actual parameters must be enclosed in parentheses. In the first method, parentheses were not used.

By using named parameters, VBA allows you to enter actual parameters in any order and omit optional ones (Optional). In this case, after the parameter name you must place a colon and an equals sign (:=), followed by the parameter value (the actual argument).

The code demonstrates the main ways of passing parameters, using the ShowResults procedure from the previous section.

Main ways of passing parameters using the ShowResults procedure

Private Sub ShowResults()
    JointResult r:=1
End Sub

Private Sub ShowResults1()
    Dim Rs As Double
    Rs = 2
    JointResult Rs
End Sub

Private Sub ShowResults2()
    Call JointResult(4)
End Sub

The procedure parameter list <arguments> has the following syntax:

[Optional] [ByVal | ByRef] [ParamArray] variableName[()] _
    [As Type][= defaultValue]

• Optional — keyword indicating that the parameter is not required. When this element is used, all subsequent parameters in the <arguments> list must also be optional and must be described with the keyword Optional. All parameters described as Optional must have the type Variant. The keyword Optional cannot be used for any parameter if the keyword ParamArray is specified.
• ByVal — keyword indicating that the parameter is passed by value.
• ByRef — keyword indicating that the parameter is passed by reference. The ByRef description is used in VBA by default.
• ParamArray — keyword that can only be used as the last element in the <arguments> list, indicating that the final parameter is an optional array of values of type Variant. The ParamArray keyword allows you to specify an arbitrary number of parameters. It cannot be used together with the keywords ByVal, ByRef, or Optional.
• variableName — the name of the variable, which must follow the standard variable naming rules.
• Type — the data type of the parameter value passed into the procedure. Valid types:
  Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (only variable-length strings), Object, Variant.
  If the Optional keyword is not used, a user-defined type or an object type may also be specified.
• defaultValue — defines the value assigned to the parameter by default. Used only together with the Optional parameter. If the type is Object, the only default value allowed is Nothing.

In addition to the Sub procedure, VBA also provides the Function procedure (or simply, function).

[Public | Private | Friend] [Static] Function <FunctionName> _
    [(<arguments>)] [As Type]
    <instructions>
    <FunctionName> = expression
    [Exit Function]
    <instructions>
    <FunctionName> = expression
End Function

The syntax of a Function procedure contains the same elements as a Sub procedure. The instruction Exit Function causes an immediate exit from the function procedure.

Like a Sub, a function is an independent procedure that can accept parameter values, execute a sequence of instructions, and modify its parameter values. However, unlike a Sub, when it is necessary to use the value returned by a function, a Function procedure can be used on the right-hand side of an expression, just like any built-in function (for example, Cos).

A function procedure is called in an expression by its name, followed by a list of arguments enclosed in parentheses. To return a value from a function, assign a value to the function name. Any number of such assignment instructions may appear anywhere in the procedure.

In the code an example of a function F is shown, which calculates the sum of two values.

Example of a function

Sub DemoFun()
    MsgBox F(1, 3)
End Sub

Function F(x As Double, y As Double) As Double
    F = x + y
End Function

In Example of a function the value of the function

function1 = x^2 + sin(x + z)

is calculated for the given x and z, which can be entered into worksheet cells.

Listing 2.26. Example of a function

Function function1(x As Double, z As Double) As Double
    function1 = x ^ 2 + Sin(x + z)
End Function

Note that the user-defined functions you create become available in the list of functions when entering formulas into an Excel worksheet cell.

To use a function you have created, go to the Formulas tab on the Ribbon, and in the Function Library group, click the Insert Function button. In the Insert Function dialog box , select User Defined from the Category list, and then, in the Select a function field, specify the function you created.

Insert Function dialog box

A procedure is a self-contained part of the code that has a name and can contain parameters, execute a sequence of instructions, and modify the values of its parameters.

[Private | Public | Friend] [Static] Sub <ProcedureName> [(<arguments>)]
    <instructions>
    [Exit Sub]
    <instructions>
End Sub

• Private — keyword indicating that the procedure is private, and its scope is limited to the module.
• Public — keyword indicating that the procedure is public and accessible from all other procedures in all modules.
• Friend — keyword used only in a class module, indicating that the procedure is friendly and its scope is the entire project.
• Static — keyword indicating that the local variables of the procedure retain their values between calls of this procedure.
• <ProcedureName> — the name of the procedure, which must follow the standard variable naming rules.
• <arguments> — a list of parameters whose values are passed into the procedure or returned from the procedure when it is called. Parameters are separated by commas.
• <instructions> — any group of instructions (usually a set of statements) executed within the Sub procedure.
• Exit Sub — a statement that causes an immediate exit from the procedure.

In the following examples, there are two procedures, DemoSub1 and DemoSub2, located in different modules. The DemoSub2 procedure is declared as private, so a call to DemoSub1 generates an error.

Procedure DemoSub1 does not see procedure DemoSub2 (Same standard module)

Sub DemoSub1()
    DemoSub2
End Sub

Procedure DemoSub1 does not see procedure DemoSub2 (Another standard module)

Private Sub DemoSub2()
    Beep
    MsgBox "Call received"
End Sub

To avoid this error, the DemoSub2 procedure must be declared either as public or without a scope specification.

Procedure DemoSub1 now sees procedure DemoSub2 (Another standard module)

Public Sub DemoSub2()
    Beep
    MsgBox "Call received"
End Sub