HJ
Cycling in Scotland
- Location
- Auld Reekie
I need to generate a large number of random passwords for a list of users in a excel spreadsheet, can anyone suggest a macro script for doing this?
Sub PasswordGenerate()
'Creates a password 10 characters long
'with at least 1 upper & lower case letters, 1 number, and 1 special character
Dim l, x, c As Integer
Dim p, p1, p2 As String
p = ""
'Force at least one of each character type
c = RandBetween(35, 38) 'Special characters #, $, %, &
p = p & Chr(c)
c = RandBetween(48, 57) 'Number
p = p & Chr(c)
c = RandBetween(65, 90) 'Upper case
p = p & Chr(c)
c = RandBetween(97, 122) 'Lower case
p = p & Chr(c)
'Create a string variable 6 characters long, randomising characters, numbers, upper & lower case letters
For l = 1 To 6
x = Empty 'Just in case
x = RandBetween(1, 4) 'Choose a random number
If x = 1 Then c = RandBetween(35, 38) 'Specials
If x = 2 Then c = RandBetween(48, 57) 'Numbers
If x = 3 Then c = RandBetween(65, 90) 'Uppers
If x = 4 Then c = RandBetween(97, 122) 'Lowers
p = p & Chr(c) 'concatenate old password & random character
Next l
'Now randomly re-arrange the characters in the password string
x = RandBetween(0, 9)
p1 = Left(p, x)
p2 = Right(p, Len(p) - x)
p = p2 & p1 'Password is ready!
Selection.Value = p
End Sub
If you add in the data editor and use its the random numbers aren't recalculated.amnesia said:Does the password have to be symbol/alpha/numeric, or is just numeric OK (like a PIN) ?
If so, you can use the RAND or RANDBETWEEN function...
=RANDBETWEEN(1000000,999999)
However, the random number will be recalculated every time you hit F9 (for example) so you might want to do something with the result.
Fine for a one-off though.
amnesia said:Does the password have to be symbol/alpha/numeric, or is just numeric OK (like a PIN) ?
If so, you can use the RAND or RANDBETWEEN function...
=RANDBETWEEN(1000000,999999)
However, the random number will be recalculated every time you hit F9 (for example) so you might want to do something with the result.
Fine for a one-off though.
HJ said:I need to generate over 100 strong passwords, i.e. symbol/alpha/numeric not just numeric, too easy to crack.
HJ said:Well after a bit of scratching around I came up with this
Which does the sort of thing I want, but I can figure out how to get it to run as a function, so I don't have to keep running a macro...Code:Function PasswordGenerate() 'Creates a password 10 characters long 'with at least 1 upper & lower case letters, 1 number, and 1 special character Dim l, x, c As Integer Dim p, p1, p2 As String p = "" 'Force at least one of each character type c = RandBetween(35, 38) 'Special characters #, $, %, & p = p & Chr(c) c = RandBetween(48, 57) 'Number p = p & Chr(c) c = RandBetween(65, 90) 'Upper case p = p & Chr(c) c = RandBetween(97, 122) 'Lower case p = p & Chr(c) 'Create a string variable 6 characters long, randomising characters, numbers, upper & lower case letters For l = 1 To 6 x = Empty 'Just in case x = RandBetween(1, 4) 'Choose a random number If x = 1 Then c = RandBetween(35, 38) 'Specials If x = 2 Then c = RandBetween(48, 57) 'Numbers If x = 3 Then c = RandBetween(65, 90) 'Uppers If x = 4 Then c = RandBetween(97, 122) 'Lowers p = p & Chr(c) 'concatenate old password & random character Next l 'Now randomly re-arrange the characters in the password string x = RandBetween(0, 9) p1 = Left(p, x) p2 = Right(p, Len(p) - x) p = p2 & p1 'Password is ready! Selection.Value = p return p End Function