dustarook
04-02-2010, 08:17 PM
Ok, so i'm writing a sub in excel that calculates price volatility incrementally through a series of data. an earlier, nearly identical program runs just fine. I understand this error {Invalid procedure call or argument (Error 5)} indicates that the number is out of range but i'm just not sure how to correct it. attached is a picture of what the spreadsheet looks like, along with some identifying notes. the code is as follows:
Sub Calc_FieldVolatility()
Dim HorCount As Integer, HorMax As Integer, VerCount As Integer, VerMax As Integer, PerLength As Integer, rgOffset1 As Integer, rgOffset2 As Integer
Dim ChangeSquareSum As Double, PercentSum As Double
'The Range ("Hor_Max") is actually indicative of the vertical max for each column,oops...
'VolPerCount = Range("Volatility_PerCount")
Range("FieldClearer").ClearContents
ChangeSquareSum = 0
PercentSum = 0
HorCount = 0
Do While HorCount < 26
HorCount = Range("HorizontalCount")
VerMax = Range("Day_10").Offset(1, HorCount)
Do While VerCount < VerMax
VerCount = Range("VolFieldStart").Offset(-1, HorCount)
PerLength = Range("Day_10").Offset(0, HorCount)
rgOffset1 = VerCount * PerLength
rgOffset2 = VerCount * PerLength + PerLength
ChangeSquareSum = 0
PercentSum = 0
For rgOffset1 = rgOffset1 To rgOffset2
'This loop sums each part of an array divided into sections equal to the adjusted period length
ChangeSquareSum = ChangeSquareSum + Range("Changed_Sqr").Offset(rgOffset1, 0).Value
PercentSum = PercentSum + Range("Percent_Change").Offset(rgOffset1, 0).Value
Next rgOffset1
ChangeSquareSum = (Sqr((ChangeSquareSum - ((PercentSum) ^ 2) / PerLength) / (PerLength - 1))) * Sqr(252 / PerLength)
Range("VolFieldStart").Offset(VerCount, HorCount).Value = ChangeSquareSum
Loop
Loop
End Sub
PLEASE HELP?!?!
Sub Calc_FieldVolatility()
Dim HorCount As Integer, HorMax As Integer, VerCount As Integer, VerMax As Integer, PerLength As Integer, rgOffset1 As Integer, rgOffset2 As Integer
Dim ChangeSquareSum As Double, PercentSum As Double
'The Range ("Hor_Max") is actually indicative of the vertical max for each column,oops...
'VolPerCount = Range("Volatility_PerCount")
Range("FieldClearer").ClearContents
ChangeSquareSum = 0
PercentSum = 0
HorCount = 0
Do While HorCount < 26
HorCount = Range("HorizontalCount")
VerMax = Range("Day_10").Offset(1, HorCount)
Do While VerCount < VerMax
VerCount = Range("VolFieldStart").Offset(-1, HorCount)
PerLength = Range("Day_10").Offset(0, HorCount)
rgOffset1 = VerCount * PerLength
rgOffset2 = VerCount * PerLength + PerLength
ChangeSquareSum = 0
PercentSum = 0
For rgOffset1 = rgOffset1 To rgOffset2
'This loop sums each part of an array divided into sections equal to the adjusted period length
ChangeSquareSum = ChangeSquareSum + Range("Changed_Sqr").Offset(rgOffset1, 0).Value
PercentSum = PercentSum + Range("Percent_Change").Offset(rgOffset1, 0).Value
Next rgOffset1
ChangeSquareSum = (Sqr((ChangeSquareSum - ((PercentSum) ^ 2) / PerLength) / (PerLength - 1))) * Sqr(252 / PerLength)
Range("VolFieldStart").Offset(VerCount, HorCount).Value = ChangeSquareSum
Loop
Loop
End Sub
PLEASE HELP?!?!