作者rexstory (Rex)
看板Visual_Basic
標題[VBA ] 新手請教 型態不符合
時間Mon Nov 29 01:52:08 2010
我有一段程式，試跑時可以得到我要的答案，
但我用了一個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
1F：→ MOONRAKER:是哪一行型態不符合 請標出來 11/29 10:51
2F：推 Marty:這是在模擬BS吧 建議把所有的變數都指定型態 11/29 12:50
3F：→ Marty:原本的程式碼完全都沒有指定 這樣Debug很難 11/29 12:51
4F：推 Marty:要快的話再加Application.Calculation = xlCalulationManual 11/29 13:28