A queuing macro script for excel.

#Excel Queueing Macro

A queuing macro script for excel.

QueueingFunctions.bas

Attribute VB_Name = "QueueingFunctions"
' This module computes queueing results for simple queueing models
' described in Chapter 6 of Banks, Carson, Nelson and Nicol,
' Discrete-Event System Simulation, 5th edition
'
' original code courtesy of Professor Barry Nelson
' refactored code by Jack Wilson
' v2.1.1


Function QueueMG1(lambda, mu, sigma2, Optional output = 0) As Variant
    Dim result(5) As Variant

    If lambda >= mu Then
        QueueMG1 = "The arrival rate must be less than the service rate"
    ElseIf lambda <= 0 Or mu <= 0 Then
        QueueMG1 = "Arrival rate and service rate must be positive"
    ElseIf sigma2 < 0 Then
        QueueMG1 = "Variance must be nonnegative"
    Else
        rho = lambda / mu
        L = rho + (rho ^ 2 * (1 + sigma2 * mu ^ 2)) / 2 / (1 - rho)
        w = L / lambda
        wQ = w - 1 / mu
        LQ = wQ * lambda
        P0 = 1 - rho
        
        result(0) = rho
        result(1) = L
        result(2) = w
        result(3) = wQ
        result(4) = LQ
        result(5) = P0
        
        Select Case output
        Case 1, "rho"
            QueueMG1 = result(0)
        Case 2, "L", "l"
            QueueMG1 = result(1)
        Case 3, "w"
            QueueMG1 = result(2)
        Case 4, "wQ", "wq"
            QueueMG1 = result(3)
        Case 5, "LQ", "lq"
            QueueMG1 = result(4)
        Case 6, "P0", "p0"
            QueueMG1 = result(5)
        Case Else
            QueueMG1 = result
        End Select
    End If
End Function

Function QueueMMc(lambda, mu, c, Optional output = 0) As Variant
    Dim result(5) As Variant
    
    If lambda >= c * mu Then
        QueueMMc = "The arrival rate must be less than the c times service rate"
    ElseIf lambda <= 0 Or mu <= 0 Then
        QueueMMc = "Arrival rate and service rate must be positive"
    ElseIf c < 1 Then
        QueueMMc = "Number of servers must be 1 or greater"
    Else
        rho = lambda / mu / CDbl(c)
        OfferedLoad = lambda / mu
        Factor = 1
        P0 = 1
        For i = 1 To c - 1
            Factor = Factor * OfferedLoad / CDbl(i)
            P0 = P0 + Factor
        Next i
        cfactorial = CDbl(WorksheetFunction.Fact(c))
        P0 = P0 + Factor * OfferedLoad / CDbl(c) / (1 - rho)
        P0 = 1 / P0
        
        L = OfferedLoad + (OfferedLoad ^ (c + 1) * P0) / CDbl(c) / cfactorial / (1 - rho) ^ 2
        w = L / lambda
        wQ = w - 1 / mu
        LQ = wQ * lambda
    
        result(0) = rho
        result(1) = L
        result(2) = w
        result(3) = wQ
        result(4) = LQ
        result(5) = P0
        
        Select Case output
        Case 1, "rho"
            QueueMMc = result(0)
        Case 2, "L", "l"
            QueueMMc = result(1)
        Case 3, "w"
            QueueMMc = result(2)
        Case 4, "wQ", "wq"
            QueueMMc = result(3)
        Case 5, "LQ", "lq"
            QueueMMc = result(4)
        Case 6, "P0", "p0"
            QueueMMc = result(5)
        Case Else
            QueueMMc = result
        End Select
    End If
End Function

Function QueueMGc(lambda, mu, c, sigma2, Optional output = 0) As Variant
    Dim result(4) As Variant
    
    If lambda >= c * mu Then
        QueueMGc = "The arrival rate must be less than the c times service rate"
    ElseIf lambda <= 0 Or mu <= 0 Then
        QueueMGc = "Arrival rate and service rate must be positive"
    ElseIf c < 1 Then
        QueueMGc = "Number of servers must be 1 or greater"
    ElseIf sigma2 < 0 Then
        QueueMGc = "Variance must be nonnegative"
    Else
        cv2 = sigma2 * mu ^ 2
        rho = lambda / mu / CDbl(c)
        OfferedLoad = lambda / mu
        Factor = 1
        P0 = 1
        For i = 1 To c - 1
            Factor = Factor * OfferedLoad / CDbl(i)
            P0 = P0 + Factor
        Next i
        cfactorial = CDbl(WorksheetFunction.Fact(c))
        P0 = P0 + Factor * OfferedLoad / CDbl(c) / (1 - rho)
        P0 = 1 / P0
        
        rho = rho
        LQ = (OfferedLoad ^ (c + 1) * P0 / c / cfactorial / (1 - rho) ^ 2) * (1 + cv2) / 2
        wQ = LQ / lambda
        w = wQ + 1 / mu
        L = w * lambda
        
        result(0) = rho
        result(1) = L
        result(2) = w
        result(3) = wQ
        result(4) = LQ
        
        Select Case output
        Case 1, "rho"
            QueueMGc = result(0)
        Case 2, "L", "l"
            QueueMGc = result(1)
        Case 3, "w"
            QueueMGc = result(2)
        Case 4, "wQ", "wq"
            QueueMGc = result(3)
        Case 5, "LQ", "lq"
            QueueMGc = result(4)
        Case Else
            QueueMGc = result
        End Select
    End If
End Function

Function QueueMMcN(lambda, mu, c, N, Optional output = 0) As Variant
    Dim result(7) As Variant
    
    If lambda < 0 Or mu < 0 Then
        QueueMMcN = "Arrival rate and service rate must be positive"
    ElseIf c < 1 Then
        QueueMMcN = "Number of servers must be 1 or greater"
    ElseIf N < c Then
        QueueMMcN = "Capacity must be at least as large as the number of servers"
    ElseIf lambda = c * mu Then
        QueueMMcN = "This spreadsheet does not handle the case Lambda equal c*Mu"
    Else
        Dim rhosum As Double
        rho = lambda / mu / CDbl(c)
        OfferedLoad = lambda / mu
        Factor = 1
        P0 = 1
        For i = 1 To c
            Factor = Factor * OfferedLoad / CDbl(i)
            P0 = P0 + Factor
        Next i
        If c < N Then
            rhosum = rho
            If c < N + 1 Then
                For i = c + 2 To N
                    rhosum = rhosum + rho ^ (i - c)
                Next i
            End If
            P0 = P0 + Factor * rhosum
        End If
        P0 = 1 / P0
        cfactorial = CDbl(WorksheetFunction.Fact(c))
        PN = (OfferedLoad ^ N / cfactorial / c ^ (N - c)) * P0
        LQ = P0 * OfferedLoad ^ c * rho / cfactorial / (1 - rho) ^ 2 * (1 - rho ^ (N - c) - (N - c) * rho ^ (N - c) * (1 - rho))
        LambdaEffective = lambda * (1 - PN)
        wQ = LQ / LambdaEffective
        w = wQ + 1 / mu
        L = LambdaEffective * w
        rho = LambdaEffective / CDbl(c) / mu
        
        result(0) = rho
        result(1) = L
        result(2) = w
        result(3) = wQ
        result(4) = LQ
        result(5) = P0
        result(6) = PN
        result(7) = LambdaEffective
        
        Select Case output
        Case 1, "rho"
            QueueMMcN = result(0)
        Case 2, "L", "l"
            QueueMMcN = result(1)
        Case 3, "w"
            QueueMMcN = result(2)
        Case 4, "wQ", "wq"
            QueueMMcN = result(3)
        Case 5, "LQ", "lq"
            QueueMMcN = result(4)
        Case 6, "P0", "p0"
            QueueMMcN = result(5)
        Case 7, "PN", "pn"
            QueueMMcN = result(6)
        Case 8, "LambdaEffective", "lambda", "lambdae"
            QueueMMcN = result(7)
        Case Else
            QueueMMcN = result
        End Select
    End If
End Function

Function QueueMMcKK(lambda, mu, c, K, Optional output = 0) As Variant
    Dim result(6) As Variant
    Dim P() As Double
        
    If lambda < 0 Or mu < 0 Then
        QueueMMcKK = "Arrival rate and service rate must be positive"
    ElseIf K < c Then
        QueueMMcKK = "Size of calling population must be at least as large as the number of servers"
    ElseIf c < 1 Then
        QueueMMcKK = "Number of servers must be 1 or greater"
    Else
        ReDim P(1 To K) As Double
        OfferedLoad = lambda / mu
        Kfac = WorksheetFunction.Fact(K)
        P0 = 1
        If c > 1 Then
            For i = 1 To c - 1
                P(i) = (Kfac / WorksheetFunction.Fact(i) / WorksheetFunction.Fact(K - i)) * OfferedLoad ^ i
                P0 = P0 + P(i)
            Next i
        End If
        cfactorial = WorksheetFunction.Fact(c)
        For i = c To K
            P(i) = (Kfac / WorksheetFunction.Fact(K - i) / cfactorial / CDbl(c ^ (i - c))) * OfferedLoad ^ i
            P0 = P0 + P(i)
        Next i
        P0 = 1 / P0
        L = 0
        LQ = 0
        LambdaEffective = CDbl(K) * lambda * P0
        For i = 1 To K
            P(i) = P(i) * P0
            L = L + CDbl(i) * P(i)
            LQ = LQ + CDbl(WorksheetFunction.Max(0, i - c)) * P(i)
            LambdaEffective = LambdaEffective + lambda * CDbl(K - i) * P(i)
        Next i
        w = L / LambdaEffective
        wQ = LQ / LambdaEffective
        rho = LambdaEffective / CDbl(c) / mu
        
        result(0) = rho
        result(1) = L
        result(2) = w
        result(3) = wQ
        result(4) = LQ
        result(5) = P0
        result(6) = LambdaEffective
        
        Select Case output
        Case 1, "rho"
            QueueMMcKK = result(0)
        Case 2, "L", "l"
            QueueMMcKK = result(1)
        Case 3, "w"
            QueueMMcKK = result(2)
        Case 4, "wQ", "wq"
            QueueMMcKK = result(3)
        Case 5, "LQ", "lq"
            QueueMMcKK = result(4)
        Case 6, "P0", "p0"
            QueueMMcKK = result(5)
        Case 7, "LambdaEffective", "lambda", "lambdae"
            QueueMMcKK = result(6)
        Case Else
            QueueMMcKK = result
        End Select
    End If
End Function