I'm a long time lurker, first time poster to Ozgrid. So, hello all, and thanks for all the help to date.

My question is around how to improve my code so that it takes less time to run.

I have a UDF that uses a for each statement to calculate an array. Here is the code:

```
Public Function FRONGELLO(Current As Variant, portreturns As Variant, BenchReturn As Variant, Previous As Variant) As Variant
'Variable declaration
Dim frong1 As Variant
Dim frong2 As Variant
Dim frong3 As Variant
Dim prod As Variant
Dim r As Range
'Set Array to 1
ArrayCalc = 1
'calculates array as {PRODUCT(1+periodic portfolio returns)}
For Each r In portreturns
ArrayCalc = ArrayCalc * (r.Value + 1)
Next
'Links prior periods to current period
frong1 = CDec(Current * ArrayCalc)
frong2 = CDec(BenchReturn * Previous + Previous)
frong3 = CDec(frong1 + frong2)
'displays value of formula
FRONGELLO = CDec(frong3)
End Function
```

Display More

I can accomplish this more quickly with an array formula in Excel, but then I lose the ability of the user to interpret the fact that this formula is a specific algorithm (known as the frongello alogrithm). Also, if a user wants to use the algorithm and this formula isn't defined, then they have to know how to use an array formula...easier said than done in this crowd...

Really, this code could probably be improved in two ways:

1) is there a better way to have the formula evaluate as a decimal? I realize that declaring my variables as variant is taking up more space than is probably neccesary, but I am not sure of the proper method

2) is there a more efficient way to write an array formula in VBA that mimics {=PRODUCT(1+"range")}

I have been an Excel user for a long time, but I am fairly new to VBA, so any help would be appreciated. I have been reading several programming guides to get up to speed (and Ozgrid obviously!)

--Jeremy