I have come across a rather odd issue with excel / VBA and the RND function. The issue is that repeatedly reinitializing the RND function with Randomize results in a lot of repeated "random" values; what looks like a random number generator with a very short period.

Now my understanding from these two MS links;

http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx
http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx

Is that the randomize statement reinitialize the RND function using a seed value from the system clock. Since random numbers generated after repeated randomize statements are not the same, it would seem that the seed values generated by the system timer are in some way conspiring to produce a short period of random numbers.

And surprise surprise there seems to be an issue with it;
http://support2.microsoft.com/defaul...b;en-us;120587
but I am not sure why or how this produces the results I am seeing.

Can anyone shed some light on whats going on?

See code below. If I enable the randomize statement in rand_wrapper() function (highlighted with lots of #########s) I get a lot of repeated values for the random number, if not I dont.

Option Explicit

Public Sub rv_test_2()

Dim lng_no_iterations As Long
Dim i As Long
Dim arr_dbl_norm_rvs() As Double

lng_no_iterations = 10000 '// No. Iterations.


ReDim arr_dbl_norm_rvs(1 To lng_no_iterations)

Randomize

For i = 1 To lng_no_iterations

    arr_dbl_norm_rvs(i) = rand_wrapper ' RandNorm_Leva
Next i

Call qsort(arr_dbl_norm_rvs, 1, lng_no_iterations)

i = lng_no_iterations  '// Break point do what you like with the array, I was just looking at it in the locals window.

End Sub

Public Function rand_wrapper()

Randomize '//#########################################################
rand_wrapper = Rnd

End Function

'// QuickSort sorting algorithm for ordering numeric data in an array - better than bubble sort etc.
'// ByVal is to prevent changing of original values, as equivalent of paste value rather than link.

Public Sub qsort(ByRef sortarray As Variant, ByVal leftindex As Long, ByVal rightindex As Long)

Dim dbl_compvalue#, dbl_tempnum As Double
Dim i As Long
Dim j As Long

i = leftindex
j = rightindex
dbl_compvalue = sortarray(Int((i + j) / 2))

Do

    Do While (sortarray(i) < dbl_compvalue And i < rightindex)
        i = i + 1
    Loop
    
    Do While (dbl_compvalue < sortarray(j) And j > leftindex)
        j = j - 1
    Loop
    
    If i <= j Then
        dbl_tempnum = sortarray(i)
        sortarray(i) = sortarray(j)
        sortarray(j) = dbl_tempnum
        i = i + 1
        j = j - 1
    End If
    
Loop While i <= j

If leftindex < j Then qsort sortarray, leftindex, j
If i < rightindex Then qsort sortarray, i, rightindex

End Sub