Access Excel VBA generate random password or random characters

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

Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

Your email address will not be published. Required fields are marked *