[VBA ] 除以0的問題
Sub 按鈕1_Click()
Dim n As Integer
n = Worksheets("Sheet1").Range("B2").Value
ReDim ZeroBond(1 To n) As Double
ReDim CashFlow(1 To n) As Double
ReDim SpotRate(1 To n) As Double
Dim P As Double
Dim DM As Double
Dim convexity As Double
For i = 1 To n
ZeroBond(i) = Worksheets("Sheet1").Cells(3, 2 + n).Value
CashFlow(i) = Worksheets("Sheet1").Cells(4, 2 + n).Value
Next i
For i = 1 To n
SpotRate(i) = (100 / ZeroBond(i)) ^ (1 / i) - 1
Next i
P = MD = convexity = 0
For i = 1 To n
P = P + CashFlow(i) / (1 + SpotRate(i)) ^ (i)
MD = MD + i * CashFlow(i) / (1 + SpotRate(i)) ^ (i + 1)
convexity = convexity + i * (i + 1) * CashFlow(i) / (1 + SpotRate(i))
^ (i + 2)
Next i
MD = MD / P
convexity = convexity / P
Worksheets("Sheet1").Cells(5, 2).Value = P
Worksheets("Sheet1").Cells(6, 2).Value = MD
Worksheets("Sheet1").Cells(7, 2).Value = convexity
End Sub
---
第一次寫VBA
我的問題是 cells(3,2~XX) 裡面是都有給值的
但是編譯的時候還是說除以0
還是說我的寫法不對??
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.166.240.230
→
03/11 04:32, , 1F
03/11 04:32, 1F