CODEWITHSUNDEEP
excelexcel-2007excel-2003vba
user1080320
user1080320

Reputation: 253

Validate IBAN in Excel

Is there any ready excel sheet to validate the IBAN? I will enter the IBAN and it will show valid or invalid.

I have searched for a number of Adds In and found this one

But I don't know how to open it. Can anyone help?

Upvotes: 4

Views: 26789

Answers (4)

selami sahin
selami sahin

Reputation: 21

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 ValidateIbanCountryLength(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
            ValidateIbanCountryLength = True
            Exit Function
        End If
    Next i
    ValidateIbanCountryLength = 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 ValidateIban(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
            ValidateIban = False
            Exit Function
        End If
    Next i

    ' Check if length of IBAN equals expected length for country
    If Not ValidateIbanCountryLength(Left(strIban, 2), Len(strIban)) Then
        ValidateIban = 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
    ValidateIban = Mod97(strIban) = 1
End Function

source :http://www.aswinvanwoudenberg.com/2013/07/18/vba-iban-validator/

Upvotes: 2

ConnDublin
ConnDublin

Reputation: 1

I found the answer from bonsvr helpful, thank you. From my reading of the code, it seems this was specific to the CZ range of accounts.

As I deal mostly with Irish, UK and German IBAN codes I developed this regular expression to replace the line with objRegExp.Pattern = ... with ...

objRegExp.Pattern = "^[GB|IE]{2}\d{2}[a-zA-Z]{4}\d{14}|[DE]\d{20}$"

I hope this helps someone else, as the initial code helped me. If you wish to add your own countries, extend the above.

Note: I removed the provision of spaces ([ ]) as the text I am testing has none. If you wish to add these back every 4 characters, this is easy to do - or simpler still, replace the first line of the code above with:

IBAN = Trim(Ucase(Replace(IBAN, " ", "")))

which will eliminate the spaces, trim off any extra spaces at the front and back and convert to upper case. (Trim may be redundant, but belts and braces...)

The regex expression is made up of [GB|IE] (GB or IE) allows for either ISO country code followed by the same format of the 2-digit checksum, the 4 character bank code and 14 digits thereafter, as is the case for these two country IBAN formats. The |[DE] allows for another "or" for Germany, followed by 22 digits. To add another country, simply put your text before the $ sign, starting with |.

Find other country formats here. (Wikipedia)

Upvotes: 0

Koen Rijnsent
Koen Rijnsent

Reputation: 41

What about this, has the formatting issue solved and carries out the "97 check":

Public Function VALIDATEIBAN(ByVal IBAN As String) As String

' Created by : Koen Rijnsent (www.castoro.nl)
' Inspired by : Chris Fannin (AbbydonKrafts)
' Inspired by : bonsvr (http://stackoverflow.com/users/872583/bonsvr)

On Error GoTo CatchError

Dim objRegExp As Object
Dim IBANformat As Boolean
Dim IBANNR As String
Dim ReplaceChr As String
Dim ReplaceBy As String

'Check format
IBAN = UCase(IBAN)
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[a-zA-Z]{2}[0-9]{2}[a-zA-Z0-9]{4}[0-9]{7}([a-zA-Z0-9]?){0,16}"
IBANformat = objRegExp.Test(IBAN)

'Validity of country code will not be checked!
If IBANformat = False Then
    VALIDATEIBAN = "FORMAT NOT RECOGNIZED"
Else
    'Flip first 4 characters to the back
    IBANNR = Right(IBAN, Len(IBAN) - 4) & Left(IBAN, 4)

    'Replace letters by the right numbers
    For Nr = 10 To 35
        ReplaceChr = Chr(Nr + 55)
        ReplaceBy = Trim(Str(Nr))
        IBANNR = Replace(IBANNR, ReplaceChr, ReplaceBy)
    Next Nr

    'Loop through the IBAN, as it is too long to calculate at one go
    CurrPart = ""
    Answer = ""
    For CurrDigit = 1 To Len(IBANNR)
        CurrPart = CurrPart & Mid$(IBANNR, CurrDigit, 1)
        CurrNumber = CLng(CurrPart)
        'If the number can be divided
        If 97 <= CurrNumber Then
            LeftOver = CurrNumber Mod 97
            WorkValue = (CurrNumber - LeftOver) / 97
            Answer = Answer & CStr(WorkValue)
            CurrPart = CStr(LeftOver)
        Else
            'If no division occurred, add a trailing zero
            If Len(Answer) > 0 Then
                Answer = Answer & "0"
                'Exception for the last number
                If CurrDigit = Len(IBANNR) Then
                    LeftOver = CurrNumber Mod 97
                Else
                End If
            Else
            End If
        End If
    Next
    If LeftOver = 1 Then
        VALIDATEIBAN = "IBAN OK"
    Else
        VALIDATEIBAN = "97 CHECK FAILED"
    End If
End If

Exit Function

CatchError:
    VALIDATEIBAN = "ERROR: " & Err.Description
    MsgBox "Module: " & MODULE_NAME & " - VALIDATEIBAN function" & vbCrLf & vbCrLf _
    & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Function

Upvotes: 4

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

It's easy, just use the below function.

'' Validate IBAN
Public Function VALIDATEIBAN(ByVal IBAN As string) As Boolean
On Error GoTo Catch

Dim objRegExp As Object
Dim blnIsValidIBAN As Boolean

Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "^[a-zA-Z]{2}\d{2}[ ]\d{4}[ ]\d{4}[ ]\d{4}[ ]\d{4}[ ]\d{4}|CZ\d{22}$"



blnIsValidIBAN = objRegExp.Test(IBAN)
VALIDATEIBAN = blnIsValidIBAN

Exit Function

Catch:
VALIDATEIBAN = False
MsgBox "Module: " & MODULE_NAME & " - VALIDATEIBAN function" & vbCrLf & vbCrLf _
    & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

How to use:

Copy the code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.

Insert -> Module.
Paste code.
Save and Exit VBE.

Run the function:

Now you have a user defined function in Excel, just like the built in SUM, AVG functions. Let's say you want to validate IBAN in cell A1, just write in any cell =VALIDATEIBAN(A1). It will return TRUE or FALSE.

Besides, it will apply to both:

ES65 0800 0000 1920 0014 5399

and

ES6508000000192000145399

But NOT: 

ES65-0800-0000-1920-0014-5399

Upvotes: -3

Related Questions