[問題] 如何從vba程式碼當中,把暫時記憶體的數值畫出來

看板Office作者lassie690 (恰恰)時間15年前 (2011/05/29 17:25)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串1/1

Public Function Binomial(AEFlag As String, CPFlag As String, S As Double, K As Double, T As Double, sig As Double, r As Double, _ y As Double, n As Integer) As Double Dim Valiue() As Double Dim u As Double, d As Double, p As Double Dim dt As Double, Df As Double Dim i As Integer, j As Integer, z As Integer ReDim Value(n + 1) If CPFlag = "C" Then z = 1 ElseIf CPFlag = "P" Then z = -1 End If dt = T / n u = Exp(sig * Sqr(dt)) d = 1 / u p = (Exp((r - y) * dt) - d) / (u - d) Df = Exp(-r * dt) For i = 0 To n Value(i) = Application.Max(0, z * (S * u ^ i * d ^ (n - i) - K)) Next For j = n - 1 To 0 Step -1 For i = 0 To j If AEFlag = "E" Then Value(i) = (p * Value(i + 1) + (1 + p) * Value(i)) * Df ElseIf AEFlag = "A" Then Value(i) = Application.Max((z * (S * u ^ i * d ^ (Abs(i - j)) - K)), (p * Value(i + 1) + (1 - p) * Value(i)) * Df) End If Next Next Binomial = Value(0) End Function Public Sub Binomial_tree() Dim AEFlag As String Dim CPFlag As String Dim S As Double Dim K As Double Dim T As Double Dim sig As Double Dim r As Double Dim y As Double Dim n As Integer Dim price As Double AEFlag = Worksheets("sheet1").Cells(3, 2).Value CPFlag = Worksheets("sheet1").Cells(3, 3).Value S = Worksheets("sheet1").Cells(3, 4).Value K = Worksheets("sheet1").Cells(3, 5).Value T = Worksheets("sheet1").Cells(3, 6).Value sig = Worksheets("sheet1").Cells(3, 7).Value r = Worksheets("sheet1").Cells(3, 8).Value y = Worksheets("sheet1").Cells(3, 9).Value n = Worksheets("sheet1").Cells(3, 10).Value price = Binomial(AEFlag, CPFlag, S, K, T, sig, r, y, n) Worksheets("sheet1").Cells(7, 2).Value = price End Sub 我是想把Public Function Binomial(AEFlag As String, CPFlag As String, S As Double, K As Double, T As Double, sig As Double, r As Double, _ y As Double, n As Integer) As Double Dim Valiue() As Double Dim u As Double, d As Double, p As Double Dim dt As Double, Df As Double Dim i As Integer, j As Integer, z As Integer ReDim Value(n + 1) If CPFlag = "C" Then z = 1 ElseIf CPFlag = "P" Then z = -1 End If dt = T / n u = Exp(sig * Sqr(dt)) d = 1 / u p = (Exp((r - y) * dt) - d) / (u - d) Df = Exp(-r * dt) For i = 0 To n Value(i) = Application.Max(0, z * (S * u ^ i * d ^ (n - i) - K)) Next For j = n - 1 To 0 Step -1 For i = 0 To j If AEFlag = "E" Then Value(i) = (p * Value(i + 1) + (1 + p) * Value(i)) * Df ElseIf AEFlag = "A" Then Value(i) = Application.Max((z * (S * u ^ i * d ^ (Abs(i - j)) - K)), (p * Value(i + 1) + (1 - p) * Value(i)) * Df) End If Next Next Binomial = Value(0) End Function Public Sub Binomial_tree() Dim AEFlag As String Dim CPFlag As String Dim S As Double Dim K As Double Dim T As Double Dim sig As Double Dim r As Double Dim y As Double Dim n As Integer Dim price As Double AEFlag = Worksheets("sheet1").Cells(3, 2).Value CPFlag = Worksheets("sheet1").Cells(3, 3).Value S = Worksheets("sheet1").Cells(3, 4).Value K = Worksheets("sheet1").Cells(3, 5).Value T = Worksheets("sheet1").Cells(3, 6).Value sig = Worksheets("sheet1").Cells(3, 7).Value r = Worksheets("sheet1").Cells(3, 8).Value y = Worksheets("sheet1").Cells(3, 9).Value n = Worksheets("sheet1").Cells(3, 10).Value price = Binomial(AEFlag, CPFlag, S, K, T, sig, r, y, n) Worksheets("sheet1").Cells(7, 2).Value = price End Sub 我是想把Public Function Binomial(AEFlag As String, CPFlag As String, S As Double, K As Double, T As Double, sig As Double, r As Double, _ y As Double, n As Integer) As Double Dim Valiue() As Double Dim u As Double, d As Double, p As Double Dim dt As Double, Df As Double Dim i As Integer, j As Integer, z As Integer ReDim Value(n + 1) If CPFlag = "C" Then z = 1 ElseIf CPFlag = "P" Then z = -1 End If dt = T / n u = Exp(sig * Sqr(dt)) d = 1 / u p = (Exp((r - y) * dt) - d) / (u - d) Df = Exp(-r * dt) For i = 0 To n Value(i) = Application.Max(0, z * (S * u ^ i * d ^ (n - i) - K)) Next For j = n - 1 To 0 Step -1 For i = 0 To j If AEFlag = "E" Then Value(i) = (p * Value(i + 1) + (1 + p) * Value(i)) * Df ElseIf AEFlag = "A" Then Value(i) = Application.Max((z * (S * u ^ i * d ^ (Abs(i - j)) - K)), (p * Value(i + 1) + (1 - p) * Value(i)) * Df) End If Next Next Binomial = Value(0) End Function 我想把ReDim Value(n + 1)這暫時記憶體裡面個數值給劃圖出來,我有嘗試用錄製巨集方式,但還是想不出來!有哪位高手可以提示一下嗎?謝謝軟體:office excel 版本:2007 -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 120.96.141.170