Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using VBA to validate a bank code (IBAN)

Posted on 2014-02-20
2
Medium Priority
?
1,883 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
[X]
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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

610 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