Türkçe

C# IBAN Checker/Verifier

For one of my projects I needed an IBAN verifier. I searched the web for check digit calculation and found it in IBAN Wikipedia entry. I also found a very good javascript example on the UN CEFACT TBG5 web site. As I needed this algorithm in C#, I developed a C# version from information presented in these sites. Using same code I also created a custom Excel worksheet function as Automation Add-in and a SmartTag for Word using VSTO.

(for those who don't want to read all the details here is the source code in C#)

IBAN checksum calculation

It is described as follows in Wikipedia entry:

The checksum is a basic ISO 7064 mod 97-10 calculation where the remainder must equal 1.
To validate the checksum:
  1. Check that the total IBAN length is correct as per the country. If not, the IBAN is invalid.
  2. Move the four initial characters to the end of the string.
  3. Replace each letter in the string with two digits, thereby expanding the string, where A=10, B=11, ..., Z=35.
  4. Interpret the string as a decimal integer and compute the remainder of that number on division by 97.
The IBAN number can only be valid if the remainder is 1.

When you apply this algorithm in C# it is something like this (I admit that this may not be the best algorithm).
C# IBAN Algorithm

The javascript presented in UN CEFACT TBG5 web site contains all the available country specific data that are listed in Wikipedia in an array. I decided to hold this data as an internal List<IbanData>. As I would like to give more information other than true-false as a return value to my IbanChecker I also created status data class as well.
IbanData classStatusData class

You can download full source code here -> IbanLibrary.zip.


IBAN Automation Add-in for Microsoft Excel

I thought it could be very helpful if this checker can be used in Excel worksheets. I could easily create a VBA solution using VBScript's regular expression support (Set regex = CreateObject("VBScript.RegExp"). However, I want to create a solution that can be deployed to user computers in an organization. Microsoft's suggested way of developing UDF for client computers is XLL, which you have to develop a special kind of DLL using the C API. If you would like to develop it for Excel Services (for SharePoint) there is already a library that you can use for your managed code (Microsoft.Office.Excel.Server.Udf).

I decided to develop an Automation Add-in for Excel where I can use my managed code as COM interop assembly.
Excel Automation Add-in

After registering this managed DLL as COM interop assemly you need to activate it in Excel. For this: Office Button -> Excel Options -> Add-Ins -> “Manage: Excel Add-in” Go… -> Automation… -> ExcelExtension.ExcelFunctions -> OK -> OK

Then you can use it from "Insert Function" dialog box.

For more details on developing Automation Add-in you can start with these two blog post by Eric Carter and Gabhan Berry.
For registering managed code DLL as COM interop assembly you can start with Regasm.exe and this article (and this tip). And you should have already know this if you plan to do a company wide deployment.

You can download full source code here -> IbanAutomationAddIn.zip. Please see below for regex examples


IBAN SmartTag for Microsoft Word

Most of the correspondences are written in Word in companies. It would be nice to have a SmartTag that notify you if the IBAN code that you write in a letter is correct or not. For developing a SmartTag the easiest way is to use Visual Studio Tools for Office.
Iban SmartTag code

This is a sample in Word.
Iban SmartTag code

For developing SmartTag for Word you can read this how-to.
You can download full source code here -> IbanSmartTag.zip.


Using Regular Expression worksheet functions in IBAN Automation Add-in

When working with Excel, especially if it contains text and numbers that I need to manipulate, I always felt the absence of regular expressions that I can use as worksheet function. After developing above Excel Add-in I added additional functions for mostly used regex functions: IsMatch, Match().Value and all Match().Value.
Regex functions

Sample results:
Regex IsMatch Regex Match
Regex All Matchs

As you can see RegexAllMatchValues function returns a string array. In order to see all the results you need to apply Array Formula for the enough cells that can hold your results. You can find many sample regex pattern at RegExLib.com

 

please send any improvement suggestions to: