I've found an explanation on Wikipedia on how to validate an IBAN. Since the number generated from the transformation can cause overflow with all data types available in VBA, I've worked with
I'd like to have someone else review. Thanks in advance!
- Check that the total IBAN length is correct as per the country. If not, the IBAN is invalid .
- Move the four initial characters to the end of the string .
- Replace each letter in the string with two digits, thereby expanding the string, where A = 10, B = 11, ..., Z = 35.
- Interpret the string as a decimal integer and compute the remainder of that number modulo 97.
Public Function isValidIBAN(IBAN As String) As Boolean isValidIBAN = False Dim Country As String Dim CountryLenght As Integer Dim tempStr As String Dim c As String Dim o As Long Dim newStr As Variant Const Modder As Integer = 97 If IBAN = vbNullString Then Exit Function Country = Left(IBAN, 2) CountryLenght = 0 On Error Resume Next CountryLenght = Application.WorksheetFunction.VLookup(Country, Foglio3.Range("A:D"), 4, 0) 'This Search in a table --- Country|someVal|someVal|IBAN lenght On Error GoTo 0 If Len(IBAN) <> CountryLenght Then Exit Function 'move first 4 chars to right tempStr = Right(IBAN, Len(IBAN) - 4) & Left(IBAN, 4) 'loop throught single char in tempStr and if not numeric return 10 based number from letter 'use string in place of number to store new-generated "IBAN" For o = 1 To Len(tempStr) c = Mid(tempStr, o, 1) If Not IsNumeric(c) Then newStr = newStr & CStr(Range(c & 1).Column + 9) Else newStr = newStr & CStr(c) End If Next o c = vbNullString ' perform primary school' style division - digit by digit For o = 1 To Len(newStr) c = c & Mid(newStr, o, 1) myStr = myStr & CStr(Int(CLng(c) / Modder)) 'if is the last char in str check if mod is 1 - Only fired once If o = Len(str) Then isValidIBAN = ((CLng(c) Mod Modder) = 1) Exit Function End If c = IIf(CLng(c) < Modder, c, CLng(c) Mod Modder) Next o End Function