Postcode validation VBA

[Mr.Daines]

Hello all,

I have created a postcode validation script for my userform but it always throws up the message saying Invalid postcode regardless of what postcode i enter [valid or not].

Can anyone see where i have gone wrong?

' Validates the postcode entered - Format Check
Private Function validPostcode(postCode) ' Format check
Dim valid As Boolean
Dim first As String
Dim number As String
Dim last As String
Dim poCode As String
Dim space As String
Dim i As String
Dim cha As String
' Splits the postcode into sections.
poCode = tbpostcode.Text
first = Left(poCode, 1)
number = Mid(poCode, 2, 2)
space = Mid(poCode, 4, 1)
last = Right(poCode, 3)
valid = True
' If there is data
If Not poCode = "" Then
valid = True
End If
' Checking the value of first digit
If Not frontLetter(first) Then
valid = False
End If
' Checking the second and third characters
If Not isNumeric(number) <> 7 Then
valid = False
End If
' Checks to see if there is a space
' After the fourth digit
If Not space = "" Then
valid = False
End If
' Checks that last three digits
If Not lastThree(last) Then
valid = False
End If
validPostcode = valid
' Display error if invalid postcode
If valid = False Then
MsgBox "Please enter a valid postcode", vbOKOnly
End If
End Function
 
' Function to validate the front letter of the postcode
Private Function frontLetter(s As String)
Dim ing As Integer
Dim ch As String
Dim valid As Boolean
valid = True
For ing = 1 To Len(s)
ch = Mid(s, ing, 1)
If ch < "A" Or ch > "Z" Then
valid = False
End If
Next ing
frontLetter = valid
End Function
 
' Function to validate numeric value of
' The postcode
Private Function isNumeric(s As String)
Dim ing As Integer
Dim ch As String
Dim valid As Boolean
valid = True
For ing = 1 To Len(s)
ch = Mid(s, ing, 1)
If ch < "0" Or ch > "9" Then
valid = False
End If
Next ing
isNumeric = valid
End Function
 
' Validate last three characters of postcode
Private Function lastThree(s As String)
Dim ing As Integer
Dim ch As String
Dim valid As Boolean
valid = True
For ing = 1 To Len(s)
ch = Mid(s, ing, 1)
If ch < "A" Or ch > "Z" Then
valid = False
End If
Next ing
lastThree = valid
End Function

Any help much appreciated.

 

[Rick Rothstein]

This function will verify if the text passed into it is of a valid format to be a UK post code... but it does not verify that the post code is actually in use anywhere, on that it is of the proper "shape".

Function ValidatePostCode(ByVal PostCode As String) As Boolean
  Dim Parts() As String
  PostCode = UCase$(PostCode & " ")
  Parts = Split(PostCode)
  ValidatePostCode = (PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
              (Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _
              (Parts(0) Like "[A-PR-UWYZ]#" Or _
               Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _
               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _
               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
End Function

 

[Biz]

This function will verify if the text passed into it is of a valid format to be a UK post code... but it does not verify that the post code is actually in use anywhere, on that it is of the proper "shape".

Function ValidatePostCode(ByVal PostCode As String) As Boolean
  Dim Parts() As String
  PostCode = UCase$(PostCode & " ")
  Parts = Split(PostCode)
  ValidatePostCode = (PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
              (Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _
              (Parts(0) Like "[A-PR-UWYZ]#" Or _
               Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _
               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _
               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
End Function

Nice compact function.

Biz

 

[RoryA]

Rick,
Those first two tests can never match as you added a trailing space to the postcode.

 

[Rick Rothstein]

Rick,
Those first two tests can never match as you added a trailing space to the postcode.

Good catch! I wrote that function some time ago and the best I can figure, I ended up concatenating the space on the end to handle the error that would be generated if an empty string (corresponding to an empty cell) were passed in and then forgot to update that part of the test. Those two postal codes were also added later on when I found out that they existed (they are special assignments in some way as I remember and, as such, would almost never be passed in under normal circumstances). Anyway, the fix is easy (just add a trailing space to each string constant in the test), but first I want to thank you agaain for catching it... thanks!

Function ValidatePostCode(ByVal PostCode As String) As Boolean
  Dim Parts() As String
  PostCode = UCase$(PostCode & " ")
  Parts = Split(PostCode)
  ValidatePostCode = (PostCode = "GIR 0AA " Or PostCode = "SAN TA1 " Or _
                     (Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _
                     (Parts(0) Like "[A-PR-UWYZ]#" Or _
                      Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _
                      Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _
                      Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
End Function

 

[RoryA]

Those two postal codes were also added later on when I found out that they existed (they are special assignments in some way as I remember and, as such, would almost never be passed in under normal circumstances)

The first is Girobank, which is still in use but not officially recognised.
The second is for kids' Christmas letters I assume.

 

[Mr.Daines]

Having a little difficulty here still.

Currently my sub wont exit if condition is met

Condition

2: If ValidatePostCode(tbpostcode.Text) = True Then
   MsgBox "Wrong postcode: " & tbpostcode.Text
Exit Sub
End If

Function

Function 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 Function
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 Function
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 Function
End If
End Function

Any ideas?

 

[Rick Rothstein]

Having a little difficulty here still.

Currently my sub wont exit if condition is met

I think the problem is that no where in your function do you assign a True or False value to the function's name, and you did not declare a data type for your function name, so the function never returns a True or False value.

By the way, you should give consideration to using the function I posted... on a fast look-over, it appears like your function might show some invalid post codes (according to the rules for creating post codes) as being valid.

 

[Mr.Daines]

I think the problem is that no where in your function do you assign a True or False value to the function's name, and you did not declare a data type for your function name, so the function never returns a True or False value.

By the way, you should give consideration to using the function I posted... on a fast look-over, it appears like your function might show some invalid post codes (according to the rules for creating post codes) as being valid.

Thank you for the advice.

How would i implement

I think the problem is that no where in your function do you assign a True or False value to the function's name, and you did not declare a data type for your function name, so the function never returns a True or False value.

as i have tested my function and it works with the 5,000 postcodes i have ran through it.

Thanks again for your reply.

 

[Mr.Daines]

Having a little difficulty here still.

Currently my sub wont exit if condition is met

Condition

2: If ValidatePostCode(tbpostcode.Text) = True Then
   MsgBox "Wrong postcode: " & tbpostcode.Text
Exit Sub
End If

Function

Function 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 Function
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 Function
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 Function
End If
End Function

Any ideas?

I have resolved this myself, thanks for looking.

changed

2: If ValidatePostCode(tbpostcode.Text) = True Then
   MsgBox "Wrong postcode: " & tbpostcode.Text
Exit Sub
End If

to

2: If ValidatePostCode(tbpostcode.Text) = invalid Then
   Me.tbpostcode.SetFocus
Exit Sub
End If

Thanks to everyone for their help

 

[Rick Rothstein]

How would i implement as i have tested my function and it works with the 5,000 postcodes i have ran through it.

Okay, I would like to make a couple of comments for you to consider...

First, you called your procedure a "Function" (which is why I made my "you do not assign a True or False value to the function's name" name comment), but you do not use it like a function, rather, you appear to use it like a subroutine. Functions return values whereas subroutines (abbreviated Sub for use in declaring a subroutine) perform an action. Think of Sin function. You do not write...

Sin 0.5

rather, you pass the 0.5 into the Sin function and use the value it calculates, for example...

CalculatedValue = Sin(0.5)

Your "function" does not return a value, so it is not really a function (even though VB's "rules" are loose enough to allow you to declare it as a function but call it like a subroutine. Your code performs an action... either doing nothing if the post code is valid or popping up a MessageBox if not. So it would be more proper, and less confusing to others, if you changed the word "Function" to Sub within your code (everything will still function as it does now).

Second, your code does not catch all invalid Post Code constructions. For example, your code does not report QZ1 2AA as being invalid, but the rules for forming Post Codes specifically says...

The letters Q, V and X are not used in the first position.
The letters I, J and Z are not used in the second position.

Here is a website delineating the above rules plus several more...

http://www.thalesjacobi.com/UK_postcodes#Rules

Third, the code I posted is a function, so you would use its return value in whatever way you thought appropriate. You could use it in a cell on a worksheet...

=ValidatePostCode(A1)

or possibly....

=IF(ValidatePostCode(A1),"Valid","Not Valid")

or you could use it directly within other VB code like this...

PC = "QZ1 2AA"
If ValidatePostCode(PC) Then
  MsgBox "That post code is valid."
Else
  MsgBox "That post code is not valid!"
End If