#### HJ

##### Cycling in Scotland
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.

#### Carwash

##### Señor Member
Use pwgen and pipe the output to a csv file, then open in Excel?

#### HJ

##### Cycling in Scotland
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)

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.

#### HLaB

##### Marie Attoinette Fan
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.

#### HJ

##### Cycling in Scotland
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 ! #### garrilla

##### Senior Member
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
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)

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)

#### HJ

##### Cycling in Scotland
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.