Solved

Using VBA to validate a bank code (IBAN)

Posted on 2014-02-20
2
1,731 Views
Last Modified: 2014-02-20
Hi,

See code below which I downloaded.
I understand the detail of the code but am unsure about how I interpret the result!

Suppose I am validating the string "BankCode99".

Presumably I call the validation as follows;

IsValidIBAN BankCode99

......

After the code returns from the function what IF condition do I use to determine if I have failed or succeeded the validation


How do I check if I have passed the validation?



'---------------------------------------------------------------------------------------------------
' Module:       MIBAN
' Author:       Frans Bus, pixcels.nl/iban-check
' Date:         2013-08-28
' Public:       - function IsValidIBAN(IBAN): returns True if IBAN is valid
'               - function GetIBAN(CountryCode, BBAN): returns a valid IBAN or "Error"
' Private:      - function GetBBANFormat(CountryCode) returns the format of the BBAN in Like format.
'               - general function GetMod97(String) returns the result of String Mod 97 as a long.
' Note:         Based on http://en.wikipedia.org/wiki/International_Bank_Account_Number.
'               Please refer to this wiki page for details.
'---------------------------------------------------------------------------------------------------

Option Explicit

Public Function IsValidIBAN(ByVal IBAN As String) As Boolean
    IBAN = UCase(Replace(IBAN, " ", ""))
    IsValidIBAN = False
    If Len(IBAN) < 6 Or Len(IBAN) > 34 Then Exit Function
    If Not Left(IBAN, 4) Like "[A-Z][A-Z]##" Then Exit Function
    If Not Mid(IBAN, 5) Like GetBBANFormat(Left(IBAN, 2)) Then Exit Function
    IBAN = Mid(IBAN, 5) & Left(IBAN, 4)
    IsValidIBAN = (GetMod97(IBAN) = 1)
End Function


Public Function GetIBAN(ByVal CountryCode As String, ByVal BBAN As String) As String
    Dim D As Long
    CountryCode = UCase(Trim(CountryCode))
    BBAN = UCase(Replace(BBAN, " ", ""))
    GetIBAN = "Error!"
    If Not BBAN Like GetBBANFormat(CountryCode) Then Exit Function
    D = 98 - GetMod97(BBAN & CountryCode & "00")
    GetIBAN = CountryCode & Format(D, "00") & BBAN
End Function


'--- private functions


Private Function GetBBANFormat(ByVal CountryCode As String) As String
    Dim sFormats As String, s As String, sChar As String
    Dim v As Variant
    Dim i As Long, j As Long
    CountryCode = UCase(Trim(CountryCode))
    If Not CountryCode Like "[A-Z][A-Z]" Then Exit Function
    sFormats = vbNullString
    sFormats = sFormats & "AD=8n,12c;AE=3n,16n;AL=8n,16c;AT=16n;AZ=4c,20n;BA=16n;BE=12n;BG=4a,6n,8c;BH=4a,14c;BR=23n,1a,1c;"
    sFormats = sFormats & "CH=5n,12c;CR=17n;CY=8n,16c;CZ=20n;DE=18n;DK=14n;DO=4a,20n;EE=16n;ES=20n;FI=14n;FO=14n;FR=10n,11c,2n;"
    sFormats = sFormats & "GB=4a,14n;GE=2c,16n;GI=4a,15c;GL=14n;GR=7n,16c;GT=4c,20c;HR=17n;HU=24n;IE=4c,14n;IL=19n;IS=22n;IT=1a,10n,12c;"
    sFormats = sFormats & "KW=4a,22c;KZ=3n,13c;LB=4n,20c;LI=5n,12c;LT=16n;LU=3n,13c;LV=4a,13c;"
    sFormats = sFormats & "MC=10n,11c,2n;MD=2c,18n;ME=18n;MK=3n,10c,2n;MR=23n;MT=4a,5n,18c;MU=4a,19n,3a;NL=4a,10n;NO=11n;"
    sFormats = sFormats & "PK=4c,16n;PL=24n;PS=4c,21n;PT=21n;RO=4a,16c;RS=18n;SA=2n,18c;SE=20n;SI=15n;SK=20n;SM=1a,10n,12c;TN=20n;TR=5n,17c;VG=4c,16n;"
    i = InStr(sFormats, CountryCode & "=") ' position of countrycode in sFormats
    If i = 0 Then Exit Function ' countrycode not found
    j = InStr(i, sFormats, ";")
    v = Split(Mid(sFormats, i + 3, j - i - 3), ",") ' if cc="AD" then v(0)="8n", v(1)="12c"
    s = vbNullString
    For i = 0 To UBound(v)
        Select Case Right(v(i), 1)
        Case "n": sChar = "#" ' n = numeric
        Case "c": sChar = "[A-Z0-9]" ' c = character = alphanumeric
        Case "a": sChar = "[A-Z]" ' a = alphabetic
        End Select
        For j = 1 To Val(v(i))
            s = s & sChar
        Next j
    Next i ' after v(0)="3c" s will hold "[A-Z0-9][A-Z0-9][A-Z0-9]"
    GetBBANFormat = s
End Function


Private Function GetMod97(ByVal D As String) As Long
    ' a general implementation of Mod 97 for large integers as described in
    ' http://en.wikipedia.org/wiki/International_Bank_Account_Number
    Dim N As String, s As String
    Dim i As Long
    For i = Asc("A") To Asc("Z")
        D = Replace(D, Chr(i), i - 55) ' eg "A" replaced by "10"
    Next i
    N = Left(D, 9) ' step 1, first 9 digits
    D = Mid(D, 10) ' D holds remaining digits
    Do
        N = CStr(CLng(N) Mod 97) ' step 2.
        If Len(D) > 7 Then ' prepare for another calculation
            s = Left(D, 7) ' next 7 digits of D
            D = Mid(D, 8) ' remaining digits
        Else ' prepare for final calculation
            s = D
            D = vbNullString
        End If
        N = N & s ' step 3.
    Loop Until D = vbNullString
    GetMod97 = CLng(N) Mod 97
End Function


'--- module tests


' Use sheet "IBAN samples" to test the public functions.
' Or use these procedures to test the module functions:
'
'Private Sub test_IsValidIBAN()
'    Debug.Print IsValidIBAN("NL44RABO0123456789")
'    Debug.Print IsValidIBAN("AD2912345678ABCD5EFGH012")
'    Debug.Print IsValidIBAN("")
'End Sub
'
'Private Sub test_GetIBAN()
'    Debug.Print GetIBAN("nl", "Rabo0123456789")
'    Debug.Print GetIBAN("ad", "12345678ABCD5EFGH012")
'    Debug.Print GetIBAN("nl", "ingb0123456789")
'    Debug.Print GetIBAN("nl", "trio0123456789")
'    Debug.Print GetIBAN("nl", "RTEF0518590015") ' Check number < 10
'    Debug.Print GetIBAN("gb", "ABCD56789012345678")
'End Sub
'
'Private Sub test_GetBBANFormat()
'    Debug.Print "RABO0123456789" Like GetBBANFormat("nl"), GetBBANFormat("nl")
'    Debug.Print "12345678ABCD5EFGH012" Like GetBBANFormat("ad"), GetBBANFormat("ad")
'End Sub
'
'Private Sub test_GetMod97()
'    Debug.Print GetMod97("3214282912345698765432161182") ' from wiki page
'    Debug.Print GetMod97("RABO0123456789NL44")
'End Sub

Open in new window

0
Comment
Question by:Patrick O'Dea
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39875089
to get a result back,

if IsValidIBAN("BankCode99") then
    msgbox "Valid"
   else
   msgbox "NOT valid"
end if
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39875118
Thanks! I knew it must be simple!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question