Created
October 19, 2018 13:06
-
-
Save afvanwoudenberg/5ab600b1bddea940f9bf7100845f8a6c to your computer and use it in GitHub Desktop.
VBA code to check if an IBAN bank account number is valid or not
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Option Compare Database | |
Option Explicit | |
' http://en.wikipedia.org/wiki/International_Bank_Account_Number | |
Private Const IbanCountryLengths As String = "AL28AD24AT20AZ28BH22BE16BA20BR29BG22CR21HR21CY28CZ24DK18DO28EE20FO18" & _ | |
"FI18FR27GE22DE22GI23GR27GL18GT28HU28IS26IE22IL23IT27KZ20KW30LV21LB28" & _ | |
"LI21LT20LU20MK19MT31MR27MU30MC27MD24ME22NL18NO15PK24PS29PL28PT25RO24" & _ | |
"SM27SA24RS22SK24SI19ES24SE24CH21TN24TR26AE23GB22VG24QA29" | |
Private Function ValidIbanCountryLength(CountryCode As String, IbanLength As Integer) As Boolean | |
Dim i As Integer | |
For i = 0 To Len(IbanCountryLengths) / 4 - 1 | |
If Mid(IbanCountryLengths, i * 4 + 1, 2) = CountryCode And _ | |
CInt(Mid(IbanCountryLengths, i * 4 + 3, 2)) = IbanLength Then | |
ValidIbanCountryLength = True | |
Exit Function | |
End If | |
Next i | |
ValidIbanCountryLength = False | |
End Function | |
Private Function Mod97(Num As String) As Integer | |
Dim lngTemp As Long | |
Dim strTemp As String | |
Do While Val(Num) >= 97 | |
If Len(Num) > 5 Then | |
strTemp = Left(Num, 5) | |
Num = Right(Num, Len(Num) - 5) | |
Else | |
strTemp = Num | |
Num = "" | |
End If | |
lngTemp = CLng(strTemp) | |
lngTemp = lngTemp Mod 97 | |
strTemp = CStr(lngTemp) | |
Num = strTemp & Num | |
Loop | |
Mod97 = CInt(Num) | |
End Function | |
Public Function ValidIban(IBAN As String) As Boolean | |
Dim strIban As String | |
Dim i As Integer | |
strIban = UCase(IBAN) | |
' Remove spaces | |
strIban = Replace(strIban, " ", "") | |
' Check if IBAN contains only uppercase characters and numbers | |
For i = 1 To Len(strIban) | |
If Not ((Asc(Mid(strIban, i, 1)) <= Asc("9") And Asc(Mid(strIban, i, 1)) >= Asc("0")) Or _ | |
(Asc(Mid(strIban, i, 1)) <= Asc("Z") And Asc(Mid(strIban, i, 1)) >= Asc("A"))) Then | |
ValidIban = False | |
Exit Function | |
End If | |
Next i | |
' Check if length of IBAN equals expected length for country | |
If Not ValidIbanCountryLength(Left(strIban, 2), Len(strIban)) Then | |
ValidIban = False | |
Exit Function | |
End If | |
' Rearrange | |
strIban = Right(strIban, Len(strIban) - 4) & Left(strIban, 4) | |
' Replace characters | |
For i = 0 To 25 | |
strIban = Replace(strIban, Chr(i + Asc("A")), i + 10) | |
Next i | |
' Check remainder | |
ValidIban = Mod97(strIban) = 1 | |
End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment