This VBA function uses the Randomize() and Rnd() functions to generate a random string of characters with a specific length. The included Microsoft Access example applies the function to a simple form that can be used to create a password.
When the MS Access database opens, the form that appears looks like this:
Enter a Length, click the Go button, and a random string will appear in the Results. To use the Results, you may click the Copy button and then paste the results wherever it is needed.
The On Click event for the Go button validates the Length and then calls the Random() function. The On Click event for the Copy button uses the DoCmd.RunCommand acCmdCopy action to place the Results on the clipboard.
The Random() function below uses alphas and numerics in the base character list. You could also add special characters to the base for variety.
Random() Code:
Public Function Random(RLength As Integer) As String ' This function creates a string of random characters, both numbers ' and alpha, with a length of RLength. It uses Timer to seed the Rnd ' function. ' Random() Version 1.0.0 ' Copyright © 2009 Extra Mile Data, www.extramiledata.com. ' For questions or issues, please contact [email protected]. ' Use (at your own risk) and modify freely as long as proper credit is given. On Error GoTo Err_Random Dim strTemp As String Dim intLoop As Integer Dim strCharBase As String Dim intPos As Integer Dim intLen As Integer ' Build the base. strCharBase = "01234ABCDEFGHIJKLMNOPQRSTUVWXYZ" _ & "abcdefghijklmnopqrstuvwxyz56789" ' Get it's length. intLen = Len(strCharBase) ' Initialize the results. strTemp = String(RLength, "A") ' Reset the random seed. Rnd -1 ' Initialize the seed using Timer. Randomize (Timer) ' Loop until you hit the end of strTemp. Replace each character ' with a character selected at random from strCharBase. For intLoop = 1 To Len(strTemp) ' Use the Rnd function to pick a position number in strCharBase. ' If the result exceeds the length of strCharBase, subtract one. intPos = CInt(Rnd() * intLen + 1) If intPos > intLen Then intPos = intPos - 1 ' Now assign the character at that position in the base to the ' next strTemp position. Mid$(strTemp, intLoop, 1) = Mid$(strCharBase, intPos, 1) Next ' Return the results. Random = strTemp Exit_Random: On Error Resume Next Exit Function Err_Random: MsgBox Err.Number & " " & Err.Description, vbCritical, "Random" Random = "" Resume Exit_Random End Function
Making Microsoft Access Work. Call (505) 369-1650.
Nice!
I am appending the code generated to a table called PC. Is there code that could be added to random to ensure the generated code is unique? (not in table PC)
Thanks for the code and more importantly for adding descriptions to describe what the code does.
Tom,
There are several ways to make sure that you only add unique values to the PC table. Here are two that you can try.
(1) Put a unique index on the field in PC. After that, you will not be able to append a repeated value.
(2) After you get the value from the function, use a function like DLookup or DCount to determine if it is already in the PC table. If it is, then run the function again. For example, if you’ve stored the results in a variable named strRandom and your field name is Password:
If DCount("Password","PC","Password='" _& strRandom & "'") > 0 Then
' Code here to repeat your call to Random().
End If
Thanks for the help!
Is it possible to convert the result then to a number so a calculation can be processed ?
If you intend to end with a number, I think you would start with a number. You could remove the alpha characters from strCharBase if your goal is to get a specific number of digits in the result (though you might get a 0 at the beginning if you left that in strCharBase). If your goal is just a random number and you don’t care about the length, you could do something different, like this:
Const lngcLower As Long = 1
Const lngcUpper As Long = 2147483647
‘ Reset the random seed.
Rnd -1
‘ Initialize the seed using Timer divided by a
' numeric date.
Randomize (Timer)
‘ Return a random number between lngcLower and
' lngcUpper.
GetRandomID = Int((lngcUpper – lngcLower + 1) _
* Rnd + lngcLower)
thanks for sharing
This is a great function! Is there a way to make sure that the set of characters that are returned return at least one Upper case, one Lower case, one number, and maybe one special character? I would like to use this as a random password generator.
To make sure you have specific characters, I think you could create a boolean variable for each type (one for upper, like blnUpper, one for lower, one for number, etc, and then set each one to true as you satisfy the criteria. Loop until all of them are satisfied.
I took some inspiration from this thread and here is my approach to this task. With creativity you can use it in fields for unique identifiers (my case) or … who knows, be creative.
Hopefully someone can use it some how:
Sub Generate_Random_Serial()Dim lngUnique_Random_Number_One As Long
Dim lngUnique_Random_Number_Two As Long
Dim stUnique_Random_Letter_One As String
Dim stUnique_Random_Letter_Two As String
Dim strRandomly_Generated_Serial As String
' Reset the random seed.
Rnd -1
' Initialize the seed using Timer.
Randomize (Timer)
' Randomly choose a number from 1 to 8,923,875
lngUnique_Random_Number_One = _
Int((8923875 – 1 + 1) * Rnd() + 1)
lngUnique_Random_Number_Two = _
Int((8923875 – 1 + 1) * Rnd() + 1)
' Randomly choose a character (letter)
stUnique_Random_Letter_One = _
chr(CInt(Rnd * 26) + 65)
stUnique_Random_Letter_Two = _
chr(CInt(Rnd * 26) + 65)
' Concatenate and build the serial
strRandomly_Generated_Serial = _
lngUnique_Random_Number_One _
& stUnique_Random_Letter_One _
& lngUnique_Random_Number_Two _
& stUnique_Random_Letter_Two
MsgBox "Requested serial is: " _
& strRandomly_Generated_Serial, vbInformation, _
strRandomly_Generated_Serial
End Sub
Viscar, thank you for your example.