Randomize & Rnd not generating Random string.

[jmason]

I will start by admitting that I haven't actually done the maths on the probability of a repeat but this just seems wrong.

I needed a randomly generated sequence of chars and used the following code as found on these forums with minor additions to change the range of chars that might be selected.

Public Function RandomizeF(Num1 As Integer, Num2 As Integer)
Dim Rand As String, RandTemp As String
Application.Volatile
getLen = Int((Num2 + 1 - Num1) * Rnd + Num1)
Do
i = i + 1
Do
Randomize
RandTemp = Int((89) * Rnd + 33)
Loop Until (RandTemp = 33) Or (RandTemp >= 35 And RandTemp <= 38) Or (RandTemp >= 47 And RandTemp <= 57) Or (RandTemp >= 64 And RandTemp <= 90) Or (RandTemp >= 97)
Rand = Rand & Chr(RandTemp)
Loop Until i = getLen
RandomizeF = Rand
End Function

Given the possible permutations available here I would have thought the possibilities should be massive however I am getting many repeated values even on a repetitively small sample.

To try it out enter the following in cell A1

=RandomizeF(8,10)

and then Fill down to cell A100. You will see that you get numerous repeats easier with conditional formatting.

So my question, please, is why and how can I avoid it as really I could do with unique values?

Thanks in advance.

John

 

[cerfani]

it actually produces unique values a lot for me, whenever I refresh the calc it usually looks like this... I do a countif for each value and then get a count of the countifs, and it shows i get unique values a lot

 

[sericom]

try removing Randomize

 

[jmason]

try removing Randomize

Ok Well that is a bit odd. That gives me no repeats. I thought the whole point of putting in the Randomize was to ensure a seeded random to give really random random numbers.

Thanks for your help.

Although I don't understand why it works.

Cheers,

John.

 

[jmason]

Thanks for the reply.

Given the other reply and the result of removing the Randomize command can I just confirm that you still had it in your code?

As it seem a little odd that it works for you but needs it removed to work for me.

Cheers,

John.

 

[MARK858]

Possibly you are experiencing the below effect....

VB's Randomize Function Should Be Run Only Once Per Session

 

[Scott Huish]

Does this do what you want?

Public Function RandomizeF(Num1 As Integer, Num2 As Integer)
Dim Rand As String, RandTemp As String, i As Long
Application.Volatile
Randomize
getlen = Int((Num2 + 1 - Num1) * Rnd + Num1)
For i = 1 To getlen
    Do
        RandTemp = Int((89) * Rnd + 33)
        Select Case RandTemp: Case 33, 35 To 38, 47 To 57, 64 To 90, 97: Rand = Rand & Chr(RandTemp): Exit Do: End Select
    Loop
Next
RandomizeF = Rand
End Function

 

[jmason]

Does this do what you want?

I have't tried but I suspect yes as it removes the Randomize out side of the loop also nice bit of tidying of the only choosing certain Chars section.

May switch to that version.

Cheers,

John.

 

[sericom]

The problem will probably still be there. The link posted by MARK858 shows you how to implement it if needed.