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.

 

[bolo]

Yes, the postcode example i am using is SK4 2HD.

I would like to include a function that corrects lower case to upper case instead of throwing "invalid postcode"

A big problem for me is that for each one of your functions you re dim the variable "valid". This to me is bad practice.

Anyway other problems include:

1)

If Not space = "" Then should be
If Not space = " " Then

Your functions also will not work on your example.

Your variable, number, is "K4" in your example so it will also be false

Your last three, "2HD", is tested to check that ALL 3 are letters. Therefore also false

So your function never works!###Doh! RoryA beat me to it!

 

[Mr.Daines]

To give you an idea (I'm ignoring the fact that you pass the postcode to the function but never use it - instead the function refers to a textbox directly):

Input: SK4 2HD

first = Left(poCode, 1) - this is "S"
number = Mid(poCode, 2, 2) - this is "K4" so fails isNumeric test, but isNumeric returns True anyway!
space = Mid(poCode, 4, 1) - this is " "
last = Right(poCode, 3) - this is "2HD"

you then test:

If Not space = "" Then

which will always fail since space is " " and not "".

I think i am out of my depth on this one.

All i want is when a person fills in the userform and presses submit[cmdAdd] a code runs to check that the postcode[tbpostcode as a text box] is in the valid UK format and if it is in lowercase it converts to uppercase before dropping on my worksheet.

Maybe i have made this harder for myself than it actually is.

Sorry again. Still very new to VBA and any form of code....

 

[ZVI]

Use: poCode = UCase(tbpostcode.Text) to upercase converting

 

[Mr.Daines]

A big problem for me is that for each one of your functions you re dim the variable "valid". This to me is bad practice.

Anyway other problems include:

1)

If Not space = "" Then should be
If Not space = " " Then

Your functions also will not work on your example.

Your variable, number, is "K4" in your example so it will also be false

Your last three, "2HD", is tested to check that ALL 3 are letters. Therefore also false

So your function never works!###Doh! RoryA beat me to it!

Hello,

Thanks for your interest.

My code will probably be full of "bad practice" as i am new to VBA or any type of coding.

Would you be able to submit a full code example with some notes to state what is needed and what isn't and why so i can get a better understanding where i have gone wrong?

If not no problems.

Thanks again.

 

[RoryA]

Check the link that SuperFerret posted and Bob's (xld) posted function. UK post codes are a lot more complicated than people tend to think!

 

[Mr.Daines]

Use: poCode = UCase(tbpostcode.Text) to upercase converting

Hello ZVI,

Thanks for your interest.

Where would be the best place for this in my code?

Kind regards,

 

[bolo]

I think i am out of my depth on this one.

All i want is when a person fills in the userform and presses submit[cmdAdd] a code runs to check that the postcode[tbpostcode as a text box] is in the valid UK format and if it is in lowercase it converts to uppercase before dropping on my worksheet.

Maybe i have made this harder for myself than it actually is.

Sorry again. Still very new to VBA and any form of code....

You also need to think through your function for the exceptions.

For example....

SO15 3FS

is a valid post code but of a different format to the one you tested with. You need to capture all possible proper post codes... Then there are the post codes in london like W1T 4BQ which again you need to capture... Sorry but you really need to put in more thought before you try to code this....

 

[Mr.Daines]

Check the link that SuperFerret posted and Bob's (xld) posted function. UK post codes are a lot more complicated than people tend to think!

I looked at that post but i am unsure how i would implement that into my userform. Would i place it in the cmdAdd_click coding? or would it be in a module.

I have placed it in a module and added call validpostcode but it comes up with argument not optional...

Can anyone recommend a good book about VBA from beginner to expert?

 

[RoryA]

You would put it in a normal module, then in your form use:

If Not validPostCode(tbpostcode.Text) Then
   msgbox "Invalid postcode!"
End If

for example.

No single book will take you from beginner to expert in VBA, in my opinion. I'd start with a John Walkenbach Excel Power Programming book or one of Bill Jelen's (with the lovely Tracy) or even start with a VBA for Dummies just to ensure you get the fundamentals sorted out first.

 

[ZVI]

As to the SuperFerret link to XLD’s code, use it in this way:

Private Sub cmdAdd_Click()
  tbpostcode.Text = UCase(tbpostcode.Text)
  If Not validPostcode(tbpostcode.Text) Then MsgBox "Wrong postcode: " & tbpostcode.Text
End Sub