In this article I will cover the following topics:

• • Passing Arrays as Input Parameters to Functions and Subs ByRef
  • Passing Arrays as Input Parameters to Functions and Subs ByVal
  • Getting the dimension of the Input Array to Function or Sub:
  • Declaring Functions With an Array as the Return Type:

Passing Arrays as Input Parameters to Functions and Subs ByRef:

The code below creates the array arrInputIntegers. Populates the array with the values 1, 2, 3, …, 10. Passes the array to the Sub Example1. Sub Example1 prints the array values to column A:

Sub main()
Dim arrInputInteger(1 To 10) As Integer
Dim i As Integer
'populates the array with the values 1 to 10
For i = 1 To 10
    arrInputInteger(i) = i
Next i
'call the function example 1 with arrIntegers as an input parameter
Call Example1(arrInputInteger)
End Sub

'prints the values in arrIntegers to column A
Sub Example1(ByRef arrIntegers() As Integer)
Dim i As Integer
For i = LBound(arrIntegers) To UBound(arrIntegers)
    Cells(i, 1) = arrIntegers(i)
Next i
End Sub


Note: You cannot set the size of the array input array in the declaration of the Sub or Function.

Note: The syntax for declaring a Function with an input parameter of the array data type is the same as that of a Sub.

Passing Arrays as Input Parameters to Functions and Subs ByVal:

Unlike other data types passing arrays ByVal is not as straight forward. The following declaration would generate a syntax error:

Sub Example1(ByVal arrIntegers() As integer)
Dim i As Integer
For i = LBound(arrIntegers) To UBound(arrIntegers)
arrIntegers(i) = 12341
Next i
End Sub


In order to pass an array as an input parameter to a Sub or Function,  ByVal you would need to use the Variant data type:

Sub main()
Dim arrInputInteger(1 To 10) As Integer
Dim i As Integer
'populates the array with the values 1 to 10
For i = 1 To 10
    arrInputInteger(i) = i
Next i
'call the function example 1 with arrIntegers being ByVal
Call Example2(arrInputInteger)
For i = 1 To 10
    Cells(i, 1) = arrInputInteger(i)
Next i
End Sub

'modifies a copy of the input parameter
Sub Example2(ByVal arrIntegers As Variant)
Dim i As Integer
'modifies the copy of the input parameter
For i = LBound(arrIntegers) To UBound(arrIntegers)
arrIntegers(i) = 12341
Next i
End Sub


Sub Example2 takes a copy of arrIntegers as the input paramter and modifies the copy. As you can see below the changes made to the copy had no effect on the original array:

Getting the Dimension of the Input Array to a Function or Sub:

As mentioned in the previous sections when declaring a sub or functions with input parameters of type array you can’t define its size or dimensions.  Therefor the size and dimensions of the array can only be obtained at runtime. I have explained how you can get the size of the array in the article VBA, Working With Array. Getting the dimension of the array is not as straight forward. In order to get the dimensions of an array you would need to use an error handler. The code below explains this:

Sub main()
'1 dimensional array
Dim arrInteger1D(1 To 4) As Integer
Call Example3(arrInteger1D)
End Sub

Sub Example3(ByRef arrIntegers() As Integer)
Dim intTemp As Integer

On Error GoTo lblError:
intTemp = UBound(arrIntegers, 2)
'if there was no error
MsgBox ("The Input Array is 2 dimensional")
Exit Sub

lblError:
Err.Clear
MsgBox ("The Input Array is 1 dimensionsal")
End Sub


arrInteger1D is a 1 dimensional array. After passing it to the Sub Example3, the Sub tries to get the upper bound of the second dimension. If the array is 1 dimensional then an exception will be thrown. The error handler will catch the array and display a message box stating that the array was 1 dimensional. Since the previous code uses a 1 dimensional array, the exception will be thrown, therefore the message box below will be shown:

The code below uses a 2 dimensional array therefor an expetion will not be thrown and the result will be:

Sub main()
Dim arrInteger(1 To 4, 1 To 3) As Integer
'call the function with a two dimensional array as intput
Call Example3(arrInteger)
End Sub



Declaring Functions With an Array as the Return Type:

The  code below declares a function with an array as the return type:

Function Example4() As Integer()
Dim arrInteger(1 To 10) As Integer
Dim i As Integer
For i = 1 To 10
    arrInteger(i) = i
Next i
Example4 = arrInteger
End Function

See also:

• VBA, Working With Arrays
• VBA Call Sub and Functions

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com