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.

 

[Mr.Daines]

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

Hello Rick,

Thanks for your suggestions, i am very grateful.

What i have now done [probably not best practice but works great none the less] is combine your suggestion and function with my own function[kept it as a function]

I have placed the function you provided in module1 alongside my function, so now it use best of both. Heres how i did it [probably could tidy it up some what but im not that skilled].

Code in main Sub.

pc = tbpostcode.Text
If Not ValidatePostCodeShort(pc) Then
  ValidatePostCode (pc)
  Me.tbpostcode.SetFocus
Exit Sub
End If

Thanks again.

 

[alemcodon]

Hi,

Im new to this forum and not brilliant with excel.

ive been following this thread to create a similar spreadsheet for validating postcodes. I keep getting errors....

could you possibly post an excel file with all the functionality working for validating postcodes? please....????