Queueing simulation VBA help

[deedeedudu]

Dear Excel/VBA gurus,

I'm new to using VBA and we are taught queuing simulation in class. As part of the assignment we need to modify the attached code as per the following

Change the program so that the current simulation is embedded in a For Loop from 1 to 100. Each time through the loop, one simulation is run, and its output (you can select which ones) are reported on a Replications sheet. After all 100 simulations have run, summarize the selected outputs on a Summary sheet. For each output, report the following summary measures: minimum, maximum, average, standard deviation, median and 5th and 95th percentile.
For example, if one of your outputs on any replication is the maximum number in queue, then you will get 100 such maximums, one for each replication. The summary sheet should summarize these 100 numbers: their average, their standard deviation and so on. In this way you can see how results vary from replication to another.

[/COLOR]Option Explicit


' Declare system parameters.
'   meanIATime - mean interarrival time (reciprocal of arrival rate)
'   meanServeTime - mean service time
'   nServers - number of servers
'   maxAllowedInQ - maximum number of customers allowed in the queue
'   closeTime - clock time when no future arrivals are accepted


Dim meanIATime As Single
Dim meanServeTime As Single
Dim nServers As Integer
Dim maxAllowedInQ As Integer
Dim closeTime As Single
    
' Declare system status indicators.
'   nInQueue - number of customers currently in the queue
'   nBusy - number of servers currently busy
'   clockTime - current clock time, where the inital clock time is 0
'   eventScheduled(i) - True or False, depending on whether an event of type i is
'       scheduled or not, for i>=0, where i=0 corresponds to arrivals and i from
'       1 to nServers corresponds to server i service completions
'   timeOfLastEvent - clock time of previous event
'   timeOfNextEvent(i) - the scheduled clock time of the next event of type i
'       (only defined when eventScheduled(i) is True


Dim nInQueue As Integer
Dim nBusy As Integer
Dim clockTime As Single
Dim eventScheduled() As Boolean
Dim timeOfLastEvent As Single
Dim timeOfNextEvent() As Single


' Declare statistical variables.
'   nServed - number of customers who have completed service so far
'   nLost - number of customers who have been turned away so far
'   maxNInQueue - maximum number in the queue at any point in time so far
'   maxTimeInQueue - maximum time any customer has spent in the queue so far
'   timeOfArrival(i) - arrival time of the customer currently in the i-th
'       place in the queue, for i>=1
'   totalTimeInQueue - total customer-time units spent in the queue so far
'   totalTimeBusy - total server-time units spent serving customers so far
'   sumOfQueueTimes - sum of all times in the queue so far, where sum is over
'       customers who have completed their times in the queue
'   queueTimeArray(i) - amount of time there have been exactly i customers
'       in the queue, for i>=0


Dim nServed As Long
Dim nLost As Integer
Dim maxNInQueue As Integer
Dim maxTimeInQueue As Single
Dim timeOfArrival() As Single
Dim totalTimeInQueue As Single
Dim totalTimeBusy As Single
Dim sumOfQueueTimes As Single
Dim queueTimeArray() As Single


Sub Main()
    ' This sub runs when the user clicks on the "Run the simulation" button on
    ' the Simulation sheet. It sets up and runs the simulation.
    Dim nextEventType As Integer
    Dim finishedServer As Integer


    ' Always start with new random numbers.
    Randomize


    ' Clear previous results, if any, from the Report sheet.
    Call ClearOldResults


    ' Get inputs from the Report Sheet.
    meanIATime = 1 / Range("ArriveRate").Value
    meanServeTime = Range("MeanServeTime").Value
    nServers = Range("nServers").Value
    maxAllowedInQ = Range("MaxAllowedInQ").Value
    closeTime = Range("CloseTime").Value


    ' The next two arrays have an element for arrivals (index 0)
    ' and one for each server.
    ReDim eventScheduled(nServers + 1)
    ReDim timeOfNextEvent(nServers + 1)


    ' Set counters, status indicators to 0 and schedule first arrival.
    Call Initialize


    ' Keep simulating until the last customer has left.
    Do
        ' Find the time and type of the next event, and reset the clock.
        ' Capture the index of the finished server in case the next event
        ' is a service completion.
        Call FindNextEvent(nextEventType, finishedServer)


        ' Update statistics since the last event.
        Call UpdateStatistics


        ' nextEventType is 1 for an arrival, 2 for a departure.
        If nextEventType = 1 Then
            Call Arrival
        Else
            Call Departure(finishedServer)
        End If
    Loop Until Not eventScheduled(0) And nBusy = 0


    ' Report the results.
    Call Report
End Sub


Sub ClearOldResults()
    ' This sub clears the results from any previous simulation.
    With Worksheets("Report")
        .Range("B12:B23").ClearContents
        With .Range("A26")
            Range(.Offset(1, 0), .Offset(0, 1).End(xlDown)).ClearContents
        End With
    End With
End Sub


Sub Initialize()
    ' This sub initializes the simulation to the "empty and idle" state and
    ' sets all statistical counters to 0. It then schedules the first arrival.
    Dim i As Integer


    ' Initialize system status indicators.
    clockTime = 0
    nBusy = 0
    nInQueue = 0
    timeOfLastEvent = 0


    ' Initialize statistical variables.
    nServed = 0
    nLost = 0
    sumOfQueueTimes = 0
    maxTimeInQueue = 0
    totalTimeInQueue = 0
    maxNInQueue = 0
    totalTimeBusy = 0


    ' Redimension the queueTimeArray array to have one element (the 0 element,
    ' for the amount of time when there are 0 customers in the queue).
    ReDim queueTimeArray(1)
    queueTimeArray(0) = 0


    ' Schedule an arrival from the exponential distribution.
    eventScheduled(0) = True
    timeOfNextEvent(0) = Exponential(meanIATime)


    ' Don't schedule any departures because there are no customers in the system.
    For i = 1 To nServers
        eventScheduled(i) = False
    Next
End Sub


Function Exponential(mean As Single) As Single
    ' This generates a random number from an exponential distribution
    ' with a given mean.
    Exponential = -mean * Log(Rnd)
End Function


Sub FindNextEvent(nextEventType As Integer, finishedServer As Integer)
    ' This sub finds the type (arrival, departure, or closing time) of the next
    ' event and advances the simulation clock to the time of the next event.
    Dim i As Integer
    Dim nextEventTime As Single


    ' nextEventTime will be the minimum of the scheduled event times.
    ' Start by setting it to a large value.
    nextEventTime = 10 * closeTime


    ' Find type and time of the next (most imminent) scheduled event. Note that
    ' there is a potential event scheduled for the next arrival (indexed as 0) and
    ' for each server completion (indexed as 1 to nServers).
    For i = 0 To nServers
        ' Check if there is an event schedule of type i.
        If eventScheduled(i) Then
            ' If the current event is the most imminent so far, record it.
            If timeOfNextEvent(i) < nextEventTime Then
                nextEventTime = timeOfNextEvent(i)
                If i = 0 Then
                    ' It's an arrival.
                    nextEventType = 1
                Else
                    ' It's a departure - record the index of the server who finished.
                    nextEventType = 2
                    finishedServer = i
                End If
            End If
        End If
    Next


    ' Advance the clock to the time of the next event.
    clockTime = nextEventTime
End Sub
      
Sub UpdateStatistics()
    ' This sub updates statistics since the time of the previous event.
    Dim timeSinceLastEvent As Single


    ' timeSinceLastEvent is the time since the last update.
    timeSinceLastEvent = clockTime - timeOfLastEvent


    ' Update statistical variables.
    queueTimeArray(nInQueue) = queueTimeArray(nInQueue) + timeSinceLastEvent
    totalTimeInQueue = totalTimeInQueue + nInQueue * timeSinceLastEvent
    totalTimeBusy = totalTimeBusy + nBusy * timeSinceLastEvent


    ' Reset timeOfLastEvent to the current time.
    timeOfLastEvent = clockTime
End Sub


Sub Arrival()
    ' This sub takes care of all the logic when a customer arrives.
    Dim i As Integer


    ' Schedule the next arrival.
    timeOfNextEvent(0) = clockTime + Exponential(meanIATime)


    ' Cut off the arrival stream if it is past closing time.
    If timeOfNextEvent(0) > closeTime Then
        eventScheduled(0) = False
    End If


    ' If the queue is already full, this customer is turned away.
    If nInQueue = maxAllowedInQ Then
        nLost = nLost + 1
        Exit Sub
    End If


    ' Check if all servers are busy.
    If nBusy = nServers Then


        ' All servers are busy, so put this customer at the end of the queue.
        nInQueue = nInQueue + 1


        ' If the queue is now longer than it has been before, update maxNInQueue
        ' and redimension arrays appropriately.
        If nInQueue > maxNInQueue Then
            maxNInQueue = nInQueue


            ' queueTimeArray is 0-based, with elements 0 to maxNInQueue.
            ReDim Preserve queueTimeArray(0 To maxNInQueue)


            ' timeOfArrival is 1-based, with elements 1 to maxNInQueue.
            ReDim Preserve timeOfArrival(1 To maxNInQueue)
        End If


        ' Keep track of this customer's arrival time (for later stats).
        timeOfArrival(nInQueue) = clockTime


    Else
        ' The customer can go directly into service, so update the number of servers busy.
        nBusy = nBusy + 1


        ' This loop searches for the first idle server and schedules a departure
        ' event for this server.
        For i = 1 To nServers
            If Not eventScheduled(i) Then
                eventScheduled(i) = True
                timeOfNextEvent(i) = clockTime + Exponential(meanServeTime)
                Exit For
            End If
        Next
    End If
End Sub
      
Sub Departure(finishedServer As Integer)
    ' This sub takes care of the logic when a customer departs from service.
    Dim i As Integer
    Dim timeInQueue As Single


    ' Update number of customers who have finished.
    nServed = nServed + 1


    ' Check if any customers are waiting in queue.
    If nInQueue = 0 Then
        
        ' No one is in the queue, so make the server who just finished idle.
        nBusy = nBusy - 1
        eventScheduled(finishedServer) = False


    Else


        ' At least one person is in the queue, so take first customer
        ' in queue into service.
        nInQueue = nInQueue - 1


        ' timeInQueue is the time this customer has been waiting in line.
        timeInQueue = clockTime - timeOfArrival(1)


        ' Check if this is a new maximum time in queue.
        If timeInQueue > maxTimeInQueue Then
            maxTimeInQueue = timeInQueue
        End If


        ' Update the total of all customer queue times so far.
        sumOfQueueTimes = sumOfQueueTimes + timeInQueue


        ' Schedule departure for this customer with the same server who just finished.
        timeOfNextEvent(finishedServer) = clockTime + Exponential(meanServeTime)


        ' Move everyone else in line up one space.
        For i = 1 To nInQueue
            timeOfArrival(i) = timeOfArrival(i + 1)
        Next
    End If
End Sub
      
Sub Report()
    ' This sub calculates and then reports summary measures for the simulation.
    Dim i As Integer
    Dim avgTimeInQueue As Single
    Dim avgNInQueue As Single
    Dim avgNBusy As Single


    ' Calculate averages.
    avgTimeInQueue = sumOfQueueTimes / nServed
    avgNInQueue = totalTimeInQueue / clockTime
    avgNBusy = totalTimeBusy / clockTime


    ' queueTimeArray records, for each value from 0 to maxNInQueue, the percentage
    ' of time that many customers were waiting in the queue.
    For i = 0 To maxNInQueue
        queueTimeArray(i) = queueTimeArray(i) / clockTime
    Next


    ' Enter simulate results in named ranges.
    Range("FinalTime").Value = clockTime
    Range("NServed").Value = nServed
    Range("AvgTimeInQ").Value = avgTimeInQueue
    Range("MaxTimeInQ").Value = maxTimeInQueue
    Range("AvgNInQ").Value = avgNInQueue
    Range("MaxNInQ").Value = maxNInQueue
    Range("AvgServerUtil").Value = avgNBusy / nServers
    Range("NLost").Value = nLost
    Range("PctLost").Formula = "=NLost/(NLost + NServed)"


    ' Enter the queue length distribution from row 27 down, and name the two columns.
    With Range("A27")
        For i = 0 To maxNInQueue
            .Offset(i, 0).Value = i
            .Offset(i, 1).Value = queueTimeArray(i)
        Next
        Range(.Offset(0, 0), .Offset(maxNInQueue, 0)).Name = "NInQueue"
        Range(.Offset(0, 1), .Offset(maxNInQueue, 1)).Name = "PctOfTime"
    End With
    
    ' Update the chart.
    With ActiveSheet.ChartObjects(1).Chart
        With .SeriesCollection(1)
            .Values = Range("PctOfTime")
            .XValues = Range("nInQueue")
        End With
    End With


    Range("A2").Select
End Sub


Sub ViewChangeInputs()
    ' This sub runs when the user clicks on the "View/Change Inputs" button on the
    ' Explanation sheet. It clears old results, if any, and lets the user see
    ' the Report sheet.
    With Worksheets("Report")
        .Visible = True
        .Activate
    End With
    Call ClearOldResults
End Sub