1
\$\begingroup\$

I have a main sub that makes use of a Client class: creates an array with 100 000 Clients and loops over the array 100 times, each time setting a different random number to each Client.

Sub start()
    Application.ScreenUpdating = False

    Dim j As Long

    Dim clientsColl() As Client
    ReDim clientsColl(1 To 100000) As Client

    For j = 1 To 100000
        Set clientsColl(j) = New Client

        clientsColl(j).setClientName = "Client_" & j
    Next

    Dim clientCopy As Variant

    MsgBox ("simulations start")
    Dim i As Long
    For i = 1 To 100
        For Each clientCopy In clientsColl
            clientCopy.setSimulationCount = 100
            clientCopy.generateRandom
        Next
    Next

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox ("done")
End Sub

However, this code takes different time to run, depending whether the line clientCopy.setSimulationCount = 100 is commented out or not. If this line is commented out the code after the simulations start MsgBox takes 16 seconds to run. However, if that line is not commented out and is executed the second loop takes 2 minute 35 seconds to run.

Here's the inside of the Client class, the used Let property:

Private simulationCount As Long

Public Property Let setSimulationCount(value As Double)
    simulationCount = value
End Property


Private randomNumber As Double

Public Sub generateRandom()
    randomNumber = Rnd()
End Sub

So it simply puts number 100 inside each client. Why would it increase execution time nine-fold?

EDIT: To clarify, what I'm asking. I'm asking why the operation that simply sets a Long number for each client increases execution time from 16 seconds to 2 minutes 35 seconds, while the similar operation that sets a Double number for each client to a random number takes only 16 seconds to execute. I would have expected the program with both Long and Double numbers settings to run 16 * 2 seconds, rather than 16 * 9 seconds. I hope it's clear enough.

EDIT: Above is the working example for everyone interested in the actual quesion. Here is the code that is employed in the bigger program, with slight (not essential to the question itself) differences:

Function getClients(dataWorkbook As Workbook)
    Dim globals As Object
    Set globals = getGlobalVariables()

    Dim column_names As Long
    Dim column_contract_numbers As Long
    Dim column_identity As Long
    Dim column_debt As Long
    Dim column_debt_type As Long
    Dim column_rating As Long
    Dim column_tenor As Long
    Dim column_pd As Long
    Dim column_lgd As Long
    Dim column_reserves As Long
    Dim column_loss_ratio As Long
    Dim column_rates As Long

    column_names = globals("CREDIT_NAMES_COLUMN_NUMBER")
    column_contract_numbers = globals("CONTRACT_NUMBERS_COLUMN")
    column_identity = globals("IDENTITY_COLUMN_NUMBER")

    column_debt = globals("DEBT_COLUMN_NUMBER")
    column_debt_type = globals("DEBT_TYPE_COLUMN_NUMBER")
    column_rating = globals("RATING_COLUMN_NUMBER")

    column_tenor = globals("TENOR_COLUMN_NUMBER")
    column_pd = globals("PD_COLUMN_NUMBER")
    column_lgd = globals("LGD_COLUMN_NUMBER")

    column_reserves = globals("RESERVES_COLUMN_NUMBER")
    column_loss_ratio = globals("LOSS_RATIO_COLUMN_NUMBER")
    column_rates = globals("RATES_COLUMN_NUMBER")


    Dim firstRow As Long
    Dim lastRow As Long

    firstRow = globals("DATA_START_ROW")
    lastRow = dataWorkbook.Worksheets(globals("DATA_SHEET")).Range( _
            globals("DATA_COLUMN") & Rows.Count).End(xlUp).Row

    Dim tableArray() As Variant

    Dim resultColl() As Client
    ReDim resultColl(1 To lastRow - firstRow + 1) As Client

    Dim clientCopy As Client
    Dim k As Long

    With dataWorkbook.Worksheets(globals("DATA_SHEET"))

         tableArray = .Range(.Cells(firstRow, column_names), _
                            .Cells(lastRow, column_loss_ratio)).value


        For k = LBound(tableArray) To UBound(tableArray)
            Set clientCopy = New Client

            clientCopy.setClientName = tableArray(k, column_names)
            clientCopy.setContractNumber = tableArray(k, column_contract_numbers)
            clientCopy.setIdentityNumber = tableArray(k, column_identity)

            clientCopy.setTotalDebt = tableArray(k, column_debt)
            clientCopy.setDebtType = tableArray(k, column_debt_type)
            clientCopy.setCreditRating = tableArray(k, column_rating)

            clientCopy.setContractTenor = tableArray(k, column_tenor)
            clientCopy.setTotalPd = tableArray(k, column_pd)
            clientCopy.setTotalLgd = tableArray(k, column_lgd)

            clientCopy.setTotalReserves = tableArray(k, column_reserves)
            clientCopy.setTotalReservesRate = _
            clientCopy.getTotalReserves / clientCopy.getTotalDebt

            clientCopy.setContractRate = tableArray(k, column_rates)
            clientCopy.setLossRatio = tableArray(k, column_loss_ratio)

            clientCopy.setTotalLoss = _
            clientCopy.getTotalDebt * clientCopy.getLossRatio _
            * clientCopy.getTotalLgd - clientCopy.getTotalReserves

            clientCopy.setTotalProfit = _
            clientCopy.getTotalDebt * clientCopy.getContractRate

            Application.StatusBar = "getting clients: " & _
                k & "/" & UBound(tableArray) & ", " & clientCopy.getClientName

            DoEvents

            Set resultColl(k) = clientCopy
        Next
    End With

    getClients = resultColl
End Function

Client class:

Private clientname As String
Private identityNumber As String
Private creditRating As String

Private contractTenor As Long
Private contractNumber As String
Private contractRate As Double

Private totalReserves As Double
Private totalReservesRate As Double

Private debtType As String
Private totalDebt As Double

Private lossRatio As Double
Private totalLoss As Variant
Private totalProfit As Double

Private totalPd As Double
Private totalLgd As Double

Private simulationCount As Long
Private randomNumber As Double
Private outcome As Integer

Private loss As Double
Private profit As Double

Private sumLosses() As Double
Private sumProfits() As Double
Private sumResults() As Double

Private averageDebtInfo As Double

Public Sub generateRandom()
    randomNumber = Rnd()
End Sub

Public Property Get getClientName() As String 'not used so far
    getClientName = clientname
End Property

Public Property Let setClientName(value As String)
    clientname = value
End Property


'to remove
Public Property Get getAverageDebtInfo()
    getAverageDebtInfo = averageDebtInfo
End Property

Public Property Get getSumResultsArray()
    getSumResultsArray = sumResults
End Property

Public Property Get getSumResults(simulation As Long)
    getSumResults = sumResults(simulation)
End Property

Public Property Get getLossRatio()
    getLossRatio = lossRatio
End Property

Public Property Get getTotalReservesRate()
    getTotalReservesRate = totalReservesRate
End Property

Public Property Get getProfit()
    getProfit = profit
End Property

Public Property Get getSumProfitsArray()
    getSumProfitsArray = sumProfits
End Property

Public Property Get getSumProfits(simulation As Long)
    getSumProfits = sumProfits(simulation)
End Property

Public Property Get getContractTenor()
    getContractTenor = contractTenor
End Property

Public Property Get getContractNumber()
    getContractNumber = contractNumber
End Property

Public Property Get getDebtType()
    getDebtType = debtType
End Property

Public Property Get getContractRate()
    getContractRate = contractRate
End Property

Public Property Get getTotalReserves()
    getTotalReserves = totalReserves
End Property

Public Property Get getSumLossesArray()
    getSumLossesArray = sumLosses
End Property

Public Property Get getSumLosses(simulation As Long)
    getSumLosses = sumLosses(simulation)
End Property

Public Property Get getLoss()
    getLoss = loss
End Property

Public Property Get getOutcome()
    getOutcome = outcome
End Property

Private Property Get getSimulationCount()
    getSimulationCount = simulationCount
End Property

Public Property Get getRandomNumber()
    getRandomNumber = randomNumber
End Property

Public Property Get getTotalLoss()
    getTotalLoss = totalLoss
End Property

Public Property Get getTotalProfit()
    getTotalProfit = totalProfit
End Property

Public Property Get getTotalDebt()
    getTotalDebt = totalDebt
End Property

Public Property Get getTotalLgd()
    getTotalLgd = totalLgd
End Property

Public Property Get getCreditRating()
    getCreditRating = creditRating
End Property

Public Property Get getTotalPd()
        getTotalPd = totalPd
End Property

Public Property Get getIdentityNumber()
    getIdentityNumber = identityNumber
End Property

'absent

Public Sub copyData(clientCopy As Variant)
    identityNumber = clientCopy.getIdentityNumber
    creditRating = clientCopy.getCreditRating

    contractTenor = clientCopy.getContractTenor
    contractNumber = clientCopy.getContractNumber
    contractRate = clientCopy.getContractRate

    lossRatio = clientCopy.getLossRatio
    totalReservesRate = clientCopy.getTotalReservesRate

    debtType = clientCopy.getDebtType
    totalDebt = clientCopy.getTotalDebt

    totalLoss = clientCopy.getTotalLoss
    totalPd = clientCopy.getTotalPd
    totalLgd = clientCopy.getTotalLgd

    averageDebtInfo = clientCopy.getAverageDebtInfo

End Sub

Public Property Let setAverageDebtInfo(value As Double)
    averageDebtInfo = value
End Property

Public Property Let setSumProfits(value() As Double)
    sumProfits = value
End Property

Public Property Let setSumResults(value() As Double)
    sumResults = value
End Property

Public Property Let setLossRatio(value As Double)
    lossRatio = value
End Property

Public Property Let setTotalReservesRate(value As Double)
    totalReservesRate = value
End Property

Public Property Let setDebtType(value As String)
    debtType = value
End Property

Public Property Let setContractTenor(value As Long)
    contractTenor = value
End Property

Public Property Let setContractRate(value As Double)
    contractRate = value
End Property


Public Property Let setTotalReserves(value As Double)
    totalReserves = value
End Property

Public Property Let setContractNumber(value As String)
    contractNumber = value
End Property

Public Property Let setSumLosses(value() As Double)
    sumLosses = value
End Property

Public Property Let setSimulationCount(value As Double)
    simulationCount = value
End Property

Public Property Let setTotalLoss(value As Double)
    totalLoss = value
End Property

Public Property Let setTotalProfit(value As Double)
    totalProfit = value
End Property

Public Property Let setTotalDebt(value As Double)
    totalDebt = value
End Property

Public Property Let setTotalLgd(value As Double)
    totalLgd = value
End Property

Public Property Let setCreditRating(value As String)
        creditRating = value
End Property

Public Property Let setTotalPd(value As Double)
    totalPd = value
End Property

Public Property Let setIdentityNumber(value As String)
    identityNumber = value
End Property

Public Sub calculateOutcome()
    If totalPd <> -1 Then
        If randomNumber < totalPd Then
            outcome = 1
        Else
            outcome = 0
        End If
    Else
        outcome = Null
    End If
End Sub

Public Sub calculateFinancialResult()
    If outcome = 1 Then
        loss = totalLoss
        profit = 0
    ElseIf outcome = 0 Then
        loss = 0
        profit = totalProfit
    Else
        loss = Null
        profit = Null
    End If

    Debug.Print i & ", losses: " & losses(i)
End Sub

''''temp
Public Sub clearSums()
    Erase sumLosses
    Erase sumProfits
End Sub


Public Property Let setProfit(value As Double)
    profit = value
End Property

Public Property Let setLoss(value As Double)
    loss = value
End Property

Public Property Let setRandomNumber(value As Double)
    randomNumber = value
End Property

The main sub (mostly the same as the first code posted):

Sub StartDataCollect()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim globals As Object
    Set globals = getGlobalVariables()

    Dim ExcelApp As Object
    Set ExcelApp = getExcelApp()

    Dim dataWorkbook As Workbook

    Set dataWorkbook = ExcelApp.Application.Workbooks.Open( _
                    ThisWorkbook.Worksheets("setup").Cells(3, 2).value)

    Dim clientsColl() As Client
    clientsColl = getClients(dataWorkbook)

    dataWorkbook.Close
    Set dataWorkbook = Nothing

    Dim simulation As Long

    MsgBox ("simulations start")

    For simulation = 1 To globals("SIMULATION_COUNT")
        tempCount = 1
        For Each clientCopy In clientsColl
          clientCopy.setSimulationCount = globals("SIMULATION_COUNT")
          clientCopy.generateRandom
          'clientCopy.calculateOutcome
          'clientCopy.calculateFinancialResult
        Next
    Next

    MsgBox ("Done!")
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    ExcelApp.Quit
End Sub

Global variables dictionary:

Function getGlobalVariables()
    Dim resultDict As Object
    Set resultDict = CreateObject("Scripting.Dictionary")

    'FOR DATA COLLECTION
    resultDict.Add "DATA_START_ROW", 2
    resultDict.Add "DATA_SHEET", "èñõîäíûå_äàííûå_01.07.17"

    resultDict.Add "DATA_COLUMN", "A"
    resultDict.Add "CREDIT_NAMES_COLUMN_NUMBER", 1
    resultDict.Add "CONTRACT_NUMBERS_COLUMN", 2
    resultDict.Add "IDENTITY_COLUMN_NUMBER", 3
    resultDict.Add "DEBT_COLUMN_NUMBER", 5
    resultDict.Add "DEBT_TYPE_COLUMN_NUMBER", 4
    resultDict.Add "RATING_COLUMN_NUMBER", 7

    resultDict.Add "TENOR_COLUMN_NUMBER", 6
    resultDict.Add "PD_COLUMN_NUMBER", 8
    resultDict.Add "LGD_COLUMN_NUMBER", 9
    resultDict.Add "RESERVES_COLUMN_NUMBER", 10
    resultDict.Add "RATES_COLUMN_NUMBER", 11

    resultDict.Add "LOSS_RATIO_COLUMN_NUMBER", 12

    'AFTER DATA COLLECTION
    resultDict.Add "SIMULATION_COUNT", ThisWorkbook.Worksheets("setup").Cells(7, 2).value

    resultDict.Add "DIVIDED_DEBTS", ThisWorkbook.Worksheets("setup").Cells(10, 2).value

    resultDict.Add "PERCENTILE_PERCENT", _
                    ThisWorkbook.Worksheets("setup").Cells(8, 2).value

    resultDict.Add "DEBT_DIVIDE_PERCENTILE_PERCENT", _
                    ThisWorkbook.Worksheets("setup").Cells(9, 2).value

    resultDict.Add "PD_FOR_SD", ThisWorkbook.Worksheets("setup").Cells(12, 2).value
    resultDict.Add "LGD_FOR_SD", ThisWorkbook.Worksheets("setup").Cells(13, 2).value

    resultDict.Add "DEBT_DIVIDE_METHOD", _
                    ThisWorkbook.Worksheets("setup").Cells(14, 2).value

    resultDict.Add "DISPLAY_SIMULATIONS", _
                    ThisWorkbook.Worksheets("setup").Cells(16, 2).value

    Set getGlobalVariables = resultDict
End Function
\$\endgroup\$
  • 2
    \$\begingroup\$ Why do you create 100 000 clients in the first place? Why do you want to set simulationCount to 100 ? What is really going on here? \$\endgroup\$ – Simon Forsberg Dec 27 '17 at 13:59
  • \$\begingroup\$ @Simon Forsberg♦ I create 100 000 clients because in my main program there are 100 000 clients in a data workbook. I set simulationCount to 100 because I need the number 100, the number of iterations in the outer loop to be known in Clients. My question was about why this extra simple operation increases time so much, because apart from that the code seems pretty straightforward - I made it so specifically so it was easier for people here to read, but the structure is the same as in the main project. \$\endgroup\$ – Ans Dec 27 '17 at 14:05
  • 2
    \$\begingroup\$ that doesn't really answer the question. We can see what the code does. What we can't see is why? Why are you setting it to 100, why do you need the number of iterations of the outer loop inside the data-holder Client? Also do note that this site does not accept simplified code. We review only the actual code, because there can be major differences in what's relevant between actual and simplified code ... \$\endgroup\$ – Vogel612 Dec 27 '17 at 14:14
  • 2
    \$\begingroup\$ @Ans Please do not simplify your code just for posting it here. Code Review needs to look at concrete code in a real context, unlike Stack Overflow. \$\endgroup\$ – Simon Forsberg Dec 27 '17 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.