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

Question

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.

You defined Postavke as a String array.

Rory
– Rory

2019-11-21 10:36:53 +00:00
Commented Nov 21, 2019 at 10:36 — Rory
– Rory, Commented Nov 21, 2019 at 10:36
Note too that your array will be zero-based by default.

SJR
– SJR

2019-11-21 10:40:26 +00:00
Commented Nov 21, 2019 at 10:40 — SJR
– SJR, Commented Nov 21, 2019 at 10:40

FaneDuru · Accepted Answer · 2019-11-21 11:08:55Z

1

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.

edited Nov 21, 2019 at 11:08

answered Nov 21, 2019 at 10:56

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related