We know 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

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.

• 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

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.

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

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

1. 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

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

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.