Can anyone explain this behaviour?

[pcc]

Can anyone explain this behaviour? I have a procedure that randomly generates row numbers and column numbers in the range 1 to 80.
Once the row number and column number are generated, the relevant cell is coloured black. This is repeated until all cells in the range are black,
and the time taken, plus number of attempts to select all 6400 (ie 80x80) cells is written to a log sheet. This process is then repeated 18 times. (NB this is for amusement only -
it's not used for anything useful, just curiosity).

On running the procedure, I find that it typically takes 20 to 30 seconds, with fewer than around 100,000 attempts. However, as seen from the log sheet, it sometimes takes far longer to run (see attempts 4, 9 and 10).
Logically I would expect a variation in the times, but there is such a variation from max to min that it looks like the data is not entirely random. What causes this?

Sub pixels()
Application.ScreenUpdating = False

Sheets.Add.Name = "Log"
[a1] = "Duration"
[b1] = "Tries"
drow = 2
ncols = 80
nrows = 80
Sheets("Main").Activate
Application.ScreenUpdating = True
iter = 1
Do Until iter = 20  ' run the test 19 times
tries = 0 ' initialise the counter for random number generation
Cells.Interior.ColorIndex = xlNone ' set all cells to no colour
starttime = Time
nums = 0  ' initialise number of cells that have been chosen
Do Until nums = (ncols * nrows) ' ie do it until every cell in the matrix has been coloured
Randomize
x = Int(Rnd * ncols) + 1 ' generate random number (row) from 1 to 80
Randomize
y = Int(Rnd * nrows) + 1  ' generate random number (column) from 1 to 80

tries = tries + 1 ' increment the counter for random number generation
If Cells(x, y).Interior.ColorIndex = xlNone Then
Cells(x, y).Interior.ColorIndex = 1 'cells(x, y) not previously chosen, so colour it black
nums = nums + 1  ' increment the number of discrete cells chosen
End If
Loop
' once it gets here, all cells have been chosen
endtime = Time
iter = iter + 1
' write results to "Log" sheet
Sheets("Log").Cells(drow, 1) = Format((endtime - starttime) * 100000, "0.0") & "s"
Sheets("Log").Cells(drow, 2) = tries
drow = drow + 1
' do it again until number of tests is 19
Loop
End Sub

 

[rlv01]

I think it must be the randomization. X and Y are picked using a (pseudo) random number generator. That means the number of attempts to pick every cell in the 80 x 80 grid is going to vary. Column B in your log reflects this.

Not sure what you are doing, but you could speed things up a lot by changing to RandBetween

            x = Application.RandBetween(1, 80)
            y = Application.RandBetween(1, 80)

 

[pcc]

I think it must be the randomization. X and Y are picked using a (pseudo) random number generator. That means the number of attempts to pick every cell in the 80 x 80 grid is going to vary. Column B in your log reflects this.

Not sure what you are doing, but you could speed things up a lot by changing to RandBetween

            x = Application.RandBetween(1, 80)
            y = Application.RandBetween(1, 80)

Yes, but I am just surprised that there is such a variation. I would have expected maybe a +/- 20% variation (not based on any mathematical analysis), just a gut feel..
I have a very old version of Excel (2002), and it doesn't recognise Application.RandBetween! Thanks for your reply.

 

[Jeffrey Mahoney]

There was a lot of redundancy in the code, try this. It reduces the time you need to check all the cells. My times were consistently around 200 seconds for an 80X80 grid. Now it's floating under 5 seconds

Random Fill Cells Black Test.xlsm
	A	B	C	D
1	Duration	Tries	Cells	nums
2	4.6s	6,400	6,400	6400
3	4.6s	6,400	6,400	6400
4	4.6s	6,400	6,400	6400
5	3.5s	6,400	6,400	6400
Log

Sub pixels()
  Dim ncols As Long
  Dim nrows As Long
  Dim iter As Long
  Dim x As Long
  Dim y As Long
  Dim tries As Long
  Dim drow As Long
  Dim Cel As Range
  Dim StartTime As Date
  Dim EndTime As Date
  Dim nums As Long
  Dim Celxy() As Single
  Dim CelCnt As Long
  Dim R As Long
  Dim ColXY As VBA.Collection
  Dim SaveColXY As VBA.Collection
  Dim V As Single
  Dim Item As Variant
 
  Application.ScreenUpdating = False
 
  'Sheets.Add.Name = "Log"
  Sheets("Log").Range("A1") = "Duration"
  Sheets("Log").Range("B1") = "Tries"
  Sheets("Log").Range("C1") = "Cells"
  Sheets("Log").Range("D1") = "nums"
 
  drow = 2
  '------------
  ncols = 80
  nrows = 80
  '------------
  Set ColXY = New Collection
  Set SaveColXY = New Collection
  For x = 1 To ncols
    For y = 1 To nrows
      SaveColXY.Add x + y / 1000
      ColXY.Add x + y / 1000
    Next y
  Next x
 
  Sheets("Main").Activate
  Application.ScreenUpdating = True
  iter = 0
  Do Until iter = 4  ' run the test 19 times
    iter = iter + 1
    tries = 0 ' initialise the counter for random number generation
    Cells.Interior.ColorIndex = xlNone ' set all cells to no colour
    StartTime = Time
    nums = 0  ' initialise number of cells that have been chosen
    If iter > 1 Then
      For Each Item In SaveColXY
        ColXY.Add Item
      Next Item
    End If
    CelCnt = ncols * nrows
    Do Until nums = (ncols * nrows) ' ie do it until every cell in the matrix has been coloured
      R = Application.RandBetween(1, CelCnt)
      V = ColXY.Item(R)
      x = Int(V)
      y = (V - x) * 1000
     
      tries = tries + 1 ' increment the counter for random number generation
      Cells(x, y).Interior.ColorIndex = 1 'cells(x, y) not previously chosen, so colour it black
      nums = nums + 1  ' increment the number of discrete cells chosen
      ColXY.Remove R
      CelCnt = CelCnt - 1
    Loop
  ' once it gets here, all cells have been chosen
    EndTime = Time
   
    ' write results to "Log" sheet
    Sheets("Log").Cells(drow, 1) = Format((EndTime - StartTime) * 100000, "0.0") & "s"
    Sheets("Log").Cells(drow, 2) = tries
    Sheets("Log").Cells(drow, 3) = ncols * nrows
    Sheets("Log").Cells(drow, 4) = nums
    drow = drow + 1
   
  Loop
 
 
End Sub

 

[Jeffrey Mahoney]

I did have some larger variances when I ran larger patterns. I think it was due to other applications on my laptop hijacking some of the CPU.

Random Fill Cells Black Test.xlsm
	A	B	C	D
1	Duration	Tries	Cells	nums
2	12.7s	10,000	10,000	10,000
3	23.1s	10,000	10,000	10,000
4	37.0s	10,000	10,000	10,000
5	15.0s	10,000	10,000	10,000
6	10.4s	10,000	10,000	10,000
7	10.4s	10,000	10,000	10,000
Log

 

[Jeffrey Mahoney]

What I noticed in the patterns in your code is that it seemed to follow the same pattern every time. I can't tell if the patterns changed with the code I provided, but it seems more random.