Random letter and number generator

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
For 9 characters --> RandNumLet(9)
VBA Code:
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
 
Solution

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there. This will generate what you want:
VBA Code:
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

Board Regular
Joined
Feb 25, 2016
Messages
171
For 9 characters --> RandNumLet(9)
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Another solution ...

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
Me.TextBox1.Value =  RandNumLet(9)

Using mine:
VBA Code:
Me.TextBox1.Value =  RandomCode
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
Me.TextBox1.Value =  RandNumLet(9)
 

Mikeymike_W

Board Regular
Joined
Feb 25, 2016
Messages
171
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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 ...

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,830
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top