Avoid Loop over Arrays in VBA?

Question

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

There is no other way to do it in principle other than to use a loop. In some languages the loop might be hidden behind syntactic sugar better than in the others, in VBA it is not hidden at all and you have to enjoy it. If you want some sugar though, create a function that accepts a Variant array and an argument to add. — GSerg
– GSerg, Commented Nov 25, 2016 at 18:07
You can do it without, using the maths, using array formula, EXP(SUM(LN(B2:B30))) don't know if that helps? — Nathan_Sav
– Nathan_Sav, Commented Nov 25, 2016 at 18:08

RBarryYoung · Accepted Answer · 2016-11-26 18:02:06Z

4

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].

edited Nov 26, 2016 at 18:02

answered Nov 25, 2016 at 18:20

RBarryYoung

57.1k15 gold badges99 silver badges143 bronze badges

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

4 Comments

gioxc88 Over a year ago

I do love working with loops .. I just wanted to know if there was a more efficient way to do it ... thank you very much for the answers !!!

user3598756 Over a year ago

there are actually other ways to avoid loops (see other answers). Not sure which is the most efficient

RBarryYoung Over a year ago

@user3598756 I am sure which is the most efficient, as I have tried all of them at one time or another. As I stated above, usually the fastest way is 1) array-copy cells to vba array, 2) loops, then 3) array-paste vba array back into cells (which is what the OP is doing). There are specific cases where worksheet functions from vba (or even just formulas wo VBA) can be faster but it requires a very good direct match between that functionality and what you are trying to accomplish.

RBarryYoung Over a year ago

@user3598756 In your example, for instance, the OP asked for a vector of cumproduct(..), but you are returning just the final scalar result of the WorkSheet.Product function. It's not a direct match to what the OP wants and in order to make it so, you'd have to do a bunch of extra work.

Nathan_Sav · Accepted Answer · 2016-11-25 18:36:02Z

0

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

answered Nov 25, 2016 at 18:36

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Comments

user3598756 · Accepted Answer · 2016-11-26 08:38:04Z

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

Collectives™ on Stack Overflow

Avoid Loop over Arrays in VBA?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related