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.
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