Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Accepted Solution

Rey Obrero (Capricorn1) 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

765 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