[VBA ] 新手請教 型態不符合
我有一段程式，試跑時可以得到我要的答案，
但我用了一個FOR 迴圈讓他去跑出整張表格時
時常會出現"型態不符合"的錯誤。
有請各位高手幫我解開這個疑惑。
另外，我想請問怎麼樣的寫法可以加快速度呢?
Public Sub tt()
Dim S#, r#, sigma#, Path#, NT#, NK#, SK#, K#, Start#, EndTime#, T#, i#,
Repeat#
S = Cells(3, 2).Value
r = Cells(3, 3).Value
sigma = Cells(3, 4).Value
Path = Cells(3, 5).Value
Repeat = Cells(3, 6).Value
Dim C() As Double
Dim CpuTime() As Double
ReDim C(1 To Repeat) As Double
ReDim CpuTime(1 To Repeat) As Double
For NT = 11 To 11 Step 3
T = Cells(7, NT).Value
For NK = 3 To 3 Step 1
SK = Cells(8, NT + NK - 3).Value
K = S / SK
For i = 1 To Repeat Step 1
Start = Timer
C(i) = Crude(S, K, r, sigma, T, Path, T * 360, 0)
EndTime = Timer
CpuTime(i) = EndTime - Start
Next i
Cells(11, NT + NK - 3) = Application.Average(C)
Cells(12, NT + NK - 3) = Application.StDev(C)
Cells(13, NT + NK - 3) = Application.Average(CpuTime)
Next NK
Next NT
End Sub
=================================================================
其中: Crude函數為
Public Function Crude(S, K, r, sigma, T, Path, Stage, OptionType)
Dim Discount#, dt#
Dim i#, ST#, j#, epsilon#, a#
Discount = Exp(-r * T)
dt = T / Stage
For i = 1 To Path Step 1
ST = S
For j = 1 To Stage Step 1
epsilon = Application.NormSInv(Rnd())
ST = Price(ST, r, sigma, dt, epsilon)
Next j
If OptionType = 0 Then
a = MAX((ST - K) * Discount, 0)
Crude = Crude + a / Path
ElseIf OptionType = 1 Then
a = MAX((K - ST) * Discount, 0)
Crude = Crude + a / Path
End If
Next i
End Function
========================================================
其中，Price函數為
Public Function Price(ByVal S, ByVal r, ByVal sigma, ByVal T, ByVal epsilon)
As Double
Price = S * Exp((r - (sigma ^ 2) / 2) * T + sigma * Sqr(T) * epsilon)
End Function
很長，請各位給我些意見。
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.166.197.139
→
11/29 10:51, , 1F
11/29 10:51, 1F
推
11/29 12:50, , 2F
11/29 12:50, 2F
→
11/29 12:51, , 3F
11/29 12:51, 3F
推
11/29 13:28, , 4F
11/29 13:28, 4F