excelexcel-2007excel-2003vba

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?

Solution

  • 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