VBA IBAN Validator – Aswin van Woudenberg

From February 2014 onwards, IBAN will be the new European standard for bank account numbers. This standardization is part of the SEPA initiative to improve the efficiency of cross-border euro payments.

I wrote an IBAN validator in VBA for an MS Access database I’m maintaining, but you can also use it in Excel or any other application that supports VBA.

modValidIban.bas

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

You can find a copy of this code as a GitHub gist here.