Results 1 to 4 of 4

Passing variables between subs?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Passing variables between subs?

    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
    Last edited by jerseyguy1996; 11-04-2009 at 11:59 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1