This Access Excel VBA tutorial explains how to generate random password or random characters with random number, random letters and random symbols.
You may also want to read:
Excel VBA generate non-duplicated random number
Excel VBA Rnd Function to generate random number
Access Excel VBA generate random password or random characters
If you are a system administrator, very likely you need to genrate login password for users. Some people who are not good at Excel generate password combination that has a pattern which is predictable by other users. In order to generate a truly random password that cannot be guessed by anyone, it is easier to write a VBA custom Function.
In my previous post, I have explained how to generate random characters, so in this post I will focus on generate random password.
Gather requirement of random password
Many companies have password policy, the most common one is password must have 8 characeters with at least 1 upper & lower alphabets + number + symbol.
To generate such combination, the easiest way is to define which character is what character type. For example, the combination can be like this:
| 1st char | 2nd char | 3rd char | 4th char | 5th char | 6th char | 7th char | 8th char |
| any | a-z | A-Z | 0-9 | symbol | any | any | any |
Now the question is how we generate each character type.
Access Excel VBA Custom Function – generate random password
The custom function below allows you to select which random character type you want to generate.
Public Function wRandomPassword(Optional rndType = 1) As String
Randomize
Select Case rndType
Case 1 'generate any characters
wRandomPassword= Chr(Int((126 - 33 + 1) * Rnd + 33))
Case 2 'generate a-z or A-Z
randVariable = Int((122 - 65 + 1) * Rnd + 65)
Do While randVariable > 90 And randVariable < 97
randVariable = Int((122 - 65 + 1) * Rnd + 65)
Loop
wRandomPassword= Chr(randVariable)
Case 3 'generate a-z
wRandomPassword= Chr(Int((122 - 97 + 1) * Rnd + 97))
Case 4 'generate A-Z
wRandomPassword= Chr(Int((90 - 65 + 1) * Rnd + 65))
Case 5 'generate 0-9
wRandomPassword= Chr(Int((57 - 48 + 1) * Rnd + 48))
Case 6 'generate symbols
wRandomPassword= Chr(Int((47 - 33 + 1) * Rnd + 33))
End Select
End Function
Syntax of wRandomPassword – generate random password
wRandomPassword(Optional rndType = 1)
| RndType | Explanation |
| 1 | generate any characters |
| 2 | generate a-z or A-Z |
| 3 | generate a-z |
| 4 | generate A-Z |
| 5 | generate 0-9 |
| 6 | generate symbols |
Example of wRandomPassword – generate random password
Back to the previous password requirement below
| 1st char | 2nd char | 3rd char | 4th char | 5th char | 6th char | 7th char | 8th char |
| any | a-z | A-Z | 0-9 | symbol | any | any | any |
In Excel worksheet, we can type the below formula
=wRandomPassword()&wRandomPassword(3)&wRandomPassword(4)&wRandomPassword(5)&wRandomPassword(6)&wRandomPassword()&wRandomPassword()&wRandomPassword()
Result
Generate Password without password policy requirement
If you don’t have any requirement on the password, then we can
Public Function wRandomPassword() As String Randomize For i = 1 To 8 tempStr = tempStr & Chr(Int((126 - 33 + 1) * Rnd + 33)) Next i wRandomPassword = tempStr End Function
Result
Outbound References
https://support.office.com/en-us/article/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a
