Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" wrote in message ups.com... 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" wrote in message ups.com... 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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" wrote in message ups.com... 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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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/fre...dquestions.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" wrote in message ... 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" wrote in message ups.com... 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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" <peter_t@discussions wrote in message ... 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/fre...dquestions.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" wrote in message ... 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" wrote in message ups.com... 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" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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/a...ear=pos tcode 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" wrote: 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" wrote in message ups.com... 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" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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" wrote in message ... 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/a...XA1VZTG1KROFB2 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 <snip |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
On Sep 18, 5:24 pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message ... 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/a...onid=C2XA1VZTG... 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 UKPostcode" Else MsgBox "Invalid UKPostcode" End If Set Collection = Nothing Set RegExp = Nothing Set Myrange = Nothing End Sub Mike <snip- Hide quoted text - - Show quoted text - 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
On 18 Sep, 22:38, "Rick Rothstein \(MVP - VB\)"
wrote: Function ValidatePostCode(ByValPostCodeAs 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 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 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
On 19 Sep, 08:52, Ed Peters wrote:
On 18 Sep, 22:38, "Rick Rothstein \(MVP - VB\)" wrote: Function ValidatePostCode(ByValPostCodeAs 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 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 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 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 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 - 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full proof UK postcode validation
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Postcode structure for data validation | Excel Discussion (Misc queries) | |||
How to make a spreadsheet idiot-proof | Excel Worksheet Functions | |||
Postcode Validation | Excel Discussion (Misc queries) | |||
How to tamper proof cells? | Excel Discussion (Misc queries) | |||
Postcode validation | Excel Discussion (Misc queries) |