Excel vba: setting a long variable for each object class dramatically increases execution time

Question

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?

If your code works should be moved to another forum to be improved. — user8753746
– user8753746, Commented Dec 27, 2017 at 13:58
Any code can be made slow by repeating it often enough. And it is not fast code. Consider changing the argument type from Double to Long so you don't pay for two expensive floating point conversions. Avoid Variant, use Client. — Hans Passant
– Hans Passant, Commented Dec 27, 2017 at 14:08
Hmm, Dim obj As Client = clientCopy inside the loop should always work. Use For..To instead of For Each should always work. — Hans Passant
– Hans Passant, Commented Dec 27, 2017 at 14:14
Your comments are very misleading. Please delete the inaccurate ones. — Hans Passant
– Hans Passant, Commented Dec 27, 2017 at 15:36

S Meaden · Accepted Answer · 2017-12-27 15:13:33Z

3

You have defined clientCopy as a Variant, which has to be resolved at run-time for each method invocation. Please change to be of type Client and re-run your timings.

Ok, I have re-read the question and comments, to speed up the loop change it thus

Option Explicit

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
    Dim clientCopy As Client

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

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox ("done")
End Sub

edited Dec 27, 2017 at 15:13

answered Dec 27, 2017 at 14:37

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ans Over a year ago

I can't use Client in loops with arrays. I can't use Collection because I tried it and it runs even slower.

Ans Over a year ago

@S Meaden You used for loop instead of for each. I've been advised thousands of times on this very forum that for each loops are much much faster and must be used instead of for even if it requeres use of Variant.

S Meaden Over a year ago

I do not believe "thousands of times", your exaggeration is telling. Just try it.

S Meaden Over a year ago

A 32-bit Long being set in a class (allocated on the heap and accessed via interfaces) is always gonna be slower than an array of Longs on the stack. Stop using classes if you're going to be running this "thousands of times".

S Meaden Over a year ago

I'm leaving this Q&A thread now. Final advice, I really like OO but in your use case (with Monte Carlo simulations I presume) running millions of iteration then classes will add too much baggage to use code. Just declare a bunch of module level arrays with the key data.

|

Collectives™ on Stack Overflow

Excel vba: setting a long variable for each object class dramatically increases execution time

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related