Validate PAN in excel through VBA macro

Validating PAN in excel through VBA macro..
Note : PAN CARD PATTERN -
1. START WITH ALPHABET (A-Z)
2. ALPHABET SHOULD BE 5 CHARACTER IN LENGH
3. THEN 4 DIGITS (0-9)
4. LAST CHARACTER SHOULD BE ALPHABET(A-Z)
5. All characters must be in Uppercase


Function ValidatePAN(panentry As String) As Boolean
' arun
ValidatePAN = True
'pan = Range("PAN").Value
If Len(panentry) > 0 Then
If Not IsNumeric(Mid(panentry, 6, 4)) Then
ValidatePAN = False
Exit Function
End If
If Not CheckAtoZ(Mid(panentry, 1, 1)) Then
ValidatePAN = False
Exit Function
End If
If Not CheckAtoZ(Mid(panentry, 2, 1)) Then
ValidatePAN = False
Exit Function
End If
If Not CheckAtoZ(Mid(panentry, 3, 1)) Then
ValidatePAN = False
Exit Function
End If
If Not CheckAtoZ(Mid(panentry, 4, 1)) Then
ValidatePAN = False
Exit Function
End If
If Not CheckAtoZ(Mid(panentry, 5, 1)) Then
ValidatePAN = False
Exit Function
End If
If Not CheckAtoZ(Mid(panentry, 10, 1)) Then
ValidatePAN = False
Exit Function
End If
End If

End Function


To check the letters in PAN falls in A-Z

Function CheckAtoZ(chr1) As Boolean
CheckAtoZ = True
If ((Asc(chr1) < 65) Or (Asc(chr1) > 90)) Then
CheckAtoZ = False
End If
End Function


Comments

Author: Mrs. Meetu Choudhary Nanda25 Apr 2009 Member Level: Gold   Points : 1

Thanks Deepika, I appriciate you for changing your resource to your own creation...

++
Thanks and Regards
Meetu Choudhary
Editor

Author: Praveen13 May 2009 Member Level: Gold   Points : 1

hi,

i think we can write Regular Expression for that

"^[A-Z]{5}[0-9]{4}[A-Z]{1}$"


if you write the above regular expression it will also validate na

Author: Deepika Haridas13 May 2009 Member Level: Gold   Points : 1

Hi Praveen,

There are different ways for validating..

Both ways of doing are correct.





  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: