Full proof UK postcode validation

  • Thread starter Thread starter Ed Peters
  • Start date Start date
E

Ed Peters

Hi all,

I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
 
Sub ValidatePostCode(ByVal PostCode As String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-Z]"
Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]")
Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like "[0-9]")
Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Ed Peters said:
Hi all,

I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
 
I am pretty sure this function will work...

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
Parts = Split(PostCode)
ValidatePostCode = (Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]"))
End Function

It returns True or False (rather than posting a MessageBox) which you can
use in your own code to decide on how to proceed.

Rick


Ed Peters said:
Hi all,

I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
 
Ed,

You could look into using a regular expression, although it seems to be one
that is hard to pin down:

http://regexlib.com/REDetails.aspx?regexp_id=260

hth,

Doug

Ed Peters said:
Hi all,

I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
 
Hi Bob,

Not sure about the following

these London codes return false -ve
W1A 1HP
EC1A 1BB

this returns false +ve
A1A1 1AA

UK postcode rules -
http://www.mailsorttechnical.com/frequentlyaskedquestions.cfm
"
Format-Example Postcode
"A" indicates an alphabetic character and "N" indicates a numeric character.
AN NAA M1 1AA
ANN NAA M60 1NW
AAN NAA CR2 6XH
AANN NAA DN55 1PT
ANA NAA W1A 1HP
AANA NAA EC1A 1BB

The postcode GIR 0AA was issued historically and does not confirm to current
rules on valid Postcode formats - it is however still in use for Alliance &
Leicester Girobank Plc in Bootle.

These conventions are liable to change in the future if operationally
required.
"

I tried to fault Rick's but couldn't <g>, except the anomaly GIR 0AA.

Regards,
Peter T

Bob Phillips said:
Sub ValidatePostCode(ByVal PostCode As String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-Z]"
Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]")
Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like "[0-9]")
Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Ed Peters said:
Hi all,

I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
Click to expand...
 
I am not sure about the whole co Peter, I just made the identified problem
work :-).

I recall an earlier post that Jamie Collins was in that tried to produce a
catch-all solution.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Peter T said:
Hi Bob,

Not sure about the following

these London codes return false -ve
W1A 1HP
EC1A 1BB

this returns false +ve
A1A1 1AA

UK postcode rules -
http://www.mailsorttechnical.com/frequentlyaskedquestions.cfm
"
Format-Example Postcode
"A" indicates an alphabetic character and "N" indicates a numeric
character.
AN NAA M1 1AA
ANN NAA M60 1NW
AAN NAA CR2 6XH
AANN NAA DN55 1PT
ANA NAA W1A 1HP
AANA NAA EC1A 1BB

The postcode GIR 0AA was issued historically and does not confirm to
current
rules on valid Postcode formats - it is however still in use for Alliance
&
Leicester Girobank Plc in Bootle.

These conventions are liable to change in the future if operationally
required.
"

I tried to fault Rick's but couldn't <g>, except the anomaly GIR 0AA.

Regards,
Peter T

Bob Phillips said:
Sub ValidatePostCode(ByVal PostCode As String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-Z]"
Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]")
Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like "[0-9]")
_
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like "[0-9]")
Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Ed Peters said:
Hi all,

I trying to do UK postcode validation.

From a previous post by 'Toppers' I've got the code below. It looks
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
Click to expand...
Click to expand...
 
Well, I am not completely sure of my code anymore. I think it proofs the
vast majority of them, but I also think there are some exceptions to the
rule I used to create my code. I'll try looking into this in more detail
later on today.

Rick
I am pretty sure this function will work...

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
Parts = Split(PostCode)
ValidatePostCode = (Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]"))
End Function

It returns True or False (rather than posting a MessageBox) which you can
use in your own code to decide on how to proceed.
I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
Click to expand...
 
If this is entirely the wrong approach it's because regular expressions are
very new ground to me but this hasn't failed yet. I don't know wheteher the
OP was just testing but WX2 1BA from the original post is invalid according
to this site

http://www.royalmail.com/portal/rm/...tId=400145&pageId=pcaf_a_search&gear=postcode

Still testing though.

Option Explicit
Sub UK_Postcodes()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set Myrange = ActiveCell
Outstring = ""
Set Collection = RegExp.Execute(ActiveCell.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next

If ActiveCell.Value <> "" And ActiveCell.Value = Outstring Then
MsgBox "Valid UK Postcode"
Else
MsgBox "Invalid UK Postcode"
End If
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub

Mike


Doug Glancy said:
Ed,

You could look into using a regular expression, although it seems to be one
that is hard to pin down:

http://regexlib.com/REDetails.aspx?regexp_id=260

hth,

Doug

Ed Peters said:
Hi all,

I trying to do UK postcode validation.
From a previous post by 'Toppers' I've got the code below. It looks
Click to expand...
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
Click to expand...
 
Okay, my original function appears to be correct except for GIR 0AA which
is some kind of historically special Post Code. Here is my function,
modified to accept that special Post Code...

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
Parts = Split(PostCode)
ValidatePostCode = PostCode = "GIR 0AA" Or _
((Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")))
End Function

Now, to add one clarification.... my function only checks to make sure the
"shape" of the Post Code is correct, not that the Post Code being checked is
actually in use. From this standpoint, the function should continue to work,
given what it does, for any new Post Codes that may be added in the future.

Rick

Well, I am not completely sure of my code anymore. I think it proofs the
vast majority of them, but I also think there are some exceptions to the
rule I used to create my code. I'll try looking into this in more detail
later on today.

Rick
I am pretty sure this function will work...

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
Parts = Split(PostCode)
ValidatePostCode = (Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]"))
End Function

It returns True or False (rather than posting a MessageBox) which you can
use in your own code to decide on how to proceed.
I trying to do UK postcode validation.

From a previous post by 'Toppers' I've got the code below. It looks
good however it fails if a postcode of, for example, xxxx 4at.

Does anyone have a better solution?

Thanks,

Ed


Sub MyTest()
ValidatePostCode "WX2 1BA"
End Sub


Sub ValidatePostCode(Byval PostCode as String)
'
' Validate UK Post Code
'
Dim v As Variant
Dim Outer As String, Inner As String
Dim invalid As Boolean


v = Split(PostCode, " ")
If UBound(v) = 0 Then
MsgBox "Post code " & PostCode & " does not contain a blank"
Exit Sub
End If


Outer = UCase(Trim(v(0)))
Inner = UCase(Trim(v(1)))
'
' Validate inner code .....
'
invalid = False
If Len(Inner) <> 3 Then
invalid = True
Else
If Left(Inner, 1) Like "[0-9]" Then
If Mid(Inner, 2, 1) Like "[A-Z]" And Mid(Inner, 3, 1) Like "[A-
Z]" Then
Else
invalid = True
End If
Else
invalid = True
End If
End If
If invalid Then
MsgBox "Inner code " & Inner & " is invalid"
Exit Sub
End If
'
' Validate Outer code .....
'
invalid = False
Select Case Len(Outer)


Case Is = 2
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]"
Then
Else
invalid = True
End If
Case Is = 3
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[0-9]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Is = 4
If Left(Outer, 1) Like "[A-Z]" And Right(Outer, 1) Like "[A-Z]" _
And (Mid(Outer, 2, 1) Like "[A-Z]" Or Mid(Outer, 2, 1) Like
"[0-9]") _
And (Mid(Outer, 3, 1) Like "[A-Z]" Or Mid(Outer, 3, 1) Like
"[0-9]") Then
Else
invalid = True
End If
Case Else
invalid = True
End Select


If invalid Then
MsgBox "Outer code " & Outer & " is invalid"
Exit Sub
End If


MsgBox "Post Code " & PostCode & " is valid"
Click to expand...
Click to expand...
Click to expand...
 
That looks very good indeed. Do you or anyone know how or where to verify
the authenticity of that pattern, ideally from Royal Mail, in order to use
it with confidence.

Regards,
Peter T

Mike H said:
If this is entirely the wrong approach it's because regular expressions are
very new ground to me but this hasn't failed yet. I don't know wheteher the
OP was just testing but WX2 1BA from the original post is invalid according
to this site

http://www.royalmail.com/portal/rm/addressfinder;jsessionid=C2XA1VZTG1KROFB2
IGVUNZQUHRA0UQ2K?catId=400145&pageId=pcaf_a_search&gear=postcode

Still testing though.

Option Explicit
Sub UK_Postcodes()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set Myrange = ActiveCell
Outstring = ""
Set Collection = RegExp.Execute(ActiveCell.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next

If ActiveCell.Value <> "" And ActiveCell.Value = Outstring Then
MsgBox "Valid UK Postcode"
Else
MsgBox "Invalid UK Postcode"
End If
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub

Mike
Click to expand...
<snip>
 
That looks very good indeed. Do you or anyone know how or where to verify
the authenticity of that pattern, ideally from Royal Mail, in order to use
it with confidence.

Regards,
Peter T


If this is entirely the wrong approach it's because regular expressions
are
very new ground to me but this hasn't failed yet. I don't know wheteher the
OP was just testing but WX2 1BA from the original post is invalid according
to this site
http://www.royalmail.com/portal/rm/addressfinder;jsessionid=C2XA1VZTG...
IGVUNZQUHRA0UQ2K?catId=400145&pageId=pcaf_a_search&gear=postcode





Still testing though.
Click to expand...
Option Explicit
Sub UK_Postcodes()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set Myrange = ActiveCell
Outstring = ""
Set Collection = RegExp.Execute(ActiveCell.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
Click to expand...
If ActiveCell.Value <> "" And ActiveCell.Value = Outstring Then
MsgBox "Valid UKPostcode"
Else
MsgBox "Invalid UKPostcode"
End If
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub
Click to expand...
Mike
Click to expand...

<snip>- Hide quoted text -

- Show quoted text -
Click to expand...

Wow what a response!

Great coding!

How would I take the True of False value and insert some text from the
code below.

I've tried, which does not work.

If ValidatePostCode = False Then
ValidatePostCode = "Invalid"
Else
ValidatePostCode = "Valid"
End If


Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
Parts = Split(PostCode)
ValidatePostCode = PostCode = "GIR 0AA" Or _
((Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")))
End Function


Thanks,

ED
 
Function ValidatePostCode(ByVal PostCode As String) As Boolean
How would I take the True of False value and insert some text from the
code below.

I've tried, which does not work.

If ValidatePostCode = False Then
ValidatePostCode = "Invalid"
Else
ValidatePostCode = "Valid"
End If
Click to expand...

There are a couple of things wrong with the way you structured your code (as
shown above)...

The first thing wrong with your code is that you need to pass the PostCode
into the function (otherwise the function doesn't know what to test). I'm
not sure where you are getting your PostCode from within your program. If it
were from a TextBox (named, say, TextBox1 for this example, then your
If-Then statement should look like this...

If ValidatePostCode(TextBox1.Text) Then

Notice I did not set it equal to True or False. Why? Because the function
already returns either True or False, so asking if True = True or False =
False is redundant. That means, then, that the code directly under the
If-Then statement should be for the "True condition" (the "Valid" for you
example code) and the code after the Else statement should be for the "False
condition" (the "Invalid" in your example code).

The second thing wrong with your code is that you are trying to assign
String values to the function... you can't do that. In you have to use a
variable with a name different from the function, say,
ResultFromValidatePostCodeFunction. This would mean your example code above
should look like this instead...

If ValidatePostCode(TextBox1.Text) Then
ResultFromValidatePostCodeFunction = "Valid"
Else
ResultFromValidatePostCodeFunction = "Invalid"
End If

Of course, a variable named ResultFromValidatePostCodeFunction is kind of
extreme, but I used that to give you the general idea of how to approach
using the function.

Okay, now that that is straightened out, I have modified my ValidatePostCode
function to validate the PostCode Area as well as the "shape" of the Post
Code itself (which is all the original function did). Of course, this makes
the function less general than my original function meaning if any PostCode
Areas are added or removed UK Mail System in the future, the function will
need to be changed to account for addition and/or removal. Anyway, you can
stay with my original function or use the modified function below, your
choice.

Rick

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
End Function
 
How would I take the True of False value and insert some text from the
code below.
Click to expand...
I've tried, which does not work.
Click to expand...
If ValidatePostCode = False Then
ValidatePostCode = "Invalid"
Else
ValidatePostCode = "Valid"
End If
Click to expand...

There are a couple of things wrong with the way you structured your code (as
shown above)...

The first thing wrong with your code is that you need to pass thePostCode
into the function (otherwise the function doesn't know what to test). I'm
not sure where you are getting yourPostCodefrom within your program. If it
were from a TextBox (named, say, TextBox1 for this example, then your
If-Then statement should look like this...

If ValidatePostCode(TextBox1.Text) Then

Notice I did not set it equal to True or False. Why? Because the function
already returns either True or False, so asking if True = True or False =
False is redundant. That means, then, that the code directly under the
If-Then statement should be for the "True condition" (the "Valid" for you
example code) and the code after the Else statement should be for the "False
condition" (the "Invalid" in your example code).

The second thing wrong with your code is that you are trying to assign
String values to the function... you can't do that. In you have to use a
variable with a name different from the function, say,
ResultFromValidatePostCodeFunction. This would mean your example code above
should look like this instead...

If ValidatePostCode(TextBox1.Text) Then
ResultFromValidatePostCodeFunction = "Valid"
Else
ResultFromValidatePostCodeFunction = "Invalid"
End If

Of course, a variable named ResultFromValidatePostCodeFunction is kind of
extreme, but I used that to give you the general idea of how to approach
using the function.

Okay, now that that is straightened out, I have modified my ValidatePostCode
function to validate thePostCodeArea as well as the "shape" of the Post
Code itself (which is all the original function did). Of course, this makes
the function less general than my original function meaning if anyPostCode
Areas are added or removed UK Mail System in the future, the function will
need to be changed to account for addition and/or removal. Anyway, you can
stay with my original function or use the modified function below, your
choice.

Rick

Function ValidatePostCode(ByValPostCodeAs String) As Boolean
Dim Parts() As String
PostCode= UCase$(PostCode)
Parts = Split(PostCode)
IfPostCode= "GIR 0AA" OrPostCode= "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
End Function
Click to expand...

Thanks Rick for Shape coding. Looks good.

Thanks also for your detailed explaination of my coding. However I'm
still trying to work out how to pass the Postcode into the function.
I'm using cells to get postcode, eg, in cell B1 I'm putting
=validatepostcode(a1) which then returns True / False and this is
where I need the text string "valid" or "Invalid".

Cheers

Ed
 
There are a couple of things wrong with the way you structured your code (as
shown above)...
Click to expand...
The first thing wrong with your code is that you need to pass thePostCode
into the function (otherwise the function doesn't know what to test). I'm
not sure where you are getting yourPostCodefrom within your program. If it
were from a TextBox (named, say, TextBox1 for this example, then your
If-Then statement should look like this...
Click to expand...
If ValidatePostCode(TextBox1.Text) Then
Click to expand...
Notice I did not set it equal to True or False. Why? Because the function
already returns either True or False, so asking if True = True or False =
False is redundant. That means, then, that the code directly under the
If-Then statement should be for the "True condition" (the "Valid" for you
example code) and the code after the Else statement should be for the "False
condition" (the "Invalid" in your example code).
Click to expand...
The second thing wrong with your code is that you are trying to assign
String values to the function... you can't do that. In you have to use a
variable with a name different from the function, say,
ResultFromValidatePostCodeFunction. This would mean your example code above
should look like this instead...
Click to expand...
If ValidatePostCode(TextBox1.Text) Then
ResultFromValidatePostCodeFunction = "Valid"
Else
ResultFromValidatePostCodeFunction = "Invalid"
End If
Click to expand...
Of course, a variable named ResultFromValidatePostCodeFunction is kind of
extreme, but I used that to give you the general idea of how to approach
using the function.
Click to expand...
Okay, now that that is straightened out, I have modified my ValidatePostCode
function tovalidatethePostCodeArea as well as the "shape" of the Post
Code itself (which is all the original function did). Of course, this makes
the function less general than my original function meaning if anyPostCode
Areas are added or removed UK Mail System in the future, the function will
need to be changed to account for addition and/or removal. Anyway, you can
stay with my original function or use the modified function below, your
choice.
Rick
Click to expand...

Function ValidatePostCode(ByValPostCodeAs String) As Boolean
Dim Parts() As String
PostCode= UCase$(PostCode)
Parts = Split(PostCode)
IfPostCode= "GIR 0AA" OrPostCode= "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
End Function
Click to expand...

Thanks Rick for Shape coding. Looks good.

Thanks also for your detailed explaination of my coding. However I'm
still trying to work out how to pass thePostcodeinto the function.
I'm using cells to getpostcode, eg, in cell B1 I'm putting
=validatepostcode(a1) which then returns True / False and this is
where I need the text string "valid" or "Invalid".

Cheers

Ed- Hide quoted text -

- Show quoted text -
Click to expand...

Hi ,

I think I've sorted it now, using an idea from another post, see code
below . I've removed the as boolean for the function.

Public Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
Dim invalid As Boolean
Parts = Split(PostCode)

invalid = False

If ValidatePostCode = PostCode = "GIR 0AA" Or _
((Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]"))) Then

Else
invalid = True
End If
If invalid Then

ValidatePostCode = "Invalid postcode"
Exit Function
Else
ValidatePostCode = "Valid postcode"
End If

End Function

Ed
 
I think I've sorted it now, using an idea from another post, see code
below . I've removed the as boolean for the function.

Public Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
Dim invalid As Boolean
Parts = Split(PostCode)

invalid = False

If ValidatePostCode = PostCode = "GIR 0AA" Or _
((Parts(1) Like "#[A-Z][A-Z]") And _
((Parts(0) Like "[A-Z]#") Or _
(Parts(0) Like "[A-Z]#[0-9A-Z]") Or _
(Parts(0) Like "[A-Z][A-Z]#") Or _
(Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]"))) Then

Else
invalid = True
End If
If invalid Then

ValidatePostCode = "Invalid postcode"
Exit Function
Else
ValidatePostCode = "Valid postcode"
End If

End Function
Click to expand...

Okay, I missed the part where you wanted to use the function up at the
spreadsheet level as opposed to running it wholly within the macro. In your
modification above, you don't really need to create a new variable to handle
the Valid/Invalid selection...

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
If ValidatePostCode Then
ValidatePostCode = "Valid"
Else
ValidatePostCode = "Invalid"
End If
End Function

Notice, all I did was check to see if ValidatePostCode was True and, if it
was, set the function name to "Valid"; otherwise, set it to "Invalid". This
works because the default value for any variable in VBA (the function name
act like a variable within function; that is why you assign the return value
to it) is 0, the numerical equivalent for False. So, ValidatePostCode is
equal to 0 (False) unless it passes the test within the If-Then block where
it will be set to True. Hence, you can use the value of ValidatePostCode
directly after the If-Then block to reset its value to "Valid" or "Invalid".

Rick
 
Hi - I realise this thread is old, but since this VBA code (or some very similar) can be found on a several forums, I thought I would raise what appears to be a small defect. But maybe it's just that the North London postcodes have changed since - I am by no means an expert. Perhaps someone could update this code for the latest formats?

The code does not validate the N# postcode areas. It looks like line 11 should be changed to:

ValidatePostCode = (Parts(0) Like "[BEGLMNSW]#*" Or _

Alex.
 
Back
Top