CODEWITHSUNDEEP
vbaexcel
gioxc88
gioxc88

Reputation: 553

VBA function that returns an array in excel

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

Upvotes: 0

Views: 456

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

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.

Upvotes: 3

Related Questions