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