Post Code Validation Visual Basic

[Groves29]

Hi,

I have looked for visual basic to validate some postcodes. I believe it needs updating as some of the postcodes I know are correct are returning as not validated.

Below is the VB Code

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

I am looking to validate postcode EX23 0BH. This postcode is correct as it returns an address from a web address finder.

Please could you tell me how and where I can update the code to ensure this postcode is valid

Thanks

 

[Mike LH]

Hi,

The code below which uses regular expressions will evaluate all uk postcodes. The postcode must be properly formatted in the cell with a space as in you EX23 0BH.

On the worksheet call with

=postcode(A1)

Function postcode(pc As String) As Boolean
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim 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
    
        Outstring = ""
        Set Collection = RegExp.Execute(pc)
        For Each RegMatch In Collection
            Outstring = Outstring & RegMatch
        Next
       
       If pc <> "" And pc = Outstring Then
            postcode = True
       Else
            postcode = False
       End If
    Set Collection = Nothing
    Set RegExp = Nothing
End Function

 

[Groves29]

Hi,

The code below which uses regular expressions will evaluate all uk postcodes. The postcode must be properly formatted in the cell with a space as in you EX23 0BH.

On the worksheet call with

=postcode(A1)

Function postcode(pc As String) As Boolean
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim 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
    
        Outstring = ""
        Set Collection = RegExp.Execute(pc)
        For Each RegMatch In Collection
            Outstring = Outstring & RegMatch
        Next
       
       If pc <> "" And pc = Outstring Then
            postcode = True
       Else
            postcode = False
       End If
    Set Collection = Nothing
    Set RegExp = Nothing
End Function

I could not get this code to work. I placed in VB code but the postcode formula in the workbook is not recognised

 

[Mike LH]

Hi,

ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code in there on the right. It's what's called a 'General Module'

 

[Groves29]

Works a treat!

Thanks Mike