CODEWITHSUNDEEP
arraysexcelvbafunctionpass-by-reference
TE27
TE27

Reputation: 1

How to pass a dynamcally allocated array into a function in Excel VBA

I am very fresh with Excel VBA, so I ask you for understanding. Doing research on the internet has not resolved my problem. So I wish to pass an array of numerical values in VBA into a function, namely enter cash flows into an NPV function all in Excel VBA code.

Dim Postavke() As String, velikost As Integer, i As Integer

velikost = WorksheetFunction.CountA(Worksheets("List1").Columns(11))

ReDim Postavke(velikost)

For i = 1 To velikost
    Postavke(i) = Cells(i + 1, 11).Value
Next i

Now I want to pass the Postavke() array to the NPV function:

Dim NSV As Long

NSV = NPV(0.06, ByRef Postavke() As Long))

which always goes an error. Any thoughts to how this might be done?

Thank you in advance. Best regards.

Upvotes: 0

Views: 64

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Create the function in this way:

Function NPV(whatever As Double, Postavke() As String) As Long
    Dim x As Long
    'do whatever you want with your code, work on the 'x' variable and finally ends with:
    NPV = x
    'or define NPV earier and use Exit Function after..
End Function

You must decide if the Postavke() array must be declared as a string or a long. I kept your initial declaration...

And call the function like this:

NSV = NPV(0.06, Postavke())

In order to properly load velikost, starting with 1, you must add on top of your module code:

Option Base 1

Otherwise, you must take care (like @SJR remarked) that the array is zero-based by default.

Upvotes: 1

Related Questions