Vba code to check if a string contains special symbols

[kelly mort]

Hi,

I have been searching for the solution to this for a while now but I can't seem to get the right solution.

How do I check for the availability of symbols such as, @,!,@, and so on?

That's any character that's not a number or letter.

I found this on the Web.

Sub Password()

Dim b As Integer
Dim i As Integer, j As Integer, k As Integer
Dim psw As String

Dim hasNum As Boolean, hasUpper As Boolean, hasLower As Boolean

Dim LengthOFPasswordsList As Long

LengthOFPasswordsList = Range("D" & Rows.Count).End(xlUp).Row

For b = 3 To LengthOFPasswordsList
    'assume the password is no good.
    hasNum = False
    hasUpper = False
    hasLower = False

    'capture the psw in question
    psw = Range("D" & b)

    'see if there is a number in the password
    'NOTE: the following For loops uses the ASCII values for numbers and letters.
    For k = 48 To 57
        If (InStr(1, psw, Chr(k))) Then
            hasNum = True
            Exit For
        End If
    Next k

    'See if there is an upper case
    For i = 65 To 90
        If (InStr(1, psw, Chr(i))) Then
            hasUpper = True
            Exit For
        End If
    Next i

    'See if there is a lower case
    For j = 97 To 122
        If (InStr(1, psw, Chr(j))) Then
            hasLower = True
            Exit For
        End If
    Next j

    'See if all criteria was met
    If Not hasLower Or Not hasUpper Or Not hasNum Or (Len(psw) <> 8) Then
        Range("F" & b) = "Password Inválida"
    End If
Next b

End Sub

As the above code is check for caps, lower case and numbers, I need another criteria to check for special characters or symbols.
Thanks in advance
Kelly Mort.

 

[Rick Rothstein]

The following If-Then block of code is all you need to check for if the variable Txt (substitute your own variable as needed) contains only letters (upper or lower case) and/or digits or not...

If Txt Like "*[!A-Za-z0-9]*" Then
  MsgBox "There is a non-alphanumeric character in the text"
Else
  MsgBox "The text contains only letters and/or digits
End If

You are going to have to describe in more detail what you consider to be a "special character" (what you consider them to be may differ from what we here consider them to be).

 

[johnnyL]

Check a range (G column in this example) and highlight cell when special character is found:

Sub CheckRangeForSpecialCharactersWithOutRegEx()
'
    Dim r As Range, rng As Range, s As String
    Dim i As Long, L As Long
'
    Set rng = Intersect(Range("G:G"), ActiveSheet.UsedRange)
'
    For Each r In rng
        If r.Value <> "" Then
            s = Replace(r.Text, "-", "")
            L = Len(s)
            For i = 1 To L
                If Not Mid(s, i, 1) Like "[0-9a-zA-Z]" Then
                    r.Interior.Color = vbYellow
                End If
            Next i
        End If
    Next r
End Sub

 

[Rick Rothstein]

@johnnyL,

I am not completely sure that non-letters, non-digits are all supposed to be counted as "special characters". With that said, you may want to look at what I posted in Message #2 to see how to do the test you did without using a loop.

 

[Phuoc]

Another way:

Public Function CheckPassword(txt As String) As Boolean
CheckPassword = txt Like "*[A-Z]*" And txt Like "*[a-z]*" And txt Like "*[0-9]*" _
    And txt Like "*[!A-Za-z0-9]*" And Len(txt) = 8
End Function

 

[johnnyL]

@johnnyL,

I am not completely sure that non-letters, non-digits are all supposed to be counted as "special characters".

Hey @Rick Rothstein I was just going by the OP.

How do I check for the availability of symbols such as, @,!,@, and so on?

That's any character that's not a number or letter.

Wasn't my definition, just trying to accomodate.

 

[johnnyL]

@johnnyL,

I am not completely sure that non-letters, non-digits are all supposed to be counted as "special characters". With that said, you may want to look at what I posted in Message #2 to see how to do the test you did without using a loop.

The following If-Then block of code is all you need to check for if the variable Txt (substitute your own variable as needed) contains only letters (upper or lower case) and/or digits or not...

Upon further review, doesn't your last statement there say that you did the same check for upper/lower case and or digits?

 

[johnnyL]

@kelly mort are you just checking one cell and want a true/false, are you checking an entire range? Let us know. I know your OP referenced a password, but is that what you are really checking?

 

[Rick Rothstein]

Upon further review, doesn't your last statement there say that you did the same check for upper/lower case and or digits?

Yes, but I am not sure any non-letter/non-digit is to be considered a "special" character. This is the part of Kelly's description that confused me. In her OP, she posted some code and then said "As the above code is check for caps, lower case and numbers, I need another criteria to check for special characters or symbols." Since she already had (much longer) code to check for letters/digits, it is her "I need another criteria to check for special characters or symbols" that leads me to think she wants to check for some kind of specific "special" characters/symbols as opposed to any character that is not a letter or digit.

 

[kelly mort]

Yes, but I am not sure any non-letter/non-digit is to be considered a "special" character. This is the part of Kelly's description that confused me. In her OP, she posted some code and then said "As the above code is check for caps, lower case and numbers, I need another criteria to check for special characters or symbols." Since she already had (much longer) code to check for letters/digits, it is her "I need another criteria to check for special characters or symbols" that leads me to think she wants to check for some kind of specific "special" characters/symbols as opposed to any character that is not a letter or digit.

Non-letters/non-digits are considered as "special characters".