Code snippets and tips for MS Access and VBA from Extra Mile Data - Generate a random set of characters using VBA function Rnd()

View Code Snippets & Tips

Generate a random set of characters using VBA function Rnd()

May 15

Written by: Extra Mile Data
5/15/2009 8:12 AM

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:

frmRandom

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 support@extramiledata.com.
' 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

Download Example: Random.zip

Trackback Print

Location: Blogs

MS Access and VBA

Tags:

6 comment(s) so far...

Re: Generate a random set of characters using VBA function Rnd()

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.

By Tom on 9/12/2009 3:31 PM

Re: Generate a random set of characters using VBA function Rnd()

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

By Extra Mile Data on 9/12/2009 3:43 PM

Re: Generate a random set of characters using VBA function Rnd()

Great! I added the coded and a loop statement and it worked.

Thanks for the help!

By Tom on 9/15/2009 7:33 AM

Re: Generate a random set of characters using VBA function Rnd()

thanks for sharing

By sam on 11/3/2010 10:35 AM

Re: Generate a random set of characters using VBA function Rnd()

Is it possible to convert the result then to a number so a calculation can be processed ?

By Kevin on 11/3/2010 10:35 AM

Re: Generate a random set of characters using VBA function Rnd()

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)

By Extra Mile Data on 11/3/2010 10:35 AM

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:

Security Code Enter the code shown above in the box below
Add Comment Cancel