understanding arrays

[choubix]

hello,
I have the following code that generates random prices. (code is not mine)
I don't exactly get how the array works (it's my first time with an array in vba)

it seems that the array "Frequency" is initially created.
then it is dimensioned to get 100 values.

then the array is set from 1 to 100 (still empty)
then I don't get he portion of code "For i = 1 to iteration..."
because in the end the array returns in "Example3Result" the correct value of FinalPrice (when it was manipulated in the loop quiet a few time...)

hope someone can explain that to me.
thx!
[VBA]

Dim objSim As Object
Dim CurrentPrice As Double, Volatility As Double, ExpectedReturn As Double
Dim Days As Integer
Dim Iterations As Double, FirstPrice As Double, FinalPrice As Double
Dim Frequency()
Dim i As Single, Gap As Single
Const num = 100
ReDim Frequency(num)
CurrentPrice = Range("Example3Inputs").Cells(1)
Volatility = Range("Example3Inputs").Cells(2)
Days = Range("Example3Inputs").Cells(3)
ExpectedReturn = Range("Example3Inputs").Cells(4)
Iterations = Range("Example3Inputs").Cells(5)
Gap = Range("Gap")
FirstPrice = Range("Example3Results")

For i = 1 To num
Frequency(i) = 0
Next

Set objSim = CreateSimulationObject

For i = 1 To Iterations
FinalPrice = objSim.NextPrice(CurrentPrice, Volatility, ExpectedReturn, Days, 0)
FinalPrice = FinalPrice - FirstPrice + 1
FinalPrice = ((FinalPrice / Gap)) + 1
If FinalPrice >= 1 And FinalPrice < num Then
Frequency(FinalPrice) = Frequency(FinalPrice) + 1
End If
Next

For i = 1 To num
Range("Example3Results").Cells(i, 2) = Frequency(i)
Next
End Sub
[/VBA]

[Bob Phillips]

Iterations is a variable that is populated from a cell, so that determines the loop range.

When dropping an array onto a range, it only loads as many items as in the range.

[choubix]

i understood the 2nd loop (iterations)
what i don't get is: how useful is it to loop the array in the first place?

waht i also don't get is: how come in the 3rd loop frequency(i) returns FinalPrice as a result of : [vba]objSim.NextPrice(CurrentPrice, Volatility, ExpectedReturn, Days, 0) [/vba]and not as a result of all the calculation that are made on lines: [vba] FinalPrice = FinalPrice - FirstPrice + 1
FinalPrice = ((FinalPrice / Gap)) + 1 [/vba]

and what does this do to the array? [VBA]Frequency(FinalPrice) = Frequency(FinalPrice) + 1[/VBA]

thanks

[Bob Phillips]

It has to loop as it works out the next price in each iteration.

What evidence do you have for that follow-up statement, the code says otherwise.

[choubix]

evidence: what shows up on my screen when I run the macro

Let's say I run the macro with iterations =1, Gap = 2, FirstPrice = 90

here, I get 105.39

[VBA]FinalPrice = objSim.NextPrice(CurrentPrice, Volatility, ExpectedReturn, Days, 0)[/VBA]

here: 82.38
[VBA]FinalPrice = FinalPrice - FirstPrice + 1[/VBA]
here: 42.20
[VBA]FinalPrice = ((FinalPrice / Gap)) + 1[/VBA]

so I'd say that from now on, FinalPrice should be 42.20

then when it comes to the last loop

[VBA] For i = 1 To num
Range("Example3Results").Cells(i, 2) = Frequency(i)
Next[/VBA]

all cells are equal to 0 BUT Range("Example3Results").Cells(40, 2) that contains 1 (for infoRange("Example3Results").Cells(40, 1) = 106)

the simulation returns a table used to build a distribution curve.
meaning that:
Range("Example3Results").Cells(i, 1) = values
Range("Example3Results").Cells(i, 2) = frequency for a given value

[Bob Phillips]

It seems the previous loop loads the array not based upon the loop index, i, but rather the FinalPrice. Is it correct? I don't know, not my code.

I don't understand this line ...

all cells are equal to 0 BUT Range("Example3Results").Cells(40, 2) that contains 1 (for infoRange("Example3Results").Cells(40, 1) = 106)