VBA function that returns an array in excel

Question

hello guys I hope you can help me with this one. I wrote a simple function to convert returns to prices. It is useless, but I will use it explain my problem. The function works. It does what I want.

Function ret2prices(ByVal r As Range)

    Dim temp() As Variant
    temp = r

    Dim prices() As Variant
    ReDim prices(1 To (UBound(temp, 1) + 1), 1)
    prices(1, 1) = 1

    For i = 2 To UBound(prices, 1)
        prices(i, 1) = 1 + temp(i - 1, 1)
        prices(i, 1) = prices(i - 1, 1) * prices(i, 1)
    Next i

    ret2prices = prices

End Function

the problem is that when I use it in excel worksheet it always returns 0. I would like to be able to use it the same way I use MMULT with CTRL + SHIFT + ENTER. Any suggestion?

thank you very much for your time

Second dimension of prices should be 1 To 1, now it's 0 To 1 and function returns array with 2 columns. — BrakNicku
– BrakNicku, Commented Nov 26, 2016 at 13:06
try to use your array for a range with 2 columns with CSE and you will see that @BrakNicku is right — Dirk Reichel
– Dirk Reichel, Commented Nov 26, 2016 at 13:34
Change appropriate line to ReDim prices(1 To (UBound(temp, 1) + 1), 1 To 1) — BrakNicku
– BrakNicku, Commented Nov 26, 2016 at 14:32
Ohhh .. what an idiot ... I completely forgot that I had to specify the starting index for the second dimension too .. Now it works just fine. Thank you very much ... since we are here do you know if it is possible to refer to an entire column or row of an array ? — gioxc88
– gioxc88, Commented Nov 26, 2016 at 14:46

BrakNicku · Accepted Answer · 2016-11-26 15:20:25Z

3

Arrays in VBA are 0-based, so:

ReDim prices(1 To (UBound(temp, 1) + 1), 1)

is equivalent to

ReDim prices(1 To (UBound(temp, 1) + 1), 0 To 1)

The code in question returned expected results, but in the second column of result array. Changing lower bound of second dimension to 1 fixes the problem.

answered Nov 26, 2016 at 15:20

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

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

1 Comment

John Coleman Over a year ago

Option Base 1 would also fix it. I general I don't like Option Base 1, but if all arrays in a given module are designed to transfer data between VBA and ranges, it is perhaps a valid use case.

Collectives™ on Stack Overflow

VBA function that returns an array in excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related