VBA module to generate Unique random alphanumeric strings of random length (1 Viewer)

world33

Registered User.
Local time
Tomorrow, 10:15
Joined
Oct 24, 2006
Messages
21
I have been searching for a VBA module to generate Unique random alphanumeric strings of random length (or length between X and Y number of characters)

I found the following one:


Public Function GenerateUniqueSequence(numberOfCharacters As Integer) As String

Dim random As String ' * 8 'fixed length string with 8 characters
Dim j As Integer
Dim ch As Integer ' each character

random = ""

For j = 1 To numberOfCharacters
random = random & GenerateRandomAlphaNumericCharacter
Next

GenerateUniqueSequence = random

End Function

Public Function GenerateRandomAlphaNumericCharacter() As String

'Numbers : 48 is '0', 57 is '9'
'LETTERS : 65 is 'A', 90 is 'Z'
'letters : 97 is 'a', 122 is 'z'

GenerateRandomAlphaNumericCharacter = ""

Dim i As Integer

Randomize
i = (Rnd() * 2) + 1 'One chance out of 3 to choose one of 3 catégories

Randomize
Select Case i
Case 1 'Numbers
GenerateRandomAlphaNumericCharacter = Chr(Rnd() * 9 + 48)
Case 2 'LETTERS
GenerateRandomAlphaNumericCharacter = Chr(Rnd() * 25 + 65)
Case 3 'letters
GenerateRandomAlphaNumericCharacter = Chr(Rnd() * 25 + 97)
End Select

End Function

Source: https://stackoverflow.com/questions...al-basic-generate-random-string-in-text-field


The only issue I have with this function is that it automatically changes random string every time I click on the generated random string in the query Datasheet View. I would like the strings to be static not dynamic. Any way to modify the above module so that once the random strings are generate they cannot change?

Alternatively is there any other VBA module that allows me to generate Unique random alphanumeric strings of random length ?

Thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:15
Joined
May 7, 2009
Messages
19,169
assign the value generated by the
random generator to a Public Variable or
Tempvars. then you will just check this
variable on the random generator and
exit the function if it does.
you must find a way to set back the
value to empty string on your code.
something like this:

Add a Standard Module on your VBE and this:

Public gstrRandomNumber As String


on your random generator:

Public Function GenerateUniqueSequence(numberOfCharacters As Integer) As String
...
...
If Len(gstrRandomNumber) > 0 Then
GenerateUniqueSequence = gstrRandomNumber
Exit Function
End If

' rest of code the generage the number
..
..

gstrRandomNumber = random
GenerateUniqueSequence
End Function


'* now you need a way to reset to blank
the gstrRandomNumber. If you use form
(datasheet, continuous, single, etc)
you can initialize this variable on its
Open or Load Event:

Private Sub Form_Open(Cancel As Integer)
gstrRandomNumber = ""
End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:15
Joined
Jan 23, 2006
Messages
15,364
world33,

Isn't the fact that the "random numbers/string" changes each time - the whole idea of random?
Do you want repeatable or constants?

Note: It is the Randomize in the function that ensures the seed is reset on each
execution. You could try to comment out that line and experiment.
 

plog

Banishment Pending
Local time
Today, 18:15
Joined
May 11, 2011
Messages
11,613
To further jdraw's comment and set it in VBA, here's a function that will return a random, static string:

Code:
Public Function GenerateUniqueSequence() As String

GenerateUniqueSequence = "1msdlkf87JFEY6509GVEf"

End Function

If you want something else, perhaps you could explain the ultimate goal. Take us a step higher and tell us the ends this random string is the means to.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:15
Joined
Feb 19, 2002
Messages
42,977
Don't call the functions if you don't want to change the values.

What event are you using to call the functions?
 

MarkK

bit cruncher
Local time
Today, 16:15
Joined
Mar 17, 2004
Messages
8,178
The Scripting.FileSystemObject exposes a .GetTempName method, which returns a random filename. If you strip off the garbage, you can use the characters it returns and do something like...
Code:
Public Function GetRandomName(Optional Length As Byte = 10) As String
    Dim i As Integer
    Dim tmp As String
    
    With CreateObject("Scripting.FileSystemObject") [COLOR="Green"]'late bound[/COLOR]
        For i = 1 To CInt((Length / 5) + 0.5)       [COLOR="green"]'each call to .GetTempName will give us 5 chars[/COLOR]
            tmp = tmp & Mid(.GetTempName, 4, 5)     [COLOR="green"]'only keep the 5 chars we need[/COLOR]
        Next i
    End With
    GetRandomName = Left(tmp, Length)               [COLOR="green"]'pare it down to the requested length[/COLOR]
End Function
Just an idea you might be able to use...
Mark
 

Mark_

Longboard on the internet
Local time
Today, 16:15
Joined
Sep 12, 2017
Messages
2,111
I think the solution to the question you are trying to ask is in
every time I click on the generated random string in the query Datasheet View

Are you defaulting a field value to your random string that you then want to remain consistent? Just to make sure, you are fine with there being duplicate values?

If yes, then when you first enter the record you would want to do something like this:

Code:
DIM asString as String
DIM afBreak as Integer

afBreak = 0
IF File.Field = NULL then 
   DO
      asString =  GenerateUniqueSequence(<NUMBER>)
      IF DLookup("[Field]", "File", "[Field] = " & asString) = NULL Then
         File.Field = asString
         afBreak = 1
      end If
   Until afBreak = 1
End IF

Please note, I haven't tried this myself as I've not needed to.
 

world33

Registered User.
Local time
Tomorrow, 10:15
Joined
Oct 24, 2006
Messages
21
Thanks all for the feedback.

I use the following string to call the module in the query Universities by Name:

UpdateID: GenerateUniqueSequence(Rnd([SITES]![ID])*15+15)

I attach 2 screenshots for those that did not understand the issue. I do need unique random strings but I do not need them to change once they are populated in a query just because I click on the cell where they are shown.

I have to modify somehow the function or the way I call it so that this occurrence does not happen anymore.

Thanks again for any help
 

Attachments

  • 1 Random strings before clicking on a record.jpg
    1 Random strings before clicking on a record.jpg
    94.6 KB · Views: 264
  • 2 Random strings after clicking on a record.jpg
    2 Random strings after clicking on a record.jpg
    97.1 KB · Views: 216

Mark_

Longboard on the internet
Local time
Today, 16:15
Joined
Sep 12, 2017
Messages
2,111
world33

Only assign a value to your UpdateID if UpdateID = Null. If it does not exist, make one. If it does exist, leave it alone.

Where do you have your code currently? What event fills UpdateID?
 

world33

Registered User.
Local time
Tomorrow, 10:15
Joined
Oct 24, 2006
Messages
21
Hi Mark,

The UpdateID is a calculated query field using this formula:
UpdateID: GenerateUniqueSequence(Rnd([SITES]![ID])*15+15)
It is not present in any table so there is no point, correct me if I am wrong, to have UpdateID = Null . It is a complete new query field.
The function is in a Module.
 

Minty

AWF VIP
Local time
Today, 23:15
Joined
Jul 26, 2013
Messages
10,355
As it is calculated to be random at the time the query is run, any time you amend the data the function will re-run.

That is how a calculated field works. Think about it logically - if your calculated field was LineTotal: Qty * UnitPrice and you clicked in the Linetotal control, if it didn't update to the current value it would be useless.
 

world33

Registered User.
Local time
Tomorrow, 10:15
Joined
Oct 24, 2006
Messages
21
So is there any modification I can make to keep the same values?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:15
Joined
May 7, 2009
Messages
19,169
like i suggested, create a Public variable.
use Form (datasheet) to display your Query.

So everytime you open the Form, you Reset the
variable:

Private Sub Form_Open(Cancel As Integer)
gstrRandomNumber = ""
End Sub


'**
'**
follow the code from the first post (#2).
 

Mark_

Longboard on the internet
Local time
Today, 16:15
Joined
Sep 12, 2017
Messages
2,111
So is there any modification I can make to keep the same values?

If you want the same value for a given ID, store it in a file. Queries are temporary images of data, not a place to store data.

The alternative is to create a function that will ALWAYS generate the same value for a given ID, but this would not then be a "Random" value.

From your description at top, I had thought it was a field in your table. Using your code as a default value (or filling it in if null when you enter the update form) would allow you to keep the same "UpdateID" for any given ID. Likewise if this would need to be unique (and associating it with a given ID lends me to believe this is what you are looking for) you could us DLookup to then make sure it is truly a unique random value that you are storing.
 

Users who are viewing this thread

Top Bottom