Generate Random Passwords With Excel VBA

Objective

We want to create a VBA code that will generate random passwords with specific characteristics, such as length and the inclusion of uppercase letters, lowercase letters, numbers, and special characters.

Steps to Create the VBA Code for Generating Random Passwords

1. Open Excel: Start by opening your Excel workbook where you want to implement the password generation.
2. Access VBA Editor: Press Alt + F11 to open the VBA editor.
3. Insert a Module:
   • In the VBA editor, go to Insert > Module.
   • This will create a new module where we can write the code.
4. Write the Code: Here is a detailed VBA code that generates random passwords based on user-specified length and character types (uppercase, lowercase, digits, special characters).

VBA Code for Random Password Generator

Sub GenerateRandomPassword()
    ' Declare variables
    Dim passwordLength As Integer
    Dim password As String
    Dim charSet As String
    Dim i As Integer
    Dim randIndex As Integer  
    ' Prompt user for the length of the password
    passwordLength = InputBox("Enter the length of the password (between 8 and 20):", "Password Length")
    ' Validate the input
    If passwordLength < 8 Or passwordLength > 20 Then
        MsgBox "Password length must be between 8 and 20 characters."
        Exit Sub
    End If   
    ' Define the characters available for the password
    Dim lowerCase As String
    Dim upperCase As String
    Dim numbers As String
    Dim specialChars As String   
    lowerCase = "abcdefghijklmnopqrstuvwxyz"
    upperCase = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    numbers = "0123456789"
    specialChars = "!@#$%^&*()-_=+[]{}|;:,.<>?/~"
    ' Combine all characters into one string
    charSet = lowerCase & upperCase & numbers & specialChars 
    ' Initialize password as an empty string
    password = ""   
    ' Generate the password by randomly selecting characters from the character set
    For i = 1 To passwordLength
        randIndex = Int((Len(charSet) * Rnd) + 1)
        password = password & Mid(charSet, randIndex, 1)
    Next i  
    ' Display the generated password in a message box
    MsgBox "Your generated password is: " & password
End Sub

Explanation of the Code

Step-by-Step Breakdown:

1. Variable Declarations:
   • passwordLength: This variable holds the length of the password that the user inputs.
   • password: This string will hold the final generated password.
   • charSet: This string will contain all the characters (lowercase, uppercase, digits, and special characters) that can be used to form the password.
   • lowerCase, upperCase, numbers, specialChars: These strings define the individual character sets for lowercase letters, uppercase letters, digits, and special characters, respectively.
   • randIndex: This variable will hold the randomly generated index to select a character from the charSet.
2. User Input for Password Length:
   • The InputBox function prompts the user to enter the length of the password. The length must be between 8 and 20 characters.
   • If the length entered is outside the allowed range, a message box is displayed to notify the user, and the macro exits without generating a password.
3. Character Set Definition:
   • We define the character sets:
     • lowerCase: a string containing all lowercase letters.
     • upperCase: a string containing all uppercase letters.
     • numbers: a string containing digits 0 to 9.
     • specialChars: a string containing common special characters (you can modify this to include other characters if needed).
   • These character sets are then combined into a single string charSet which contains all the characters that can be used to generate the password.
4. Password Generation Loop:
   • The For loop runs for the number of times specified by the user (passwordLength).
   • Inside the loop, a random index (randIndex) is generated using the Rnd function, which produces a random number between 0 and 1. This value is multiplied by the length of charSet and rounded down to give a valid index for selecting a character.
   • The Mid function is used to extract a character from charSet at the randomly generated index. This character is appended to the password string.
5. Displaying the Password:
   • After the loop has completed, the password string contains the randomly generated password.
   • A message box (MsgBox) is used to display the generated password to the user.

How to Run the Code

1. After pasting the code into the module, you can run it by:
   • Pressing F5 while in the VBA editor, or
   • Going back to Excel, pressing Alt + F8, selecting GenerateRandomPassword, and clicking Run.
2. A dialog box will appear, prompting you to enter the length of the password (between 8 and 20 characters). After entering a valid length, the password will be generated and displayed in a message box.

Customization

• Password Complexity: You can modify the charSet string to include additional characters or exclude some if you want more control over the password complexity.
• Min/Max Length: You can change the password length constraints (in the If condition) to match your specific requirements.

Conclusion

This code is a basic random password generator, allowing users to generate passwords with a mix of letters, digits, and special characters. It provides a customizable approach, enabling the password length and complexity to be adjusted based on user requirements.