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.

The function ValidateIban takes an IBAN bank account number and returns True if the number is valid and False otherwise. It checks if the length is correct as per country and if, after rearranging and converting the IBAN code to an integer, the remainder of the modulo 97 operation equals 1. See wikipedia for a description of how to validate an IBAN number.


Aswin van Woudenberg

I'm a lecturer at the NHL Stenden University of Applied Sciences where I teach Artificial Intelligence, Algorithms and Concurrent Programming. Together with my students I'm building dialogue systems that can learn, reason and converse in a variety of domains.

19 Comments

Michiel · September 16, 2013 at 6:54 pm

Thanks! Works like a charm

Fonzie · October 8, 2013 at 10:22 am

This works great, U saved me alot work by not inventing the wheel twice

Nico · January 2, 2014 at 7:54 pm

Mooi, scheelt me een hoop werk.
Thanks.

Harald Klein · January 3, 2014 at 9:39 pm

Danke 🙂 🙂 🙂

Antonio Sierra · January 22, 2014 at 6:27 pm

Muchas Gracias. thank you

Arend Jan · January 24, 2014 at 12:44 pm

Dit wordt zeer gewaardeerd. Bedankt!

Raymond · March 6, 2014 at 7:28 am

Thanks!

ALNER · March 22, 2014 at 9:35 am

It’s not working on Qatar IBANs,,, do we have update on the codes? I incuded QA29 on the string but still not working.

SergeDo · April 3, 2014 at 8:10 am

Great It save a lot of work Thanks a lot.

David · September 17, 2014 at 10:18 am

Scheelt een hoop werk, en zeer helder geschreven. Thanks!

Japerik · November 25, 2014 at 8:17 pm

Was just testing my own routines when I struck on the international differences (ABNA only specifies the NL-conventions). Found this site: Copy-paste and run! Thanks for transparent programming!

One minor thing: You do some cosmetic repairs just before testing. The original input value may not be accepted by SEPA systems . . .
If OK then
return a polished IBAN
* In Uppercase
* spaces, underscores, dashes, etc. removed
Else
return “”
End If

Dhr · March 17, 2015 at 11:13 am

Thanks, this really works!

Joolz · April 7, 2015 at 11:52 am

this was fantastic, thanks

Jeffrey · March 30, 2016 at 8:57 am

Code works great, Thanks.

I added some code for getting the IBAN if you only have the accountnumber and the Bank it belongs to.

Public Function GetIBAN(AccountNr As String, BankCode As String, Optional CountryCode As String = “NL”) As String
Dim strIBAN As String
Dim intCheckSum As Integer
Dim i As Integer

AccountNr = Format(AccountNr, “0000000000”)
‘ Arrange
strIBAN = UCase(BankCode) & aRekNr & aCountryCode

‘ Replace characters
For i = 0 To 25
strIBAN = Replace(strIBAN, Chr(i + Asc(“A”)), i + 10)
Next i

‘ Get remainder
intCheckSum = 98 – Mod97(strIBAN & “00”)

GetIBAN = aCountryCode & Format(intCheckSum, “00”) & UCase(BankCode) & AccountNr
End Function

If anyone should need it :).

    Jeffrey · March 30, 2016 at 9:19 am

    Sorry, posted to fast:

    Private Function GetCountryAccountLength(CountryCode As String)
    Dim i As Integer
    For i = 0 To Len(IbanCountryLengths) / 4 – 1
    If Mid(IbanCountryLengths, i * 4 + 1, 2) = CountryCode Then
    GetCountryAccountLength = CInt(Mid(IbanCountryLengths, i * 4 + 3, 2)) – 8
    Exit Function
    End If
    Next i
    GetCountryAccountLength = -1
    End Function

    Public Function GetIBAN(AccountNr As String, BankCode As String, Optional CountryCode As String = “NL”) As String
    Dim strIBAN As String
    Dim intCheckSum As Integer
    Dim i As Integer

    AccountNr = Format(AccountNr, Left(“00000000000000000000000000000000000000000000000000”, GetCountryAccountLength(CountryCode)))
    ‘ Arrange
    strIBAN = UCase(BankCode) & AccountNr & CountryCode

    ‘ Replace characters
    For i = 0 To 25
    strIBAN = Replace(strIBAN, Chr(i + Asc(“A”)), i + 10)
    Next i

    ‘ Get remainder
    intCheckSum = 98 – Mod97(strIBAN & “00”)

    GetIBAN = CountryCode & Format(intCheckSum, “00”) & UCase(BankCode) & AccountNr
    End Function

Hans · June 26, 2016 at 8:04 pm

Aswin en Kevin, allebei bedankt voor deze fijne functies.
Is het ook mogelijk om het IBAN te berekenen aan de hand van alleen het rekeningnummer? Op verschillende websites gebeurt dit tenslotte ook…

Joe Camilleri · May 18, 2018 at 6:27 am

How do you make this work in access? I created a button in an access form, so when i click it, it takes the iban number from a text box and validates it. On the click event i wrote ValidateIban (Text0) . Text0 is the textbox i write the number in. Now whatever number I insert in the text box , it will not tell me if it is a valid number or not.
Thanks 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *