Random String generator throwing out duplicates issue

[JumboCactuar]

Hi,
i'm using this code to generate a unique 4 character string, but hours later i'm seeing it generate the same 4 character strings again

Am i doing something wrong?

Function GenerateRandomString() As String
    Dim i As Integer
    Dim strResult As String
    Dim strChars As String
    strChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789"

    ' Initialize the random-number generator.
    Randomize

    For i = 1 To 4
        strResult = strResult & Mid(strChars, Int((Len(strChars) * Rnd) + 1), 1)
    Next i

    GenerateRandomString = strResult
End Function

 

[6StringJazzer]

Random strings are not unique. They are random. There are 1,500,625 different four-character combinations from that set, so the chances of a duplicate are small but certainly it's possible.

Also, when it comes to computers, random number generators are not truly random (unless they are sampling some real-world random process). They should give a random distribution, but that is not the same thing as random numbers. The algorithm might be sufficiently complex that the next number cannot be predicted, but that's still not the same thing as "random." (There is a famous story about a digital slot machine that everybody thought generated random numbers but a Russian syndicate figured out how to predict the sequence and started cleaning up in the casinos until they were caught.)

Can you explain what you mean by "hours later i'm seeing it generate the same 4 character strings again"? Are you running this from scratch hours apart but getting the same sequence? Or are you just getting some duplicates? How many are you generating and how many duplicates are you seeing?

Most importantly, how are you using the results?

 

[JumboCactuar]

Hi,
so i generate 10 random strings just now as an example:

ME1Q
J6LJ
QCFS
DUGX
VKLZ
EWHZ
86YB
TUHG
PRRZ
NGDR

Hours later, i would see the 10 same strings generated in the same order

I thought it was due to Randomize and the seed, but i think i'm using that correctly.

String is part of a filename including the current date and needs to be unique everytime.

An example filename is: 20240829ME1Q.txt
and i need to ensure i dont generate the same on a given day

 

[Micron]

I got a different estimation for Permutations and Combinations for 35 characters taken 4 at a time, but no matter. Depending on how frequent you are doing this, appending _ and the time value as milliseconds may be more reliable. So if you did this operation within 10 seconds of each other you''d need to use abt 7 decimal places. If once per day, then fewer since the date portion would be different even if a user managed to run the process at the exact same millisecond on another day.

 

[6StringJazzer]

I got a different estimation for Permutations and Combinations for 35 characters taken 4 at a time

My number is with replacement since that's what the code does.

 

[6StringJazzer]

Hours later, i would see the 10 same strings generated in the same order

You should not be calling Randomize in your function. You should call it one time as the first thing in the top-level sub, before the loop.

I am not sure that will fix your problem, but it is the correct way to use Randomize.

 

[StephenCrump]

If you want to generate a batch of unique strings, you could use a VBA Dictionary to test for duplicates.

Have a look here, for example: Macros Serial number generation with repeatability comparison base

Post #10 provides a workbook (although some of the code was updated later in the thread).

 

[6StringJazzer]

The code provided should generate four-character strings with a negligible chance of duplication. To duplicate an entire sequence of 10 strings means something else is wrong. I would like to see all the OP's code, not just the low-level function.

The only way I know that a series of numbers should be repeated is this. This is generally for development purposes.

To repeat sequences of random numbers, call Rnd with a negative argument immediately before using Randomize with a numeric argument.

 

[StephenCrump]

To duplicate an entire sequence of 10 strings means something else is wrong.

Yes, I agree.

VBA's Rnd() function has a cycle length of only 2^24.

Sub Test1()

    Const N = 16777216
    Dim i As Long, extra As Long
    Dim d As Double

    extra = 50
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Randomize

    For i = 1 To N + extra
        d = Rnd()
        If i < extra Then Range("A" & i).Value = d
        If i > N And i < N + extra Then Range("B" & i - N).Value = d
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

But repeated use of Randomize will generate duplicates far quicker:

Sub Test2()

    Const N = 16777216
    Dim i As Long, m As Long, count As Long, MaxToShow As Long
    Dim d(1 To N, 1 To 1) As Double
    Dim dic As Scripting.Dictionary
    
    Set dic = New Scripting.Dictionary
    MaxToShow = 5 'say
    
    For i = 1 To N + 1
        Randomize
        d(i, 1) = Rnd()
        On Error Resume Next
        dic.Add d(i, 1), d(i, 1)
        If Err <> 0 Then
            count = count + 1
            If count > MaxToShow Then Exit For
            m = Application.Match(d(i, 1), d, 0)
            MsgBox m & "th: " & d(m, 1) & vbCr & i & "th: " & d(i, 1)
        End If
        On Error GoTo 0
    Next i

End Sub

It's not immediately obvious why a sequence of 10 codes should repeat. Perhaps something to do with Excel's recalculation of the individual UDF values?

 

[StephenCrump]

A quick test .... which for me generated several repeat sequences up to length 5.

Sub Test3()

    Dim i As Long
    Application.ScreenUpdating = False
  
    For i = 1 To 10000
        Range("A" & i).Formula2 = "=GenerateRandomString()"
    Next i
  
    Application.ScreenUpdating = True

End Sub

After removing Randomize from GenerateRandomString, the same code didn't generate any repeat sequences, but still generated nearly 100 single repeats.