Using VBA to validate a bank code (IBAN)

Posted on 2014-02-20
Last Modified: 2014-02-20

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,
' 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
'               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
    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
        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

Question by:Patrick O'Dea
LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 39875089
to get a result back,

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

Author Closing Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now