Calling a Procedure and Passing Parameter Values with Excel VBA

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