Populate array with random numbers efficiently

Question

I have an initial Excel workbook with about 100 000 rows. I put those rows into array and then use that array to create Client class objects, which I add into a final array resultColl.

Function getClients(dataWorkbook As Workbook)
         ...
      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)


            ...
            Set resultColl(k) = clientCopy
         Next
    End With

    getClients = resultColl
End Function

Then I use that array of Clients to run simulations, which means I run a for loop N times and generate a random number for each client N times:

Sub StartDataCollect()
    ...
    Dim clientsColl() As Client
    clientsColl = getClients(dataWorkbook)
    ...
    For simulation = 1 To globals("SIMULATION_COUNT")
        For Each clientCopy In clientsColl
            clientCopy.setSimulationCount = globals("SIMULATION_COUNT")
            clientCopy.generateRandom
        Next
    Next

    ...
End Sub

The generateRandom function:

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

So, all that happens in 1 simulation: VBA runs through an array of size 100 000, puts a Rnd() inside each Client in that array.

The problem is, one iteration takes about a minute. Given I need to run at least 5 000 of them, it's a really long time. I'm not sure how to make it faster, since it doesn't seem too complicated to me already.

What is the point of the line clientCopy.setSimulationCount = globals("SIMULATION_COUNT") in your innermost loop? The simulation count is presumably a constant(ish), so you only need to set it once. — JohnRC
– JohnRC, Commented Dec 22, 2017 at 15:53
I’m downvoting because you’ve omitted portions of code from your question. This has resulted in the best answer being “the performance problem is in code you’ve not shown us”. — RubberDuck
– RubberDuck, Commented Dec 23, 2017 at 1:25

Charles Williams · Accepted Answer · 2017-12-22 15:49:21Z

4

This loop takes under 3 milliseconds on my PC - so your problem is somewhere else in your client class.

Sub testloop()
    Dim var(1 To 100000) As Variant
    Dim j As Long
    Dim dTime As Double

    dTime = microtimer
    For j = LBound(var) To UBound(var)
        var(j) = Rnd()
    Next j
    MsgBox (microtimer - dTime)
End Sub

answered Dec 22, 2017 at 15:49

Charles Williams

1562 bronze badges

Add a comment |

Billal BEGUERADJ · Accepted Answer · 2017-12-22 15:57:25Z

This is not direct answer to you question but refers to CPU performacne based on this:

Private Enum ProcesPriority
    Idle = 64
    Normal = 32
    Realtime = 56
    High_priority = 128
    Below_Normal = 16384
    ABOVE_NORMAL = 32768
End Enum

Sub SetPriority_High
  SetPriority "Excel.exe", High_priority
End sub


Sub SetPriority(ByVal appName As String, ByVal priority As Long)
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery _
    ("Select * from Win32_Process Where Name = '" & appName & "'")
For Each objProcess in colProcesses
    objProcess.SetPriority(BELOW_NORMAL)
Next

End Sub

snehm · Accepted Answer · 2017-12-22 19:52:19Z

1

Here is my solution to polute m*n matrix considering performance. This idea may not generate pure random number but you can implement it, if you like in your favorite language.

array x[4*n]
for i=0 to 4*n                 //polute this array with random numbers
   x[i] = random()

for i=0 to m
   z = rand()                 //now choose a random offset value
   for j=0 to n
      mat[i][j] = x[(j+z)%n]

answered Dec 22, 2017 at 19:52

snehm

3621 silver badge6 bronze badges

Add a comment |

Stack Exchange Network

Populate array with random numbers efficiently

3 Answers 3

You must log in to answer this question.

Hot Network Questions

Populate array with random numbers efficiently

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions