Excel VBA: Passing Arguments to Procedures, Parameter Arrays (ParamArray)
---------------------------------------------------------------------------------------------------------
Contents:
Passing Arguments By Reference
Pass an Arbitrary or Indefinite Number of Arguments - Parameter Arrays (ParamArray)
---------------------------------------------------------------------------------------------------------
When an external value is to be used by a procedure to perform an action, it is passed to the procedure by variables. These variables which are passed to a procedure are called arguments. An argument is the value supplied by the calling code to a procedure when it is called. When the set of parentheses, after the procedure name in the Sub or Function declaration statement, is empty, it is a case when the procedure does not receive arguments. However, when arguments are passed to a procedure from other procedures, then these are listed or declared between the parentheses.
It is usual to declare a data type for arguments passed to a procedure and if not specified, the default data type is variant.
Example 1
Function Grade(Marks As Integer) As String
'The declaration of the Grade function contains one variable as argument, with String as the return data type.
If Marks >= 80 Then
Grade = "A"
ElseIf Marks >= 60 Then
Grade = "B"
ElseIf Marks >= 40 Then
Grade = "C"
Else
Grade = "Fail"
End If
End Function
Sub callGrade()
Dim i As Integer
Dim str As String
i = InputBox("Enter Peter's marks")
'call the Grade function and the result is assgined to the local variable str:
str = Grade(i)
MsgBox "Peter's grade is " & str
End Sub
Example 2
Function marksPercent(Marks As Integer, TotalMarks As Integer) As Double
'The declaration of the marksPercent function contains two variables as arguments, with Double as the return data type.
marksPercent = Marks / TotalMarks * 100
marksPercent = Format(marksPercent, "#.##")
End Function
Sub callMarksGrades()
Dim Marks As Integer
Dim TotalMarks As Integer
Dim dPcnt As Double
Marks = InputBox("Enter Marks")
TotalMarks = InputBox("Enter Total Marks")
'call the marksPercent function and the result is assgined to the local variable dPcnt:
dPcnt = marksPercent(Marks, TotalMarks)
MsgBox "Percentage is " & dPcnt & "%"
End Sub
When you pass an argument by value in a procedure, only a copy of a variable is passed and any change in the value of the variable in the current procedure will not affect or change the variable in its original location because the variable itself is not accessed. To pass an argument by value, use the ByVal keyword before the argument to its left.
Example 3
Function computeCommission(ByVal commRate As Double, ByVal Sales As Currency) As Currency
'Passing an argument by value in a procedure using the ByVal keyword
'The declaration of the computeCommission function contains two variables as arguments, with Currency as the return data type.
computeCommission = commRate * Sales
End Function
Sub salesmanPeter()
Dim commPeter As Currency
Dim commRate As Double
Dim Sales As Currency
commRate = InputBox("Enter Commission Rate")
Sales = InputBox("Enter Sales Amount")
'call the computeCommission function and the result is assgined to the local variable:
commPeter = computeCommission(commRate, Sales)
MsgBox "Peter's commission is $" & commPeter
End Sub
Passing Arguments By Reference
When you pass an argument by reference in a procedure, the variable itself is accessed by the procedure in its location or address in memory. The value of the variable is changed permanently by the procedure in this case. To pass an argument by reference, use the ByRef keyword before the argument to its left. Passing arguments by reference is also the default in vba, unless you explicity specify to pass an argument by value.
Example 4 - comparing Passing Arguments By Value (Example 4a) vs Passing Arguments By Reference (Example 4b)
Example 4a - Passing an argument by value in a procedure using the ByVal keyword.
Function number(ByVal i As Integer) As Long
'Passing an argument by value in a procedure using the ByVal keyword
i = 5
number = i
End Function
Sub chkNumber()
'value of the variable n is set to 0 when it is declared:
Dim n As Integer
'the message returned is 5.
'the number function is called here which assigns value to the variable n. Because the variable was passed by value in the function, any change in the value of the variable is only in the current function and after the function ends the value of the variable n will revert to the value when it was declared where it was set to 0. If the variable had been passed by reference in the function, then the variable n would have permanently assumed the new assigned value.
MsgBox number(n)
'message returned is 0, because after the number function ends the value of the variable n reverts to the value when it was declared where it was set to 0:
MsgBox n
End Sub
Example 4b - Passing an argument by reference in a procedure using the ByRef keyword
Function number1(ByRef i As Integer) As Long
'Passing an argument by reference in a procedure using the ByRef keyword
i = 5
number1 = i
End Function
Sub chkNumber1()
'value of the variable n is set to 0 when it is declared:
Dim n As Integer
'message returned is 5 because calling the number1 function, assigns value to the variable n:
MsgBox number1(n)
'message returned is 5, because the variable has been passed by reference in the number1 function, and the variable n has permanently assumed the new assigned value by calling the number1 function in the preceding line of code:
MsgBox n
End Sub
Arguments can be specified as Optional by using the Optional keyword before the argument to its left. When you specify an argument as Optional, all other arguments following that argument to its right must also be specified as Optional. Note that specifying the Optional keyword makes an argument optional otherwise the argument will be required.
The Optional argument should be (though not necessary) declared as Variant data type to enable use of the IsMissing function which works only when used with variables declared as Variant data type. The IsMissing function is used to determine whether the optional argument was passed in the procedure or not and then you can adjust your code accordingly without returning an error. If the Optional argument is not declared as Variant in which case the IsMissing function will not work, the Optional argument will be assigned the default value for its data type which is 0 for numeric data type variables (viz. Integer, Double, etc) and Nothing (a null reference) for String or Object data type variables.
Example 5 - declaration of the sub-procedure contains two arguments, both specified as Optional.
Sub employeeName1(Optional firstName As String, Optional secondName As String)
'The declaration of the sub-procedure contains two arguments, both specified as Optional.
ActiveSheet.Range("A1") = firstName
ActiveSheet.Range("B1") = secondName
End Sub
Sub getFullName1()
Dim strName1 As String
Dim strName2 As String
strName1 = InputBox("Enter First Name")
strName2 = InputBox("Enter Second Name")
Call employeeName1(strName1, strName2)
End Sub
Example 6 - declare the Optional argument as String, without using the IsMissing function.
Sub employeeName2(firstName As String, Optional secondName As String)
'The declaration of the sub-procedure contains two arguments, the second argument is specified as Optional. Note that in this example because the Optional argument is declared as String and is not passed in the procedure, Range("B1") will contain a null reference (Nothing) after running the sub-procedure because the Optional argument will be assigned the default value for its data type (String) which is Nothing (a null reference). If the Optional argument is declared as Integer, Range("B1") will contain zero after running the sub-procedure because the Optional argument will be assigned the default value for its data type (Integer) which is zero.
ActiveSheet.Range("A1") = firstName
ActiveSheet.Range("B1") = secondName
End Sub
Sub getFullName2()
Dim strName1 As String
strName1 = InputBox("Enter First Name")
Call employeeName2(strName1)
End Sub
Example 7 - declare the Optional argument as Variant data type, and use the IsMissing function.
Sub divide1(firstNumber As Integer, Optional secondNumber As Variant)
'The declaration of the sub-procedure contains two arguments, the second argument is specified as Optional. The Optional argument should be (though not necessary) declared as Variant data type to enable use of the IsMissing function which works only when used with variables declared as Variant data type. The IsMissing function is used to determine whether the optional argument was passed in the procedure or not and then you can adjust your code accordingly without returning an error.
Dim dResult As Double
If IsMissing(secondNumber) Then
dResult = firstNumber
Else
dResult = firstNumber / secondNumber
End If
dResult = Format(dResult, "#.##")
MsgBox dResult
End Sub
Sub getDivide1()
Dim iNumber1 As Integer
iNumber1 = InputBox("Enter First Number")
Call divide1(iNumber1)
End Sub
Example 8 - declare the Optional argument as Integer, but the IsMissing function will not work and the code will return an error.
Sub divide2(firstNumber As Integer, Optional secondNumber As Integer)
'The declaration of the sub-procedure contains two arguments, the second argument is specified as Optional. The Optional argument is declared as Integer, but the IsMissing function will not work with a variable not declared as Variant data type. Hence the Optional argument will be assigned the default value for its data type which is 0 for numeric data types and the code will return the error: Run-time error "11": Division by zero.
Dim dResult As Double
If IsMissing(secondNumber) Then
dResult = firstNumber
Else
dResult = firstNumber / secondNumber
End If
dResult = Format(dResult, "#.##")
MsgBox dResult
End Sub
Sub getDivide2()
Dim iNumber1 As Integer
iNumber1 = InputBox("Enter First Number")
Call divide2(iNumber1)
End Sub
Example 9 - declare the Optional argument as String, but the IsMissing function will not work and the code will return an error.
Sub divide3(firstNumber As Integer, Optional secondNumber As String)
'The declaration of the sub-procedure contains two arguments, the second argument is specified as Optional. The Optional argument is declared as String, but the IsMissing function will not work with a variable not declared as Variant data type. Hence the Optional argument will be assigned the default value for its data type which is Nothing (a null reference) for String data type and the code will return the error: Run-time error "13": Type mismatch.
Dim dResult As Double
If IsMissing(secondNumber) Then
dResult = firstNumber
Else
dResult = firstNumber / secondNumber
End If
dResult = Format(dResult, "#.##")
MsgBox dResult
End Sub
Sub getDivide3()
Dim iNumber1 As Integer
iNumber1 = InputBox("Enter First Number")
Call divide3(iNumber1)
End Sub
Specifying a Default Value for an Optional Argument
You can specify a Default value for an Optional argument which will be used if the Optional argument is not passed to the procedure. This way you can declare optional arguments of any data type and specify a default value which will be used if the Optional argument is omitted, obviating the use of the IsMissing function which works only with the Variant data type.
Example 10 - if an Optional argument is not passed in the procedure then a default value is used.
Sub divide(firstNumb As Integer, Optional secondNumb As Integer = 3)
'The declaration of the sub-procedure contains two arguments, the second argument is specified as Optional. If the second argument, which is Optional, is not passed in the procedure then a default value is specifiedand used:
Dim dResult As Double
dResult = firstNumb / secondNumb
dResult = Format(dResult, "#.##")
MsgBox dResult
End Sub
Sub getCalculation3()
Dim iNumber1 As Integer
iNumber1 = InputBox("Enter First Number")
Call divide(iNumber1)
End Sub
Pass an Arbitrary or Indefinite Number of Arguments - Parameter Arrays (ParamArray)
We have explained above how to declare procedures by passing arguments, including optional arguments, but nevertheless you are limited to the fixed number of arguments as declared in the procedure. By using the ParamArray keyword you will be allowed to pass an arbitrary number of arguments to the procedure so that the procedure will accept an indefinite number of arguments or no argument at all. Use a ParamArray when you are not sure of the precise number of arguments to pass in a procedure at the time you define it. It might be convenient to create an optional array (ie. a ParamArray) than going through the hassle of declaring a large number of optional arguments, and then using the IsMissing function with each of them.
A procedure uses information in the form of variables, constants & expressions to perform actions whenever it is called. The procedure's declaration defines a parameter which allows the calling code (code which calls the procedure) to pass an argument or value to that parameter so that every time the procedure is called the calling code may pass a different argument to the same parameter. A Parameter is declared like a variable by specifying its name and data type. By declaring a parameter array, a procedure can accept an array of values for a parameter. A parameter array is so defined by using the ParamArray keyword.
Only one ParamArray can be defined in a procedure and it is always the last parameter in the parameter list. A ParamArray is an optional parameter and it can be the only optional parameter in a procedure and all parameters preceding it must be required. ParamArray should be declared as an array of Variant data type. Irrespective of the Option Base setting for the module, LBound of a ParamArray will always be 0 ie. index values for the array will start from 0. ByVal, ByRef or Optional keywrods cannot be used with ParamArray.
Define a procedure to accept an indefinite number of arguments or values ie. a parameter array: Use the ParamArray keyword to precede the parameter name, which must be the last parameter in the procedure declaration. An empty pair of parentheses should follow the parameter array name to be declared as a Variant data type with the customary As clause. Do not specify a default value after the As clause.
To access a parameter array's value: use the UBound function to determine the array length which will give you the number of elements or index values in the array. In the procedure code you can access a parameter array's value by typing the array name followed by an index value (which should be between 0 and UBound value) in parantheses
Example 11 - Pass an arbitrary number of arguments to the procedure using a ParamArray parameter.
Sub addNums(ParamArray numbers() As Variant)
'a procedure declaration that allows to pass an arbitrary number of arguments to the procedure using a ParamArray parameter.
Dim lSum As Long
Dim i As Long
'LBound of a ParamArray is always 0. Each element of the ParamArray is added here:
For i = LBound(numbers) To UBound(numbers)
lSum = lSum + numbers(i)
Next i
MsgBox lSum
End Sub
Sub getAddNums()
'you can pass an arbitrary or indefinite number of arguments in a procedure using ParamArray:
Call addNums(22, 25, 30, 40, 55)
End Sub
Example 12 - One argument is required and then allow to pass an arbitrary number of arguments to the procedure using a ParamArray parameter.
Sub calcComm(comm As Double, ParamArray sales() As Variant)
'a procedure declaration with one required argument (comm) and then allows to pass an arbitrary number of arguments to the procedure using a ParamArray parameter.
Dim dTotalComm As Double
Dim v As Variant
'Commission for each element of the ParamArray is computed and added here:
For Each v In sales
MsgBox v
dTotalComm = dTotalComm + comm * v
Next v
MsgBox dTotalComm
End Sub
Sub getComm()
Dim d As Double
d = 0.3
'you can pass an arbitrary or indefinite number of arguments in a procedure using ParamArray:
Call calcComm(d, 100, 200, 300)
End Sub
Example 13 - Two arguments are required and then allow to pass an arbitrary number of arguments to the procedure using a ParamArray parameter.
Sub avgMarksGrades(student As String, avg As Double, ParamArray marks() As Variant)
'a procedure declaration with two required arguments (student & avg) and then allows to pass an arbitrary number of arguments to the procedure using a ParamArray parameter.
Dim v As Variant, vSum as variant
Dim strGrade As String
Dim strAllGrades As String
For Each v In marks
If v >= 80 Then
strGrade = "Excellent"
ElseIf v >= 60 Then
strGrade = "Good"
ElseIf v >= 40 Then
strGrade = "Average"
Else
strGrade = "Fail"
End If
If Len(strAllGrades) = 0 Then
strAllGrades = strGrade
Else
strAllGrades = strAllGrades & ", " & strGrade
End If
Next v
For Each v In marks
i = i + 1
vSum = vSum + v
avg = vSum / i
Next v
strAllGrades = """" & strAllGrades & """"
avg = Format(avg, "#.##")
MsgBox student & " has grades of " & strAllGrades & " and average marks of " & avg
End Sub
Sub getAvgMarksGrades()
Dim strName As String
Dim dAverage As Double
strName = "Peter"
'you can pass an arbitrary or indefinite number of arguments in a procedure using ParamArray:
Call avgMarksGrades(strName, dAverage, 80, 45, 65)
End Sub
