VBA IBAN Validator

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.

17 thoughts on “VBA IBAN Validator

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

    1. Adding QA29 to the string should do the trick. I’ve updated the code in the blogpost. Are you sure the account number you’re testing with is correct? I’ve tried the following numbers that seem to work:
      QA98SCBL000000000001440682640
      QA47SCBL000000000009276829140
      QA81SCBL000000000009279369540

      Some sites that allow you to verify IBAN numbers are:
      http://www.ibq.com.qa/bankingibq/iban/validator_en_gb.aspx
      http://212.77.211.241/IBAN/IBAN_New.aspx
      See if these can validate the numbers you’re testing with.

  2. 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

  3. 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 :).

    1. 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

  4. 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…

Leave a Reply

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