Using an Array as a Procedure Parameter with Excel VBA

In VBA, it is possible to use an array as a procedure parameter. In this case, the array used as a parameter must be declared as dynamic in the procedure definition.

In the following example, the procedure SumM returns the sum of the elements of the array, which is passed as its first parameter.

Using an array as a procedure parameter

Option Base 1
Sub SumM(ByRef A() As Double, ByRef s As Double)
    Dim x As Variant
    s = 0
    For Each x In A
        s = s + x
    Next
End Sub

Sub DemoSumM()
    Dim B(2, 2) As Double
    Dim s As Double
    B(1, 1) = 1 : B(1, 2) = 5
    B(2, 1) = 4 : B(2, 2) = 5
    SumM B, s
    MsgBox s
    ' Result: 15
End Sub