I don't know this is a lot shorter than you were hoping for, but given you wanted complete error reporting, looping does not really help that much. I noticed you only tested the 4th character to be the letter "P" in your code and, since you said your code works fine, I restricted the check of the 4th character to only that letter. Note that I also added a check to see if the argument is too short or too long to be a proper entry. Here is the non-looping code I came up with...
Code:
Function IsPAN(S As String) As String
If Len(S) < 10 Then
IsPAN = "Too short!"
ElseIf Len(S) > 10 Then
IsPAN = "Too long!"
Else
If Mid(S, 1, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 1 is not text."
If Mid(S, 2, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 2 is not text."
If Mid(S, 3, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 3 is not text."
If Mid(S, 4, 1) Like "[!Pp]" Then IsPAN = IsPAN & vbLf & "Character 4 is not the letter 'P'."
If Mid(S, 5, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 5 is not text."
If Mid(S, 6, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 6 is not a number."
If Mid(S, 7, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 7 is not a number."
If Mid(S, 8, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 8 is not a number."
If Mid(S, 9, 1) Like "[!0-9]" Then IsPAN = IsPAN & vbLf & "Character 9 is not a number."
If Mid(S, 10, 1) Like "[!A-Za-z]" Then IsPAN = IsPAN & vbLf & "Character 10 is not text."
If Len(IsPAN) Then
IsPAN = Mid(IsPAN, 2)
Else
IsPAN = "OK"
End If
End If
End Function
In case you are interested, here is the same function using loops (among other things)...
Code:
Function IsPAN(S As String) As String
Dim X As Long, PatternChar As String, Test As New Collection
Const Pattern As String = "AAAPANNNNA"
If Len(S) < 10 Then
IsPAN = "Too short!"
ElseIf Len(S) > 10 Then
IsPAN = "Too long!"
ElseIf Len(IsPAN) = 0 Then
Test.Add "[A-Za-z]", "A"
Test.Add "[Pp]", "P"
Test.Add "#", "N"
For X = 1 To Len(S)
PatternChar = Test(Mid(Pattern, X, 1))
If Not Mid(S, X, 1) Like PatternChar Then
IsPAN = IsPAN & vbLf & "Character " & X & " is not " & Choose(InStr("APN", _
Mid(Pattern, X, 1)), "text.", "the letter ""P"".", "a number.")
End If
Next
If Len(IsPAN) Then
IsPAN = Mid(IsPAN, 2)
Else
IsPAN = "OK"
End If
End If
End Function
Reply With Quote
Originally Posted by Rick Rothstein
Bookmarks