Random letter and number generator

[Mikeymike_W]

Hi,

I was hoping to use vba to generate a random code that will contain both letters and numbers, being 9 characters long.
Its going to be a part of a userform so the random code generated will populate a textbox within the userform.

Many thanks in advance to any help you can give,

Mike

 

[JoeMo]

For 9 characters --> RandNumLet(9)

Function RandNumLet(numChars As Integer)
'Generates a string, numChars in length, of random alphanumerics (0-9, A-Z, a-z)
'Can be used on a worksheet --> =RandNumLet(x)
If Not IsNumeric(numChars) Or Not numChars > 0 Then
    RandNumLet = CVErr(xlErrNA)
    Exit Function
End If
Dim picknum As Integer
Dim Lets As String
Dim Nums As String
Dim FinalResult As String
Application.Volatile
Randomize
Do
    picknum = Int((3 * Rnd) + 1)
        Select Case picknum
            Case 1: Nums = CStr(Int(10 * Rnd)) 'generates i-digit string from 0-9
            Case 2: Lets = Chr(Int((26 * Rnd) + 65)) 'generates A-Z
            Case 3: Lets = Chr(Int((26 * Rnd) + 97))  'generates a-z
        End Select
        
    FinalResult = FinalResult & Nums & Lets
    Nums = ""
    Lets = ""
Loop Until Len(FinalResult) >= numChars
RandNumLet = FinalResult
End Function

 

[jmacleary]

Hi there. This will generate what you want:

Sub numbergen()
Dim Characters(62) As String
For i = 0 To 9   ' digits
Characters(i) = Chr(i + 48)
Next i
For i = 10 To 35   ' uppercase alphabet
Characters(i) = Chr(i + 55)
Next i
For i = 36 To 61    ' lowercase alphabet
Characters(i) = Chr(i + 61)
Next i
Randomize
For j = 1 To 9
answer = answer + Characters(Int(62 * Rnd) - 1)

Next j
MsgBox answer

End Sub

 

[Mikeymike_W]

For 9 characters --> RandNumLet(9)

Function RandNumLet(numChars As Integer)
'Generates a string, numChars in length, of random alphanumerics (0-9, A-Z, a-z)
'Can be used on a worksheet --> =RandNumLet(x)
If Not IsNumeric(numChars) Or Not numChars > 0 Then
    RandNumLet = CVErr(xlErrNA)
    Exit Function
End If
Dim picknum As Integer
Dim Lets As String
Dim Nums As String
Dim FinalResult As String
Application.Volatile
Randomize
Do
    picknum = Int((3 * Rnd) + 1)
        Select Case picknum
            Case 1: Nums = CStr(Int(10 * Rnd)) 'generates i-digit string from 0-9
            Case 2: Lets = Chr(Int((26 * Rnd) + 65)) 'generates A-Z
            Case 3: Lets = Chr(Int((26 * Rnd) + 97))  'generates a-z
        End Select
       
    FinalResult = FinalResult & Nums & Lets
    Nums = ""
    Lets = ""
Loop Until Len(FinalResult) >= numChars
RandNumLet = FinalResult
End Function

Thanks for your help JoeMo,

I've never used a function like this before, can I ask how I would then assign the resultant value to textbox1?
Also i cannot use this function in the click event of the command button so i'm unsure how to properly use.

Sorry for being such a pain and i appreciate your help,

Mike

 

[GWteB]

Another solution ...

Function RandomCode() As String
    
    Dim s       As String
    Dim i       As Long
    Dim bFlip1  As Boolean
    Dim bFlip2  As Boolean
    
    For i = 1 To 9
        bFlip1 = CBool(Round(Rnd))
        bFlip2 = CBool(Round(Rnd))
        
        If bFlip1 Then
            If bFlip2 Then
              s = s & Chr(Int((26) * Rnd + 1) + 64)
            Else
              s = s & Chr(Int((26) * Rnd + 1) + 96)
            End If
        Else
            s = s & CStr(Int((9) * Rnd + 1))
        End If
    Next i
    RandomCode = s
End Function

 

[GWteB]

I've never used a function like this before, can I ask how I would then assign the resultant value to textbox1?

Using JoeMo's code:

Me.TextBox1.Value =  RandNumLet(9)

Using mine:

Me.TextBox1.Value =  RandomCode

 

[JoeMo]

Thanks for your help JoeMo,

I've never used a function like this before, can I ask how I would then assign the resultant value to textbox1?
Also i cannot use this function in the click event of the command button so i'm unsure how to properly use.

Sorry for being such a pain and i appreciate your help,

Mike

First be sure you install the function as a standard module in your VBAProject. Then call it like this:

Me.TextBox1.Value =  RandNumLet(9)

 

[Mikeymike_W]

Thanks so much for all of your help and support, I went with JoeMo's solution, i'm sure GWteB's solution would also have worked and I appreciate your response also.

 

[GWteB]

Glad we could help and thanks for letting us know.
As a bonus a function in which the characters to be used can be expanded easily ...

Function RandomCode_2(ByVal argCount As Long) As String

    Const cString   As String = "abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim s As String, i As Long

    If argCount >= 1 Then
        For i = 1 To argCount
            Randomize
            s = s & Mid(cString, Int(Len(cString) * Rnd + 1), 1)
        Next i
        RandomCode_2 = s
    End If
End Function