VBA Excel - passing an array into a function

Question

I want to write a function in which I can include an array. I imagine that the function will work like the function NPV, we have 3 ways of specifying the syntax in the formula bar:

1) NPV(0.5, A1:A5)
2) NPV(0.5, A1, A2, A3, A4, A5)
3) NPV(0.5, {10, 20, 23, 25, 27})

Where 10, 20, 23, 25, 27 are the value in range A1:A5.

And here is my function in VBA:

Function MyNpv(r As Double, Flows As Variant)

Dim i, n As Integer
Dim p As Double
n = Application.Count(Flows)
For i = 1 To n
 p = p + Flows(i) / (r + 1) ^ i
Next i

MyNpv = p

End Function

However, my own function can only work like:

MyNpv(0.5, A1:A5)

Is there anyway that I can declare my array so that it would work flexibly like the function NPV?

Thanks.

https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-parameter-arrays — SJR, Mar 26 '20 at 13:36
also see [HERE](https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell) for how I tested if it was a range or an array. — Scott Craner, Mar 26 '20 at 13:39
I tried Param Array and MyNpv(0.5, A1, A2, A3, A4, A5) works, but MyNpv(0.5, A1:A5) does not work anymore, and NPV(0.5, {10, 20, 23, 25, 27}) still does not work — tmtran99, Mar 26 '20 at 13:54
You need to loop the param array and test whether each part is a range or an array, then loop that. — Scott Craner, Mar 26 '20 at 13:57
Thanks for your reply, but I still can not get MyNpv(0.5, {10, 20, 23, 25, 27}) work, you can show me the syntax? — tmtran99, Mar 26 '20 at 14:06

Scott Craner · Answer 1 · 2020-03-26T14:56:34.937

Use a ParamArray, Loop that to test what is in each part of the array and then adjust accordingly:

Function MyNpv(r As Double, ParamArray Flows() As Variant)

    Dim FlowsPart As Variant

    Dim p As Double
    p = 0#

    Dim k As Long
    k = 1

    For Each FlowsPart In Flows
        Dim arr As Variant
        arr = FlowsPart

        Dim x As Long
        x = -1

        Dim y As Long
        y = -1

        On Error Resume Next
            x = UBound(arr, 2)
            y = UBound(arr)
        On Error GoTo 0

        Dim j As Long
        Dim i As Long
        If x >= 0 And y >= 0 Then
            For i = LBound(arr, 1) To UBound(arr, 1)
                For j = LBound(arr, 2) To UBound(arr, 2)
                    p = p + arr(i, j) / (r + 1) ^ k
                    k = k + 1
                Next j
            Next i
        ElseIf y >= 0 Then
            For j = LBound(arr) To UBound(arr)
                p = p + arr(j) / (r + 1) ^ k
                k = k + 1
            Next j
        Else
            p = p + arr / (r + 1) ^ k
            k = k + 1
        End If
    Next FlowsPart

    MyNpv = p

End Function

VBA Excel - passing an array into a function

1 Answers1