• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA codes for Random Number Generator

J

James989

Member
Hi,

Someone sent me this codes. Is it VBA codes ?
Is it for random number generator ?

Public i97, j97, u(97) As Double, c As Double, cd As Double, cm As Double, uni As Double
Public Sub Init()
Dim i, ii, j, k, ij, kl, l, m, jj, ivec, i5, j5, s As Double, t As Double
ij = Seed1
kl = Seed2
i = ((ij / 177) Mod 177) + 2
j = (ij Mod 177) + 2
k = ((kl / 169) Mod 178) + 1
l = kl Mod 169
For ii= 1 To 97
s = 0#
t = 0.5
For jj = 1 To 24
m = ((i * j Mod 179) * k) Mod 179
i = j
j = k
k = m
l = (53 * l + 1) Mod 169
If ((l * m) Mod 64) >= 32 Then s = s + t
t = 0.5 * t
Next
u(ii) = s
Next
c = 362436# / 16777216#
cd = 7654321# / 16777216#
cm = 16777213# / 16777216#
i97 = 97
j97 = 33
End Sub

Public Function RND() as Double
uni = u(i97) - u(j97)
If uni < 0# Then uni = uni + 1#
u(i97) = uni
i97 = i97 - 1
If i97 = 0 Then i97 = 97
j97 = j97 - 1
If j97 = 0 Then j97 = 97
c = c - cd
If c < 0# Then c = c + cm
uni = uni - c
If (uni < 0#) Then uni = uni + 1#
RND=uni
end Function


Regards

James
 
Hi,

Rnd function already exists in VBA … Best is to ask the purpose to the code's sender …
 
Hi, James989!

The code is VBA or VB, the full running code with a test sub is copied below, and fills column A of active worksheet with the number of entries specified in constant klMax. It lasts less than 1" for 1000 numbers, 6" for 10K, 54" for 100K, and 9'50" for the whole 1048576, always filling a column which is a slow process even without updating the screen until ending.
Code: 
Option Explicit
 
Public Const Seed1 = 1
Public Const Seed2 = 2
 
Public i97, j97, u(97) As Double, c As Double, cd As Double, cm As Double, uni As Double
 
Public Sub Init()
    Dim I, ii, j, k, ij, kl, l, m, jj, ivec, i5, j5, s As Double, T As Double
    ij = Seed1
    kl = Seed2
    I = ((ij / 177) Mod 177) + 2
    j = (ij Mod 177) + 2
    k = ((kl / 169) Mod 178) + 1
    l = kl Mod 169
    For ii = 1 To 97
        s = 0#
        T = 0.5
        For jj = 1 To 24
            m = ((I * j Mod 179) * k) Mod 179
            I = j
            j = k
            k = m
            l = (53 * l + 1) Mod 169
            If ((l * m) Mod 64) >= 32 Then s = s + T
            T = 0.5 * T
        Next
        u(ii) = s
    Next
    c = 362436# / 16777216#
    cd = 7654321# / 16777216#
    cm = 16777213# / 16777216#
    i97 = 97
    j97 = 33
End Sub
 
Public Function RND() As Double
    uni = u(i97) - u(j97)
    If uni < 0# Then uni = uni + 1#
    u(i97) = uni
    i97 = i97 - 1
    If i97 = 0 Then i97 = 97
    j97 = j97 - 1
    If j97 = 0 Then j97 = 97
    c = c - cd
    If c < 0# Then c = c + cm
    uni = uni - c
    If (uni < 0#) Then uni = uni + 1#
    RND = uni
End Function
 
Sub TestWTFThisDoes()
    Const klmax = 1000000
    Dim I As Long, T As Date
    Init
    Application.ScreenUpdating = False
    T = Now()
    With ActiveSheet.Columns(1)
        .ClearContents
        For I = 1 To .Rows.Count
            .Cells(I, 1).Value = RND()
            If I >= klmax Then Exit For
        Next I
    End With
    Debug.Print Format(Now() - T, "hh:mm:ss")
    Application.ScreenUpdating = True
End Sub
I have no experience with such methods of generating random numbers so I can't say what the code actually does. I can only tell you that:
- it generates numbers greater than zero and less than 1
- there were missing 2 values for Seed1 and Seed2 (I assigned 1 and 2 respectively)

My recommendations, in preferred order:
- check with someone at the source what do the code really does and what values should have the seeds
- run it for the whole column and analyze the data to check for randomness (don't ask me how, please)
- find code examples of those methods, no matter in what language and compare the code structures

Hope it helps.

Regards!
 
Hi James

To add to what has been posted , my understanding of the two sections of code you have posted are as under :

1. The procedure Init is to be run just once to set up all the global variables.

2. Thereafter , you can use the RND() function in your worksheet , with the syntax =RND() or =rnd() ; it will put a random number between 0 and 1 in the cell.

To do the above , you have to paste the entire code in a module.

Whether the code does generate really random numbers I do not know.

Narayan
 
As with all random number generators simply plot a histogram of the distribution to see what it is really doing
So If its generating numbers between 0 and 1 count the number in each 0.05 bucket (20 buckets) and they should be fairly similar when n is high enough probably greater than 500 for 20 buckets
 
At a guess it looks semi random but random around a sine or repetitive triangular function as a base
 
I usually use the VBA Rnd function and I never notice some bad quality random numbers ! …​
 
@Marc L
Hi!
I randomly use the RND function, always with the RANDOMIZE initialization first, and I use to get such strange numbers that look like random values. Would they be a class B (or C, ... or Z) quality numbers? Would its quality be random too? I'm randomly afraid to be following a pattern like the Pi or e digits sequence.
Random regards!
 
You must log in or register to reply here.
Back
Top