excel random password generator?

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?
 

Gerry Attrick

Lincolnshire Mountain Rescue Consultant
There is an online generator here if that helps.
http://www.pctools.com/guides/password/
 
OP
HJ

HJ

Cycling in Scotland
Location
Auld Reekie
Well after a bit of scratching around I came up with this

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

amnesia

Free-wheeling into oblivion...
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.
If you add in the data editor and use its the random numbers aren't recalculated.
 
OP
HJ

HJ

Cycling in Scotland
Location
Auld Reekie
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.
I need to generate over 100 strong passwords, i.e. symbol/alpha/numeric not just numeric, too easy to crack.
 

amnesia

Free-wheeling into oblivion...
HJ said:
I need to generate over 100 strong passwords, i.e. symbol/alpha/numeric not just numeric, too easy to crack.

Ironic, isn't it... a 6 digit random number (could easily make it as long as you like) is too weak for a spreadsheet, but the majority of the world's banks expect a 4 digit code to be enough to protect thousands of pounds in our bank accounts !

:smile:
 

garrilla

Senior Member
Location
Liverpool
The difference is that a card thief only gets 3 attempts at guessing the pin number from 9999 options before the account is locked. There's no such limit on spreadsheet that is protected but in the wrong hands as there is no limit to the attempts.

In theory (and in practice) a brute force attack, where every possible permutation is attempted, can be successful but increasing the strength of the password diminishes the likelihood . The definintion of strong in this context is the total number of permuations required to break the password.

So the average 7 bit password of lower case alphanumerics would take about half of 2 to the power of 32 attempts (4.25 billion). Making it case sensitive and adding a punctuation symbol makes it 2 to the power of 40 attempts (a thousand billion). So 'mypass1' is 256 times weaker than 'm&Pass1' - worth considering.
 

garrilla

Senior Member
Location
Liverpool
HJ said:
Well after a bit of scratching around I came up with this

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
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...

Change 'Sub' to 'Function' and add 'return p' before the 'End Function' - I've edited the code... (should work)
 
OP
HJ

HJ

Cycling in Scotland
Location
Auld Reekie
Thank garrilla I think that is what I was trying to achieve.

amnesia I am not actually trying to protect a spreadsheet, I am just generating them in a spreadsheet, to be used on web site user accounts, which might hang around for quite sometime before the user get around to loggin and changing the passwords, I don't want them left open to brute force attacks.
 
Top Bottom