?
Solved

Using VBA to validate a bank code (IBAN)

Posted on 2014-02-20
2
Medium Priority
?
2,119 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 2000 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

589 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