The following procedure can be used to generate a random string of specified length by the user.

Furthermore, the procedure allows the user to specify whether to include: numeric, uppercase and/or lowercase characters in the generated string. It requires no reference libraries and should work in any VBA application (MS Word, MS Excel, MS Access, …).

Thus, it is a great little function that can be used to generate random password, random string, etc.

'---------------------------------------------------------------------------------------
' Procedure : GenRandomStr
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate a random string (alpha, numeric or alphanumeric)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' iNoChars      - No of characters the random string should be in length
' bNumeric      - Should the random string include Numeric characters
' bUpperAlpha   - Should the random string include Uppercase Alphabet characters
' bLowerAlpha   - Should the random string include Lowercase Alphabet characters
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' GenRandomStr(12, True, False, True)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-11-11              Initial Release
' 2         2018-06-11              Copyright update
'                                   Added missing variable declaration to make the
'                                       function Option Explicit compliant
'---------------------------------------------------------------------------------------
Function GenRandomStr(iNoChars As Integer, _
                      bNumeric As Boolean, _
                      bUpperAlpha As Boolean, _
                      bLowerAlpha As Boolean)
    On Error GoTo Error_Handler
    Dim AllowedChars()        As Variant
    Dim iNoAllowedChars       As Long
    Dim iEleCounter           As Long
    Dim i                     As Integer
    Dim iRndChar              As Integer

    'Initialize our array, otherwise it throws an error
    ReDim Preserve AllowedChars(0)
    AllowedChars(0) = ""

    'Build our list of acceptable characters to use to generate a string from
    'Numeric -> 48-57
    If bNumeric = True Then
        For i = 48 To 57
            iEleCounter = UBound(AllowedChars)
            ReDim Preserve AllowedChars(iEleCounter + 1)
            AllowedChars(iEleCounter + 1) = i
        Next i
    End If
    'Uppercase alphabet -> 65-90
    If bUpperAlpha = True Then
        For i = 65 To 90
            ReDim Preserve AllowedChars(UBound(AllowedChars) + 1)
            iEleCounter = UBound(AllowedChars)
            AllowedChars(iEleCounter) = i
        Next i
    End If
    'Lowercase alphabet -> 97-122
    If bLowerAlpha = True Then
        For i = 97 To 122
            ReDim Preserve AllowedChars(UBound(AllowedChars) + 1)
            iEleCounter = UBound(AllowedChars)
            AllowedChars(iEleCounter) = i
        Next i
    End If

    'Build the random string
    iNoAllowedChars = UBound(AllowedChars)
    For i = 1 To iNoChars
        Randomize
        iRndChar = Int((iNoAllowedChars * Rnd) + 1)
        GenRandomStr = GenRandomStr & Chr(AllowedChars(iRndChar))
    Next i

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GenRandomStr" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function