軟體:excel vba
版本:2010
各位版大好，以下有一vba程式，目的是要抓取3萬筆左右的交易價格進行統計分析(交易
價格可隨機設為1000~10000之間的值)。
我的問題是，程式中有一段 For zzz = 0.95 To 1 Step k_gap，如果我將0.95改為0.9，
則原本百分比介於0.95~1的資料就沒有跑出來，可是做迴圈時zzz卻會取到1，覺得很困惑
，系望各位大大解惑，謝謝。
Sub slope_analysis()
Dim k_gap, percentile_lbound_value, percentile_ubound_value
Dim ary0(33000), ary1(33000), ary2(33000), ary3(3500), ary4(3500), _
ary5(30), ary6(30), ary7(500, 3)
Dim i&, j&, k&, m&, n&, p&
'60=3個月；120=6個月；180=9個月；250=1年；500=2年；750=3年；1000=4年；_
1250=5年
'1500=6年；1750=7年；2000=8年；2250=9年；2500=10年
k_gap = 0.05
j = 1
For i = 1 To 33000
If Cells(6 + i, 5) <> "" Then
ary0(j) = Cells(6 + i, 5)
j = j + 1
End If
Next
'zzz=百分比上限;xxx=歷史天數;yyy=未來天數
For zzz = 0.95 To 1 Step k_gap
For xxx = 250 To 2500 Step 250
n = 0
Erase ary5, ary6
For yyy = 625 To 1250 Step 125
'若第i日前x天且後y天不超過資料範圍，則分別寫入第i日前x天與 _
後y天的報酬率
k = 0
m = 0
Erase ary1, ary2, ary3, ary4
For i = 1 To j - 1
If i - xxx > 0 And i + yyy <= j - 1 Then
ary1(i) = (ary0(i) - ary0(i - xxx)) / ary0(i - xxx)
ary2(i) = (ary0(i + yyy) - ary0(i)) / ary0(i)
k = k + 1
End If
Next
'求得歷史報酬率樣本中第i個百分比之下限值與第j個百分比之上限值
If zzz - k_gap = 0 Then
percentile_lbound_value = WorksheetFunction.Min(ary1)
Else
percentile_lbound_value = _
WorksheetFunction.Percentile(ary1, zzz - k_gap)
End If
If zzz = 1 Then
percentile_ubound_value = WorksheetFunction.Max(ary1)
Else
percentile_ubound_value = _
WorksheetFunction.Percentile(ary1, zzz)
End If
'將歷史報酬率在百分比上下限內之值寫入
For i = 1 + xxx To k + xxx
If ary1(i) > percentile_lbound_value And ary1(i) <= _
percentile_ubound_value Then
ary3(m) = ary1(i)
ary4(m) = ary2(i)
m = m + 1
End If
Next
ary5(n) = WorksheetFunction.Correl(ary3, ary4)
ary6(n) = n + 1
n = n + 1
Next yyy
ary7(p, 0) = zzz - k_gap
ary7(p, 1) = zzz
ary7(p, 2) = xxx
ary7(p, 3) = WorksheetFunction.Slope(ary5, ary6)
p = p + 1
Next xxx
Next zzz
For i = 0 To 500
Cells(6 + i, 9) = ary7(i, 0)
Cells(6 + i, 10) = ary7(i, 1)
Cells(6 + i, 11) = ary7(i, 2)
Cells(6 + i, 12) = ary7(i, 3)
Next
End Sub