作者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