PDA

View Full Version : understanding arrays



choubix
06-25-2008, 03:35 AM
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!


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

Bob Phillips
06-25-2008, 03:55 AM
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
06-25-2008, 04:10 AM
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 : objSim.NextPrice(CurrentPrice, Volatility, ExpectedReturn, Days, 0) and not as a result of all the calculation that are made on lines: FinalPrice = FinalPrice - FirstPrice + 1
FinalPrice = ((FinalPrice / Gap)) + 1

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

thanks

Bob Phillips
06-25-2008, 04:14 AM
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
06-25-2008, 04:34 AM
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

FinalPrice = objSim.NextPrice(CurrentPrice, Volatility, ExpectedReturn, Days, 0)

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

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

then when it comes to the last loop

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

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
06-25-2008, 05:02 AM
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)