VBA Validate password string

Murray Brown
Murray Brown used Ask the Experts™
Hi
I have to write validation code for a password string
It must be at least 8 long , have at least one upper case and one lower case letter, at least one number and at least one symbol.
What VBA code would I use to do this?
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
There are different ways of doing this: looping through character by character or using Instr()...  Take a look at

https://access-programmers.co.uk/forums/showthread.php?t=283490
https://stackoverflow.com/questions/40133375/vba-validate-if-my-password-contais-1-number-1-ucase-and-1-lcase
each illustrate one of these approaches.
John TsioumprisSoftware & Systems Engineer
Here is an example for symbol checking
Public Function checkSymbol(InputString As String) As Boolean
Dim blnChecked As Boolean
blnChecked = False
For i = 1 To Len(InputString)
If Asc(Mid(InputString, i, 1)) >= 32 And Asc(Mid(InputString, i, 1)) <= 47 Then
blnChecked = True
Exit For
End If

Next
checkSymbol = blnChecked
End Function

Open in new window

ste5anSenior Developer
E.g. something like

Option Explicit

Function PasswordComplexityTestPassed(APasswordCandiate As String) As Boolean

  Const SYMBOLS As String = "!#-.,¢" ' Add your symbols.

  Dim CountLowerCase As Long
  Dim CountSymbols As Long
  Dim CountUpperCase As Long
  Dim CurrentChar As String
  Dim Index As Long
  Dim Length As Long
  
  PasswordComplexityTestPassed = False
  Length = Len(APasswordCandiate)
  If Length < 8 Then
    Exit Function
  End If

  For Index = 1 To Length
    CurrentChar = Mid(APasswordCandiate, Index, 1)
      If Asc(CurrentChar) >= 65 And Asc(CurrentChar) <= 90 Then
        CountUpperCase = CountUpperCase + 1
      ElseIf Asc(CurrentChar) >= 97 And Asc(CurrentChar) <= 122 Then
        CountLowerCase = CountLowerCase + 1
      ElseIf InStr(SYMBOLS, CurrentChar) > 0 Then
        CountSymbols = CountSymbols + 1
    End If
  Next Index

  PasswordComplexityTestPassed = (CountUpperCase > 0) And (CountLowerCase > 0) And (CountSymbols > 0)
  
End Function

Open in new window

using plain VBA.

But you may consider using RegEx.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

John TsioumprisSoftware & Systems Engineer
Revised my code because i skipped some symbols :)
Public Function checkSymbol(InputString As String) As Boolean
Dim i As Integer
Dim blnChecked As Boolean
blnChecked = False
For i = 1 To Len(InputString)
If (Asc(Mid(InputString, i, 1)) >= 32 And Asc(Mid(InputString, i, 1)) <= 47) _
Or (Asc(Mid(InputString, i, 1)) >= 58 And Asc(Mid(InputString, i, 1)) <= 64) _
Or (Asc(Mid(InputString, i, 1)) >= 91 And Asc(Mid(InputString, i, 1)) <= 96) _
Or (Asc(Mid(InputString, i, 1)) >= 123 And Asc(Mid(InputString, i, 1)) <= 126) _
Then
blnChecked = True
Exit For
End If

Open in new window


I used that for ASCII codes : https://theasciicode.com.ar/
Analyst Assistant
Murray

I think the regular expression in this function covers all the requirements.
Function PassWordChecker(strPassword As String) As Boolean
Dim RegExp As Object
Dim Match As Object
Dim strPattern As String

    Set RegExp = CreateObject("VBScript.RegExp")

    strPattern = "^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])[A-Za-z\d@$!%*?&]{8,}$"
    
    RegExp.Pattern = strPattern
    
    Set Match = RegExp.Execute(strPassword)
        
    PassWordChecker = Match.Count <> 0
        
End Function

Open in new window


Here some example passwords being checked.
Sub TestPassword()
Dim MyPW As String

    MyPW = "Aa8*DEFGHIJ"
    
    If PassWordChecker(MyPW) Then
        MsgBox "Password OK!"
    Else
        MsgBox "Password must be at least 8 characters long , have at least one upper case and one lower case letter, at least one number and at least one symbol (@$!%*?&). "
    End If
        
    MyPW = "ABCDEFGH"
    
    If PassWordChecker(MyPW) Then
        MsgBox "Password OK!"
    Else
        MsgBox "Password must be at least 8 characters long , have at least one upper case and one lower case letter, at least one number and at least one symbol (@$!%*?&). "
    End If
    
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
This Function will validate a password with at least...
One upper case letter
One lower case letter
One digit
One symbol (any)
And if the Password is at least 8 characters long and have max 15 characters.

Function ValidPassword(ByVal str As String) As Boolean

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*\W)[\w\d\W]{8,15}$"
    ValidPassword = .Test(str)
End With

End Function

Open in new window


The following code validates different passwords as below...

Sub TestPasswords()
Dim pwArr As Variant
Dim pw As Variant

pwArr = Array("Aa8*DEFGHIJ", "murray_ee_2019", "Murray_EE*2019", "HELLO123_*", "hello world!", "-*/+Aa123  ", "aA123_#+", "~@#%^&()[]Aa1")

For Each pw In pwArr
    If ValidPassword(pw) Then
        MsgBox "'" & pw & "' is good Password."
    Else
        MsgBox "'" & pw & "' is bad password."
    End If
Next pw
End Sub

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial