Passing Arguments

So far, the procedures that you've seen do the exact same thing every time that they're executed. But that doesn't have to be the case. You can use a procedure argument to pass information to a procedure. When you define a procedure (by using either the Sub keyword or the Function keyword), you can also specify a list of arguments that the procedure will accept. Here's a sub procedure that accepts a single integer argument:

 Sub Procedure14(intInput As Integer)   ' Double the input argument and print the result   Debug.Print (intInput * 2) End Sub 

The declaration of Procedure14 specifies that you must pass it a single value of the Integer data type. This value is assigned to the variable named intInput, which is available within the procedure. The procedure prints the result of multiplying this value by two. Figure 4.4 demonstrates that calling the procedure with different arguments prints different values.

Figure 4.4. Calling a procedure with a single argument.

For a list of the data types that you can use for arguments, see "VBA Data Types," p. 39.

A procedure can have more than one argument. Here's a function procedure that accepts two string arguments and then returns the result of concatenating them together:

 Function Procedure15(strIn1 As String, strIn2 As String)   ' Concatenate a pair of strings   Procedure15 = strIn1 & strIn2 End Function 

NOTE

The concatenation operator, &, takes two strings and combines them into a single string.

When you declare more than one argument for a procedure, separate the declarations by commas.

Using Optional Arguments and Default Values

You can specify that some or all the arguments to a procedure are optional. For example, here's a procedure that accepts a single optional argument:

 Sub Procedure16(Optional strIn As String)   ' Print the input argument, if any   Debug.Print (strIn) End Sub 

As Figure 4.5 shows, you can call this procedure with or without an argument value. If you don't supply a value, there's simply nothing assigned to the corresponding argument within the procedure.

Figure 4.5. Calling a procedure with an optional argument.

An optional argument can also have a default value, which is used if the user does not supply a value when calling the procedure. Consider this procedure:

 Sub Procedure17(Optional strIn As String = "Missing")   ' Print the argument or a default value   Debug.Print strIn End Sub 

If you don't supply a value for the strIn argument when you call the procedure, it is assigned the default value "Missing".

NOTE

Passed values are treated like any other value, meaning that you must delimit them properly. For instance, strIn in the accompanying example is a String value. You must enclose the actual value in quotation marks in the procedure call to pass it properly to the procedure.

Passing Arguments by Reference

By default, arguments in VBA are passed by reference. This means that when you pass a variable from one procedure to another, the called procedure is working with the exact same copy of the variable as the calling procedure. This example shows how passing by reference works:

 Sub Procedure18()   ' Demonstrate passing by reference   Dim i As Integer   i = 5   ' Pass by reference to another procedure   Procedure19 i   ' And print the result   Debug.Print i End Sub Sub Procedure19(intInput As Integer)   intInput = 12 End Sub 

If you run Procedure18 from the Immediate window, you'll see that the ultimate result is 12. That's because the actual variable named i is passed to Procedure19, where its value is changed. The variable retains the changed value when control returns to Procedure18.

TIP

When passing arguments to a sub procedure, you don't need to enclose the arguments in parentheses.

Passing arguments by reference is fast, but it can have unintended side effects if you aren't expecting the called procedure to have an effect on the calling procedure. That's why there's an alternative: passing arguments by value.

Passing Arguments by Value

When you pass a variable from one procedure to another by value, VBA makes a copy of the variable and hands the copy to the called procedure. Thus, any changes made by the called procedure don't affect the variable in the calling procedure. You use the ByVal keyword to indicate that you're passing a variable by value. Here's the previous example, changed to pass the variable by value:

 Sub Procedure20()   ' Demonstrate passing by value   Dim i As Integer   i = 5   ' Pass by reference to another procedure   Procedure21 i   ' And print the result   Debug.Print i End Sub Sub Procedure21(ByVal intInput As Integer)   intInput = 12 End Sub 

Now if you run Procedure20 from the Immediate window, it prints the value 5. The change made to the variable in the called procedure, Procedure21, affects only a copy of the variable instead of the original.