PDA

View Full Version : [VBA] Smart macro but something missing - UserFORM?



ssanguszko
12-02-2012, 01:25 PM
Hi,

There is something missing in this VBA project. Maybe spreadsheet with userform or anything? WHen I try to run this code I get nothing.... Tried many options..

Public Sub AMBoption_tree(ByVal drawBinTree As String, ByVal exboundary As String)
'----------------------------------------------------------------------
'|Author: Alstad, R.M. & Foss, J.T. |
'|Version: Final |
'|Date: Fall 2003 |
'|Descripition: This sub evalutates an amercian option using a binomial |
'| tree. It contains code print the tree to the spread |
'| sheet and to print the array of early |
'| exercise boundary to the spread sheet. |
'-----------------------------------------------------------------------
Dim spark, invest, interest, alfas, sigma, opcost, capacity, eqvoptime, costnox, costco2, insurance As Double
Dim t1, t2, T, n As Integer
Dim shtTreeSheet, shtOutput As String
shtTreeSheet = "Tree"
shtOutput = "Exercise boundary"

'Input parameters
spark = Input_Sheet.Range("spark").Value
invest = Input_Sheet.Range("investment").Value
interest = Input_Sheet.Range("interest").Value
alfas = Input_Sheet.Range("alfas").Value
sigma = Input_Sheet.Range("sigma").Value
opcost = Input_Sheet.Range("opcost").Value
capacity = Input_Sheet.Range("capacity").Value
eqvoptime = Input_Sheet.Range("eqvoptime").Value
costnox = Input_Sheet.Range("costnox").Value
costco2 = Input_Sheet.Range("costco2").Value
insurance = Input_Sheet.Range("insurance").Value
t1 = Input_Sheet.Range("tone").Value
t2 = Input_Sheet.Range("ttwo").Value
T = Input_Sheet.Range("tee").Value
n = Input_Sheet.Range("periods").Value

If (drawBinTree = "Yes") And (n > 200) Then
MsgBox ("The tree cannot have more than 200 time steps")
Exit Sub
End If
If (drawBinTree = "Yes") And (n > 10) Then
MsgBox ("Use 10 or less periodes to make add colors to a tree")
End If
Dim U, D, p, dummy As Double
Dim a, b, x, y, i, offset_down, offset_up As Integer
ReDim timing(2, n) As Variant
Dim startprint As Range

'sets up, down and the up probabillity
U = ((alfas * T / n) ^ 2 + sigma ^ 2 * T / n) ^ 0.5
D = -U
p = (alfas * (T) / n + U) / (2 * U)
offset_up = -4
offset_down = -offset_up
'defines an array for the value of spark spread
ReDim periodeTabell(0 To n, 0 To n)

'defines an array for the value of the offset_up and offset_down moves used to print the option tree
ReDim offsetTable(0 To n, 0 To n)

'upper left cell = initial spark spread
periodeTabell(0, 0) = spark

'upper left cell = 0 offset
offsetTable(0, 0) = 0

'the tree of spark spread are calculated left to right
For x = 0 To n - 1 'column
For y = 0 To x 'row
If y = 0 Then

'an up move is set in the cell right of the given spark spread
periodeTabell(y, x + 1) = periodeTabell(y, x) + U
offsetTable(y, x + 1) = offsetTable(y, x) + offset_up
End If

'an down move is set in the cell down and right of the given spark spread
periodeTabell(y + 1, x + 1) = periodeTabell(y, x) + D
offsetTable(y + 1, x + 1) = offsetTable(y, x) + offset_down
Next y
Next x
'defines an array for the value of the option
ReDim OptValue(0 To n, 0 To n)
'the option value is calculated left to right

For i = 0 To n
dummy = periodeTabell(i, n)

If ProjectValue(dummy, interest, alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) _
- invest > 0 Then OptValue(i, 0) = ProjectValue(dummy, interest, alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) _
- invest
Else
OptValue(i, 0) = 0
End If
Next i

i = 0
For a = 1 To n 'colum
For b = 0 To n - a 'row
dummy = periodeTabell(b, n - a)
OptValue(b, a) = Application.Max(ProjectValue(dummy, interest, _
alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) - invest, _
(OptValue(b, a - 1) * p + (1 - p) * OptValue(b + 1, a - 1)) * Exp(-interest * (T) / n))
'returns the optimal investment point
If ProjectValue(dummy, interest, _
alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) - invest > _
(OptValue(b, a - 1) * p + (1 - p) * OptValue(b + 1, a - 1)) * Exp(-interest * (T) / n) Then

'makes an array of optimal investment points
investpoint = (n - a) * T / n
timing(1, a) = investpoint
timing(2, a) = dummy
Else
investpoint = (n - a) * T / n
timing(1, a) = investpoint
End If
Next b
Next a
If drawBinTree = "Yes" Then
'----------- this section writes the binomial tree
'activates worksheet
Worksheets(shtTreeSheet).Activate
'clears the active sheet
ActiveSheet.Cells.ClearContents
ActiveSheet.Cells.ClearFormats
'activates cell to write in
Worksheets(shtTreeSheet).Range("A" & 4 * n - 4).Activate
explain = Array("Periode", "Spark spread", "Option value", "Exercise value")
colorTab = Array(20, 40, 50, 15)
For i = 0 To 3
ActiveCell.Offset(i, 0).Value = explain(i)
If n <= 10 Then
ActiveCell.Offset(i, 0).Select
With Selection.Interior
.ColorIndex = colorTab(i)
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Worksheets(shtTreeSheet).Range("A" & 4 * n - 4).Activate
End If
Next i
Worksheets(shtTreeSheet).Range("A" & 4 * n + 4).Activate
'writes binomial tree to the worksheet

Application.ScreenUpdating = False
For j = 0 To n 'column
For i = 0 To j 'row
'activates the first node in the tree
Worksheets(shtTreeSheet).Range("A" & 4 * n + 4).Activate
'writes the time step
ActiveCell.Offset(offsetTable(i, j), j).Value = j
If n <= 10 Then
ActiveCell.Offset(offsetTable(i, j), j).Select
With Selection.Interior
.ColorIndex = 20
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'activates the first node in the tree
Worksheets(shtTreeSheet).Range("A" & 4 * n + 4).Activate
End If
ActiveCell.Offset(offsetTable(i, j) + 1, j).Value = periodeTabell(i, j)
If n <= 10 Then
ActiveCell.Offset(offsetTable(i, j) + 1, j).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'activates the first node in the tree
Worksheets(shtTreeSheet).Range("A" & 4 * n + 4).Activate
End If
ActiveCell.Offset(offsetTable(i, j) + 2, j).Value = OptValue(i, n - j)
If n <= 10 Then
ActiveCell.Offset(offsetTable(i, j) + 2, j).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'activates the first node in the tree
Worksheets(shtTreeSheet).Range("A" & 4 * n + 4).Activate
End If
ActiveCell.Offset(offsetTable(i, j) + 3, j).Value = ProjectValue(periodeTabell(i, j), interest, _
alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) - invest
If n <= 10 Then
ActiveCell.Offset(offsetTable(i, j) + 3, j).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next i
Next j
Worksheets(shtTreeSheet).Range("A" & 4 * n + 4).Activate
Application.ScreenUpdating = True
End If
'------------- end of tree writing
If exboundary = "Yes" Then
'------------- this section graphs the optimal exercise boundary
'activates worksheet
Worksheets(shtOutput).Activate
'clears the active sheet
ActiveSheet.Cells.ClearContents
ActiveSheet.Cells.ClearFormats
'activates cell to write in
Worksheets(shtOutput).Range("A1").Activate
Application.ScreenUpdating = False
For i = 0 To n
ActiveCell.Offset(i, 0).Value = timing(1, n - i)
ActiveCell.Offset(i, 1).Value = timing(2, n - i)
Next i
MsgBox ("You must adjust the ranges on the graph manually")
Application.ScreenUpdating = True

End If
'MsgBox (OptValue(0, n))
End Sub
Public Function AMBoption(spark As Double, invest As Double, interest As Double, _
alfas As Double, sigma As Double, opcost As Double, capacity As Double, _
eqvoptime As Double, costnox As Double, costco2 As Double, insurance As Double, _
t1 As Integer, t2 As Integer, T As Integer, n As Integer) As Double
'----------------------------------------------------------------------
'|Author: Alstad, R.M. & Foss, J.T. |
'|Version: Final |
'|Date: Fall 2003 |
'|Descripition: This function evalutates an amercian option using a |
'| binomial tree. |
'-----------------------------------------------------------------------
Dim U, D, p, dummy As Double
Dim a, b, x, y, i As Integer
ReDim timing(2, 1) As Variant
Dim startprint As Range
'sets up, down and the up probabillity
U = ((alfas * T / n) ^ 2 + sigma ^ 2 * T / n) ^ 0.5
D = -U
p = (alfas * (T) / n + U) / (2 * U)
'defines an array for the value of spark spread
ReDim periodeTabell(0 To n, 0 To n)
'upper left cell = initial spark spread
periodeTabell(0, 0) = spark
'the tree of spark spread are calculated left to right
For x = 0 To n - 1 'column
For y = 0 To x 'row
If y = 0 Then
'an up move is set in the cell right of the given spark spread
periodeTabell(y, x + 1) = periodeTabell(y, x) + U
End If
'an down move is set in the cell down and right of the given spark spread
periodeTabell(y + 1, x + 1) = periodeTabell(y, x) + D
Next y
Next x
'defines an array for the value of the option
ReDim OptValue(0 To n, 0 To n)
'the option value is calculated left to right
For i = 0 To n
dummy = periodeTabell(i, n)
If ProjectValue(dummy, interest, alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) _
- invest > 0 Then OptValue(i, 0) = ProjectValue(dummy, interest, alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) _
- invest
Else
OptValue(i, 0) = 0
End If
Next i
i = 0
For a = 1 To n 'colum
For b = 0 To n - a 'row
dummy = periodeTabell(b, n - a)
OptValue(b, a) = Application.Max(ProjectValue(dummy, interest, _
alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) - invest, _
(OptValue(b, a - 1) * p + (1 - p) * OptValue(b + 1, a - 1)) * Exp(-interest * (T) / n))
Next b
Next a
dummy = OptValue(0, n)
AMBoption = dummy
End Function
Function ProjectValue(ByVal spark As Double, ByVal interest As Double, _
ByVal alfas As Double, ByVal opcost As Double, ByVal capacity As Double, ByVal _
eqvoptime As Double, ByVal costnox As Double, ByVal costco2 As Double, ByVal insurance As Double, _
ByVal t1 As Integer, ByVal t2 As Integer) As Double
'----------------------------------------------------------------------
'|Author: Alstad, R.M. & Foss, J.T. |
'|Version: Final |
'|Date: Fall 2003 |
'|Descripition: This function evalutates the value of the project. |
'-----------------------------------------------------------------------
Dim c1, c2 As Double
c1 = capacity * eqvoptime * (Exp(-interest * t1) - Exp(-interest * t2)) / interest
c2 = capacity * eqvoptime * alfas * ((interest * t1 + 1) * Exp(-interest * t1) - (interest * t2 + 1) * Exp(-interest * t2)) / interest ^ 2 _
- (capacity * eqvoptime * (costnox + costco2) + (opcost + insurance)) * (Exp(-interest * t1) - Exp(-interest * t2)) / interest
ProjectValue = (c1 * spark + c2) * 0.000000001 ' i mrd NOK
End Function
Function S_star(ByVal Spark As Double, ByVal interest As Double, _
ByVal alfas As Double, ByVal opcost As Double, ByVal capacity As Double, ByVal _
eqvoptime As Double, ByVal costnox As Double, ByVal costco2 As Double, ByVal insurance AsDouble, _
ByVal t1 As Integer, ByVal t2 As Integer, beta As Double, invest As Double) As Double
'----------------------------------------------------------------------
'|Author: Alstad, R.M. & Foss, J.T. |
'|Version: Final |
'|Date: Fall 2003 |
'|Descripition: This function finds the exercise threshold for an |
'| infinite option. |
'-----------------------------------------------------------------------
Dim c1, c2 As Double
c1 = capacity * eqvoptime * (Exp(-interest * t1) - Exp(-interest * t2)) / interest
c2 = capacity * eqvoptime * alfas * ((interest * t1 + 1) * Exp(-interest * t1) - (interest * t2 + 1) * Exp(-interest * t2)) / interest ^ 2 _
- (capacity * eqvoptime * (costnox + costco2) + (opcost + insurance)) * (Exp(-interest * t1) - Exp(-interest * t2)) / interest
S_star = (c1 - c2 * beta + invest * 1000000000 * beta) / (c1 * beta)
End Function
Public Function EurAMBoption(spark As Double, invest As Double, interest As Double, _
alfas As Double, sigma As Double, opcost As Double, capacity As Double, _
eqvoptime As Double, costnox As Double, costco2 As Double, insurance As Double, _
t1 As Integer, t2 As Integer, T As Integer, n As Integer) As Double
'----------------------------------------------------------------------
'|Author: Alstad, R.M. & Foss, J.T. |
'|Version: Final |
'|Date: Fall 2003 |
'|Descripition: This function evalutates an european option using a |
'| binomial tree. |
'-----------------------------------------------------------------------
Dim U, D, p, dummy As Double
Dim a, b, x, y, i As Integer
ReDim timing(2, 1) As Variant
Dim startprint As Range
'sets up, down and the up probabillity
U = ((alfas * T / n) ^ 2 + sigma ^ 2 * T / n) ^ 0.5
D = -U
p = (alfas * (T) / n + U) / (2 * U)
'defines an array for the value of spark spread
ReDim periodeTabell(0 To n, 0 To n)
'upper left cell = initial spark spread
periodeTabell(0, 0) = spark
'the tree of spark spread are calculated left to right
For x = 0 To n - 1 'column
For y = 0 To x 'row
If y = 0 Then

'an up move is set in the cell right of the given spark spread
periodeTabell(y, x + 1) = periodeTabell(y, x) + U
End If
'an down move is set in the cell down and right of the given spark spread
periodeTabell(y + 1, x + 1) = periodeTabell(y, x) + D
Next y
Next x
'defines an array for the value of the option
ReDim OptValue(0 To n, 0 To n)
'the option value is calculated left to right
For i = 0 To n
dummy = periodeTabell(i, n)
If ProjectValue(dummy, interest, alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) _
- invest > 0 Then
OptValue(i, 0) = ProjectValue(dummy, interest, alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) _
- invest
Else
OptValue(i, 0) = 0
End If
Next i
i = 0
For a = 1 To n 'colum
For b = 0 To n - a 'row
dummy = periodeTabell(b, n - a)
OptValue(b, a) = (OptValue(b, a - 1) * p + (1 - p) * OptValue(b + 1, a - 1)) * Exp(-interest * (T) / n)
Next b
Next a
dummy = Application.Max(ProjectValue(periodeTabell(0, 0), interest, _
alfas, opcost, capacity, eqvoptime, costnox, costco2, insurance, t1, t2) - invest, _
OptValue(0, n))
EurAMBoption = dummy
End Function