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
Medium Priority
1,883 Views
Hi,

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 & "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
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("")
'End Sub
'
'Private Sub test_GetIBAN()
'    Debug.Print GetIBAN("nl", "Rabo0123456789")
'    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")
'End Sub
'
'Private Sub test_GetMod97()
'    Debug.Print GetMod97("3214282912345698765432161182") ' from wiki page
'    Debug.Print GetMod97("RABO0123456789NL44")
'End Sub
``````
0
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

LVL 120

Accepted Solution

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

ID: 39875118
Thanks! I knew it must be simple!
0

## Featured Post

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…
###### Suggested Courses
Course of the Month7 days, 22 hours left to enroll