Good Afternoon,
I think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this. Any help would be appreciated.
Public Sub CreateMCPages()
Dim TargetSimulation As Integer
Dim NewWorkSheet As Worksheet
Dim Mu As Double
Dim StDev As Double
'Create a new worksheet that will be deleted once the Monte Carlo is
'completed and the data is copied out of it.
Set NewWorkSheet = _
Application.Sheets.Add( _
After:=Worksheets("Efficient Frontier 3"), _
Type:=XlSheetType.xlWorksheet)
NewWorkSheet.Name = "Monte Carlo"
Sheets("Monte Carlo").Activate
'set initial mean and stdev assumptions
Mu = Sheets("Pg 10").Range("B21")
StDev = Sheets("Pg 10").Range("B22")
'We will run 6 simulations with different mean and stdev assumptions
For TargetSimulation = 1 To 6
MonteCarlo(Mu, StDev)
'update mean and standard deviation for next run
Mu = Worksheets("Pg 10").Range("B21").Offset(0, TargetSimulation)
StDev = Worksheets("Pg 10").Range("B22").Offset(0, TargetSimulation)
'code here to extract the relavent information from each run and paste
'it on the output page.
Next
'delete the MonteCarlo page to save on file size
Application.DisplayAlerts = False
Worksheets("Monte Carlo").Delete
Application.DisplayAlerts = True
End Sub
Public Sub MonteCarlo(Mu As Double, StDev As Double)
'Set up variables for counting columns and rows
Dim Column As Integer
Dim Row As Integer
'Set up variables for years to simulate, starting portfolio values and cash flows
'and number of simulations
Dim Years As Integer
Dim StartingPortfolioValue As Double
Dim CashFlowOut As Double
Dim CashFlowIn As Double
Dim NSimulations As Double
'Get years to simulate and number of simulations from assumptions
'page
Years = Sheets("Assumptions").Range("B12")
NSimulations = Sheets("Assumptions").Range("B14")
'Get starting portfolio value and put it in column one of each simulation
For Row = 1 To NSimulations
Worksheets("Monte Carlo").Cells(Row, 1) = _
Worksheets("Assumptions").Range("B13")
For Column = 2 To Years + 1
'CashFlowOut always occur at the end of the year and CashFlowIn
'always occur at the beginning of the year.
CashFlowOut = Worksheets("Assumptions").Range("E13"). _
Offset(Column - 2, 0)
CashFlowIn = Worksheets("Assumptions").Range("D13"). _
Offset(Column - 2, 0)
If CashFlowIn + (Worksheets("Monte Carlo"). _
Cells(Row, Column - 1) * _
(1 + WorksheetFunction.NormInv(Rnd(), Mu / 100, StDev / 100))) _
- CashFlowOut <= 0 Then
Worksheets("Monte Carlo").Cells(Row, Column) = 0
Else
Worksheets("Monte Carlo").Cells(Row, Column) _
= CashFlowIn + (Worksheets("Monte Carlo").Cells(Row, Column - 1) * _
(1 + WorksheetFunction.NormInv(Rnd(), Mu / 100, StDev / 100))) _
- CashFlowOut
End If
Next
Next
End Sub
Bookmarks