There are a few of way I like to create random passwords usually from a Filemaker server based database, for more security, but here is an Excel visual basic equivalent of what I use:
Random Password Generator
This code below generates passwords completely randomly.
Public Function RndPass(Length As Integer, Optional Lower As Boolean) As String
Dim Max As Integer
Dim Min As IntegerDim RndPassLoop As String
Max = 126
Min = 48Randomize Timer
If Length < 8 Then
Length = 8
End IfFor i = 1 To Length
RndPassLoop = RndPassLoop & Chr(Int((Max – Min + 1) * Rnd + Min))
Next iIf Lower = False Then
RndPass = RndPassLoop
Else
RndPass = StrConv(RndPassLoop, vbLowerCase)
End IfEnd Function
‘add this code to a vb module
‘syntax is RndPass(Length of password, True (lowercase) / Empty or False (both upper and lower)
‘RndPass(20,1) = 20 characters lower case
Random Letter Generator
This code below will generator code for a random letter or number based string.
Public Function RndPassP(Phrase As String) As String
Dim Max As Integer
Dim Min As IntegerDim RndPassLoop As String
If Len(Phrase) < 12 Then
RndPassP = “Phrase too short – please choose something longer”
Exit Function
End IfIf subStringCount(Phrase, “a”) + _
subStringCount(Phrase, “c”) + _
subStringCount(Phrase, “e”) + _
subStringCount(Phrase, “i”) + _
subStringCount(Phrase, “o”) + _
subStringCount(Phrase, “s”) + _
subStringCount(Phrase, “u”) + _
subStringCount(Phrase, “r”) < 4 ThenRndPassP = “Phrase does not include enough key letters – please choose another”
Exit Function
End IfPhrase = StrConv(Phrase, vbLowerCase)
Randomize Timer
RndPassLoop = Replace(Phrase, “a”, “@”)
Phrase = Replace(RndPassLoop, “b”, “8”)
RndPassLoop = Replace(Phrase, “e”, “3”)
Phrase = Replace(RndPassLoop, “i”, “!”)
RndPassLoop = Replace(Phrase, “o”, “0”)
Phrase = Replace(RndPassLoop, “s”, “$”)
RndPassLoop = Replace(Phrase, ” “, “”)
Phrase = Replace(RndPassLoop, “u”, ” * “)
RndPassLoop = Replace(Phrase, “r”, “£”)
Phrase = Replace(RndPassLoop, “M”, “M”)
RndPassLoop = Replace(Phrase, “N”, “N”)
Phrase = Replace(RndPassLoop, “T”, “T”)
RndPassLoop = Replace(Phrase, “X”, “X”)
Phrase = Replace(RndPassLoop, “G”, “G”)Phrase = RndPassLoop & “:)”””
RndPassP = Phrase
End Function
Function subStringCount(longString As String, subString As String) As Double
subStringCount = Len(longString) _
– Len(Application.Substitute(longString, subString, vbNullChar))
End Function‘add this code to a vb module
‘syntax is ‘RndPassP(string):
‘RndPassP (“I work in Property Finance”)
Let me know what you think about these and what sort of password system you use?
I wrote a fairly simple macro that uses upper & lower case letters, numerals, and a few special characters (forcing one of each type). Below is VBA for a 10-character password
Sub PasswordGenerate()
'Creates a password 10 characters long
'with at least 1 upper & lower case letters, 1 number, and 1 special character
'
'Recorded by David Z, April 3, 2009
'
'
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, randomizing 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
Thanks David for that contribution!
Unfortunately the randbetween function does not work for me (I have excel 2007)…
I tried to modify the function to get what I wanted but something went wrong, here is the code which i tried to use:
Function pass(Length As Integer)
Dim p As String
Dim c As Integer
p = “”
For i = 1 To Length
x = Int(Rnd * 2 + 1)
If x = 1 Then c = Chr(Int(48 + Rnd * (57 – 48)))
If x = 2 Then c = Chr(Int(65 + Rnd * (90 – 65)))
If x = 3 Then c = Chr(Int(97 + Rnd * (122 – 97)))
p = p & c
Next i
pass = p
End Function
Take it you mean you only have Excel 2003 as Randbetween works fine in Excel 2007. The equivalent of this is:
INT(RAND()*(b-a)+a)
So looks like you are almost there, here it is with corrections:
Function pass(Length As Integer)
Dim p As String
Dim c As String
p = “”
For i = 1 To Length
x = Int(Rnd() * 4)
If x = 1 Then c = Chr(Int(Rnd() * (57 – 48) + 48))
If x = 2 Then c = Chr(Int(Rnd() * (90 – 65) + 65))
If x = 3 Then c = Chr(Int(Rnd() * (120 – 97) + 97))
p = p & c
Next i
pass = p
End Function
Thank you for your answer, now it works well. I have Excel 2007, not 2003, the randbetween function does work in Excel main window but it does not in VB Editor, I don’t know why, probably something is wrong with installation. Fortunately the equivalent function does the same thing.
Now as I see I should have used Rnd() instead of just Rnd and another one is that I defined c as Integer (don’t know why :D ) not as string.
So finally, to sum up:
We got the simple and smart function, with the following syntax: pass(Length); it chooses randomly symbols from the following ranges: A-Z, a-z, 0-9; and creates the password of the defined length :)
Just trying to workout if David’s code could be run as a function, so that I could auto fill down a column, is this possible?
hi,
I’m knew on vb programming,
I would like to generate all possible WEP and WPA combinations in a worksheet column, or directly in a txt file.
could someone help me please?
It’ possible to use excel function in VBA code but You have to do it like this:
Application.WorksheetFunction.RandBetween