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.

• JohnRC: 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.
• RubberDuck: 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".

Answer 1

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

Answer 2

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

Answer 3

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]