[trev.h]

I'm after an expert to check my VBA code for random number generation.
I'm generating numbers from 000 to 199.

I was experiencing a problem when restarting the computer, the first numbers were not random, their was a consistency. That's what the use of seed is for, it uses time hh:mm:ss and generates a random number ssmm which will be in the range 0 to 5959 (0000-0059, 5900-5959, get the idea?). Seconds first to create as large as possible random number first. The seed is then used to start the randomize.

Anyway, here's the code and I'm looking for an expert to confirm that my numbers will always be truely random.


Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Sub Random()

Dim Num_1, Num_2, Num_3
Dim seed As Integer
Dim i, t, a1, b2 As Integer
ActiveSheet.Cells(1, 1).Font.ColorIndex = 48
ActiveSheet.Cells(1, 1).Font.Bold = False
ActiveSheet.Cells(1, 2).Value = "-"
ActiveSheet.Cells(1, 2).Font.ColorIndex = 48
ActiveSheet.Cells(1, 2).Font.Bold = False
ActiveSheet.Cells(1, 3).Value = "-"
ActiveSheet.Cells(1, 3).Font.ColorIndex = 48
ActiveSheet.Cells(1, 3).Font.Bold = False

seed = Mid(Time(), 7, 2) & Mid(Time(), 4, 2)
Randomize (seed)

i = 1
t = 1
Do
t = t + 5
Sleep t
ActiveSheet.Cells(i, 1).Value = ""
Num_1 = Int((1 - 0 + 1) * Rnd + 0)
ActiveSheet.Cells(i, 1).Value = Num_1
Loop While t < 20
ActiveSheet.Cells(i, 1).Font.ColorIndex = 3
ActiveSheet.Cells(i, 1).Font.Bold = True

seed = Mid(Time(), 7, 2) & Mid(Time(), 4, 2)
Randomize (seed)

t = 1
Do
t = t + 5
Sleep t
ActiveSheet.Cells(i, 2).Value = ""
Num_2 = Int((9 - 0 + 1) * Rnd + 0)
ActiveSheet.Cells(i, 2).Value = Num_2
Loop While t < 20
ActiveSheet.Cells(i, 2).Font.ColorIndex = 3
ActiveSheet.Cells(i, 2).Font.Bold = True

t = 1
Do
t = t + 5
Sleep t
ActiveSheet.Cells(i, 3).Value = ""
Num_3 = Int((9 - 0 + 1) * Rnd + 0)
ActiveSheet.Cells(i, 3).Value = Num_3
Loop While t < 20
ActiveSheet.Cells(i, 3).Font.ColorIndex = 3
ActiveSheet.Cells(i, 3).Font.Bold = True

End Sub

[Zack Barresse]

Hi there,

Couple of things I don't understand.

These variables..

Dim i, t, a1, b2 As Integer

.. all EXCEPT b2 will be dimensioned as Variant types. You must specify EACH variable and their types. So that should become...

Dim i as long, t as long, a1 as long, b2 As Integer

Also, there is little to no need for declaring Integer types. When VBA declares a variable (or compiles rather) to an Integer type, it first converts it to Long, then shortens it up and converts it again. And since Long types can hold everything an Integer type can plus more, there is almost no need to even declare a variable Integer type. Plus Long is shorter to type.

I do not understand your logic. What do you want your seed to be? You want a time value of ss:mm seconds in the minute slot and minutes in the second slot? Shall it be a time value or string value? What are you after with that? From what I understood you would be looking at something like this ..

seed = TimeValue("00:" & Format(Second(Time), "00") & ":" & Format(Minute(Time), "00"))

And if you want it a time value, declare your variable as the Date type instead of Integer.

Plus there are a few variables that you just don't need, which means you could trim your overall code down quite a bit. For example, there is no need to set your font color at the beginning and then again after each iterative Do/Loop, it's just a waste of time. With the above stated, maybe you could look at the following...

Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Random()

    Dim seed As Date
    Dim i, t, a1, b2 As Integer
    Range("A1:C1").Font.ColorIndex = 3
    Range("A1:C1").Font.Bold = True
    
    seed = TimeValue("00:" & Format(Second(Time), "00") & ":" & Format(Minute(Time), "00"))
    Randomize (seed)
    t = 1
    Do
        t = t + 5: Sleep t
        ActiveSheet.Cells(1, 1).Value = Int(2 * Rnd)
    Loop While t < 20
    seed = TimeValue("00:" & Format(Second(Time), "00") & ":" & Format(Minute(Time), "00"))
    Randomize (seed)
    t = 1
    Do
        t = t + 5: Sleep t
        ActiveSheet.Cells(1, 2).Value = Int(10 * Rnd)
    Loop While t < 20
    seed = TimeValue("00:" & Format(Second(Time), "00") & ":" & Format(Minute(Time), "00"))
    Randomize (seed)
    t = 1
    Do
        t = t + 5: Sleep t
        ActiveSheet.Cells(1, 3).Value = Int(10 * Rnd)
    Loop While t < 20

End Sub

Not sure why you would want to use the Sleep API unless it was for visual effect only.

As far as randomization goes, the Rnd() function is based on a human programmed algorithm. This means that it is never truly and wholely random. If somebody knew the algorithm, or spent time disecting it, one could predict the outcome every time. Nothing in computers is ever random, because they only do what you tell it to do. Only humans can attempt to do true random findings. And them, sometimes the so-called earnest findings are indeed random as well.

[trev.h]

Thanks for the feedback firefytr.

Variable declare. I didn't know that, thanks.

Seed. I welcome some advise here, I might be talking a load of rubbish, if so please tell me.
What I was getting without the seed, was not a random number. Each time a ran the routine after the PC was restarted, I was getting extremely similar results. I figured that I need to do some kind of initialising of the Random number generator, hence the seed, which fixed the problem.

The theory was to use a random number as the seed, but how to create one? I figured I could use ssmm to give me a decent spread of numbers with which to initialise with.

I see what you're doing with seed, nice and simple. To be honest, I thought the Randomize(seed) would require an integer, not a date (you learn something every day).

Sleep, yes it's there for the visual effect.

Hopefully with the random seed generation, the numbers generated should be pretty random.

This is being used as a charity draw for my rugby club. I need to ensure that:
1. The numbers are as random as possible.
2. No numbers in the range 000 to 199 will every by excluded through faulty coding or as a quirk of Rnd.

Thanks again for your help.

[slurpee55]

Although Rand() is an algorithm and by that nature, not truly random, nonetheless, you could use it in a variety of ways that would make it more than random enough for everyday purposes. Perhaps you could sum two columns of random numbers, multi9ply it by 1000, then use the left and right functions to select n numbers from one side and n from the other.
Something like this:
0.631335470593548 0.0199440540608817 651.27952465443 651.443
Random? No, but certainly random enough for most daily events. It wouldn't do for a science project, but for a charity event, well, I would have no problems with it.