Patrick O'Dea
asked on
Using VBA to validate a bank code (IBAN)
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER