CODEWITHSUNDEEP
vba
gioxc88
gioxc88

Reputation: 553

Avoid Loop over Arrays in VBA?

my goal is to write a function that converts returns to prices. I have a vector of returns stored inside a range in excel like this:

r1
r2
...
rn

Now suppose that these returns are stored in Column B. In VBA wrote the following code

Dim r As Range

Set r = ThisWorkbook.Sheets("Foglio1").Range("B2:B" & _
ThisWorkbook.Sheets("Foglio1").Range("B" & Rows.Count).End(xlUp).Row) 

Dim temp() As Variant   
temp = r

So I succesfully assigned the value r1, r2, ..., rn to an array that I called temp.

Now if I were in R or MATLAB I would have done the following, in order to convert return to prices:

temp = cumprod(1 + temp)

with one line of command I would have converted returns to prices

(1 + temp) should sum 1 to each element of array and cumprod should return me a vector with the cumulative product.

Is it possible that to achieve the same result I am forced to use for loop in VBA?

thank you very much for your time have a great week end

Upvotes: 1

Views: 1519

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56735

Yes the only way to do this directly in VBA is with loops.

It is also possible to do it indirectly in VBA by using Excel Worksheet functions, but its actually usually faster to copy the range into a VBA array as you are doing and then process it with loops.

You can also write (or find and download) libraries that have callable functions and subroutines to hide the Loops from you, but they're still doing the loops.

As one comment said "Learn to love the loops". That's just how it works in VBA.

Ironically, I think the actual fastest way to do this would be to add a new column, let's say starting at Z2 that had Z2=B2+1 and every other row/cell was Z*=(B*+1)*Z[*-1].

Upvotes: 4

user3598756
user3598756

Reputation: 29421

there's actually a way exploiting PasteSpecial() method of Range object and WorksheetFunction.Product() method:

Function CumulativeDiscount(discountsRng As Range) As Double
    With discountsRng
        .Copy
        With .Offset(, .Parent.UsedRange.Columns.Count)
            .Value = 1
            .PasteSpecial , Operation:=xlPasteSpecialOperationAdd
            Application.CutCopyMode = False
            CumulativeDiscount = WorksheetFunction.Product(Application.Transpose(.Cells))
            .ClearContents
        End With
    End With
End Function

that you could use in your "main" code as follows:

Sub main()
    With ThisWorkbook.Sheets("Foglio1")
        MsgBox CumulativeDiscount(.Range("B2", .Cells(.Rows.Count, "B").End(xlUp)))
    End With
End Sub

the only limitation being WorksheetFunction.Product() accepts up to 30 arguments, i.e. the maximum number of discounts to be multiplied is 30

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

You could do with SQL maybe?

This worked for my testing

Public Function PRODUCT_FUNCTION(strRange As String)

Dim c As ADODB.Connection
Dim r As ADODB.Recordset

strInputFile = ThisWorkbook.FullName
Set c = New ADODB.Connection

strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strInputFile & ";" & _
                            "Extended Properties=""Excel 12.0 Xml;HDR=No"";"

c.ConnectionString = strConnectionString
c.Open

strsql = "Select Exp(Sum(Log([F1]))) from [Sheet1$" & strRange & "]"

Set r = New ADODB.Recordset

r.Open strsql, c, 1

PRODUCT_FUNCTION = r.Fields(0).Value

r.Close
c.Close

Set r = Nothing
Set c = Nothing

End Function

Upvotes: 0

Related Questions