I have the following udf that generates random string specifying the needed number of capital letters and small letters and so on.
Code: Select all
Sub Test_GenerateRandomString_UDF()
Debug.Print GenerateRandomString(5, 1, 1, 2, False)
Debug.Print GenerateRandomString(3, 2, 3, 1, True, Range("A4").Value)
End Sub
Function GenerateRandomString(ByVal includeCapitalLetters As Long, ByVal includeSmallLetters As Long, ByVal includeNumbers As Long, ByVal includeSymbols As Long, Optional ByVal includeDate As Boolean = False, Optional ByVal myDate As String = vbNullString) As String
Dim s As String, p As String, t As String, symbols As String, n As Long, i As Long, j As Long
symbols = "!@#$%^&*()_-+={}[]|\:;""'<>,.?/"
For i = 1 To includeCapitalLetters
n = Int(26 * Rnd()) + 65
s = s & Chr(n)
Next i
For i = 1 To includeSmallLetters
n = Int(26 * Rnd()) + 97
s = s & Chr(n)
Next i
For i = 1 To includeNumbers
n = Int(10 * Rnd()) + 48
s = s & Chr(n)
Next i
For i = 1 To includeSymbols
n = Int(Len(symbols) * Rnd()) + 1
s = s & Mid(symbols, n, 1)
Next i
For i = Len(s) To 2 Step -1
j = Int(i * Rnd() + 1)
p = Mid(s, i, 1)
Mid(s, i, 1) = Mid(s, j, 1)
Mid(s, j, 1) = p
Next i
If InStr(1, symbols, Left(s, 1)) > 0 Then
Do
j = Int(Len(s) - 1)
Loop Until Not Mid(s, j + 1, 1) Like symbols
p = Left(s, 1)
Mid(s, 1, 1) = Mid(s, j + 1, 1)
Mid(s, j + 1, 1) = p
End If
If includeDate Then
t = IIf(myDate = vbNullString, "-" & Format(Date, "ddmmyyyy"), "-" & Format(myDate, "ddmmyyyy"))
End If
GenerateRandomString = s & IIf(includeDate, t, Empty)
End Function