Bank accounts are identified with a standardized IBAN code that simplifies international transactions. The code identifies the country, the banking institution and a customer account. You’ll find a function to validate an international Bank account IBAN identifier with VB/A in this post, along with a bunch of test IBANs.
There’s a “checksum” contained in the IBAN, that can be validated using a modulo 97 algorithm. You can peek here for a detailed explanation of the validation: https://iban.co.uk/verification.html. I borrowed the modulo 97 algorithm from some code I found online, and wapped it at my sauce.
The encoding standard in Switzerland has its peculiarities; it has a length of 21 characters and the Bank ID is composed of only number digits. Same rules seems to apply for Lichtenstein (https://bank.codes/iban/structure/liechtenstein/).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
'IsValidIban 'Will check that an Iban code is valid according to the rules 'specified at https://bank.codes/iban/structure/liechtenstein/ 'They're for Lichtenstein and differ with Swiss rules on the last 'part only. Nonetheless, the Swiss validator of the same site 'checks this swiss Iban as correct: CH1900767000U00121977 'So I stick with the Lichtenstein rules to also validate Swiss codes. 'Validation explanation also here: https://iban.co.uk/verification.html Public Function IsValidIban(ByVal psIban As String) As Boolean Dim iLen As Integer 'trim, upper case and remove spaces psIban = UCase$(Replace(psIban, " ", "")) iLen = Len(psIban) If iLen = 0 Then Exit Function Dim sChecksum As String Dim sCountry As String Dim sBankCode As String Dim sAccountNumber As String sCountry = Left$(psIban, 2) 'Country code sChecksum = Mid$(psIban, 3, 2) sBankCode = Mid$(psIban, 5, 5) sAccountNumber = Right$(psIban, iLen - 9) If Not IsOnlyNumbers(sChecksum) Then Exit Function If Not IsAlphaNum(sAccountNumber) Then Exit Function If (sCountry = "CH") Or (sCountry = "LI") Then 'swiss has only numbers for bank codes If Not IsOnlyNumbers(sBankCode) Then Exit Function End If 'Prepare for checksum computing Dim sToCheck As String Dim sConverted As String 'converted to number according to translation table Dim i As Integer Dim iAsc As Integer Const ASC_A As Integer = 65 Const ASC_Z As Integer = 90 'sToCheck = sBankCode & sAccountNumber & sCountry & sChecksum sToCheck = Right$(psIban, iLen - 4) & Left$(psIban, 4) For i = 1 To Len(sToCheck) iAsc = Asc(Mid$(sToCheck, i, 1)) If (iAsc >= ASC_A) And (iAsc <= ASC_Z) Then 'if it's a letter sConverted = sConverted & CStr(iAsc - ASC_A + 10) Else sConverted = sConverted & Chr$(iAsc) 'otherwise just concatenate End If Next i 'Smart part from : https://gist.github.com/juancho618/f34a58c3c9c4ac903a453abc1de01ea8 'Loop through the IBAN, as it is too long to calculate at one go Dim CurrPart As String 'FF Dim Answer As String 'FF Dim CurrDigit As Integer 'FF Dim CurrNumber As Long 'FF Dim LeftOver As Long Dim Workvalue As Long CurrPart = "" Answer = "" For CurrDigit = 1 To Len(sConverted) CurrPart = CurrPart & Mid$(sConverted, CurrDigit, 1) CurrNumber = CLng(CurrPart) 'If the number can be divided If 97 <= CurrNumber Then LeftOver = CurrNumber Mod 97 Workvalue = (CurrNumber - LeftOver) / 97 Answer = Answer & CStr(Workvalue) CurrPart = CStr(LeftOver) Else 'If no division occurred, add a trailing zero If Len(Answer) > 0 Then Answer = Answer & "0" 'Exception for the last number If CurrDigit = Len(sConverted) Then LeftOver = CurrNumber Mod 97 Else End If Else End If End If Next If LeftOver = 1 Then IsValidIban = True End If End Function |
You will need these small support functions too. IsOnlyNumbers() will return True if a text is composed only of digit characters “0” to “9”, and IsAlphaNum() will return True if the text is composed either of digits or letters “A” to “Z” in UPPER case only.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Public Function IsOnlyNumbers(ByVal psText As String) As Boolean Const ASC_0 As Integer = 48 Const ASC_9 As Integer = 57 Dim i As Integer Dim iAsc As Integer For i = 1 To Len(psText) iAsc = Asc(Mid$(psText, i, 1)) If (iAsc < ASC_0) Or (iAsc > ASC_9) Then Exit Function Next i IsOnlyNumbers = True End Function Public Function IsAlphaNum(ByVal psText As String) As Boolean Const ASC_A As Integer = 65 Const ASC_Z As Integer = 90 Const ASC_0 As Integer = 48 Const ASC_9 As Integer = 57 Dim i As Integer Dim iAsc As Integer For i = 1 To Len(psText) iAsc = Asc(Mid$(psText, i, 1)) If ((iAsc < ASC_0) Or (iAsc > ASC_9)) And ((iAsc < ASC_A) Or (iAsc > ASC_Z)) Then Exit Function Next i IsAlphaNum = True End Function |
Here’s a piece of code to test the function, with a bunch of IBANs I gathered online:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
Public Sub Test_Ibans() Dim avIbans As Variant Dim vIban As Variant 'Series of GB Iban numbers, from https://iban.co.uk/examples.html Debug.Print "GB IBANS:" avIbans = Array( _ "GB24BKEN10000031510604", "GB27BOFI90212729823529", "GB17BOFS80055100813796", _ "GB92BARC20005275849855", "GB66CITI18500812098709", "GB15CLYD82663220400952", _ "GB26MIDL40051512345674", "GB26MIDL40051512345674", "GB76LOYD30949301273801", _ "GB25NWBK60080600724890", "GB60NAIA07011610909132", "GB29RBOS83040210126939", _ "GB79ABBY09012603367219", "GB21SCBL60910417068859", "GB42CPBK08005470328725") For Each vIban In avIbans Debug.Print "Checking IBAN [" & vIban & "]: "; If IsValidIban(vIban) Then Debug.Print "OK" Else Debug.Print "FAILED" End If Next 'Other lists of IBANS from https://bank.codes/iban/examples/ Debug.Print "EUROPEAN COUNTRIES (with one Swiss and Lichtenstein too!)" avIbans = Array( _ "AX2112345600000785", "AL47212110090000000235698741", "AD1200012030200359100100", _ "AT611904300234573201", "BY13NBRB3600900000002Z00AB00", "BE68539007547034", _ "BA391290079401028494", "BG80BNBG96611020345678", "HR1210010051863000160", _ "CY17002001280000001200527600", "CZ6508000000192000145399", "DK5000400440116243", _ "EE382200221020145685", "FO2000400440116243", "FI2112345600000785", _ "FR1420041010050500013M02606", "DE89370400440532013000", "GI75NWBK000000007099453", _ "GR1601101250000000012300695", "GL2000400440116243", "HU42117730161111101800000000", _ "IS140159260076545510730339", "IE29AIBK93115212345678", "IT60X0542811101000000123456", _ "XK051212012345678906", "LV80BANK0000435195001", "LI21088100002324013AA", _ "LT121000011101001000", "LU280019400644750000", "MK07250120000058984", _ "MT84MALT011000012345MTLCAST001S", "MD24AG000225100013104168", "MC5811222000010123456789030", _ "ME25505000012345678951", "NL91ABNA0417164300", "NO9386011117947", _ "PL61109010140000071219812874", "PT50000201231234567890154", "RO49AAAA1B31007593840000", _ "SM86U0322509800000000270100", "RS35260005601001611379", "SK3112000000198742637541", _ "SI56191000000123438", "ES9121000418450200051332", "SE4550000000058398257466", _ "CH9300762011623852957", "GB29NWBK60161331926819") For Each vIban In avIbans Debug.Print "Checking IBAN [" & vIban & "]: "; If IsValidIban(vIban) Then Debug.Print "OK" Else Debug.Print "FAILED" End If Next Debug.Print "NON EURPEAN COUNTRIES:" avIbans = Array( _ "AZ21NABZ00000000137010001944", "BH67BMAG00001299123456", "BR1800000000141455123924100C2", _ "VG96VPVG0000012345678901", "CR05015202001026284066", "DO28BAGR00000001212453611324", _ "SV62CENR00000000000000700025", "GF4120041010050500013M02606", "PF5720041010050500013M02606", _ "TF2120041010050500013M02606", "GE29NB0000000101904917", "GP1120041010050500013M02606", _ "GT82TRAJ01020000001210029690", "IR081234123412341234123412", "IQ98NBIQ850123456789012", _ "IL620108000000099999999", "JO94CBJO0010000000000131000302", "KZ86125KZT5004100100", _ "KW81CBKU0000000000001234560101", "LB62099900000001001901229114", "MQ5120041010050500013M02606", _ "MR1300020001010000123456753", "MU17BOMM0101101030300200000MUR", "YT3120041010050500013M02606", _ "NC8420041010050500013M02606", "PK36SCBL0000001123456702", "PS92PALS000000000400123456702", _ "QA73BBME000000000004056677001", "RE4220041010050500013M02606", "BL6820041010050500013M02606", _ "LC55HEMM000100010012001200023015", "MF8420041010050500013M02606", "PM3620041010050500013M02606", _ "ST68000100010051845310112", "SA0380000000608010167519", "TL380080012345678910157", _ "TN5910006035183598478831", "TR330006100519786457841326", "UA213996220000026007233566001", _ "AE070331234567890123456", "WF9120041010050500013M02606") For Each vIban In avIbans Debug.Print "Checking IBAN [" & vIban & "]: "; If IsValidIban(vIban) Then Debug.Print "OK" Else Debug.Print "FAILED" End If Next End Sub |
Public domain. Enjoy !
Recent Comments