Introduction
For one of my projects, I needed an IBAN (International Bank Account Number) verifier. I searched the web for check digit calculation, and found it in the IBAN Wikipedia entry. I also found a very good JavaScript example on the UN CEFACT TBG5 website. As I needed this algorithm in C#, I developed a C# version from the information presented in these sites. Using the same code, I also created a custom Excel worksheet function as an Automation Add-in and a SmartTag for Word using VSTO.
Background
IBAN is described as follows in its Wikipedia entry:
The checksum is a basic ISO 7064 mod 97-10 calculation where the remainder must equal 1. To validate the checksum:
- Check that the total IBAN length is correct as per the country. If not, the IBAN is invalid.
- Move the four initial characters to the end of the string.
- Replace each letter in the string with two digits, thereby expanding the string, where A=10, B=11, ..., Z=35.
- 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.
Using the Code
When you apply this algorithm in C#, it is something like this (I admit that this may not be the best algorithm):
internal static class Iban
{
public static StatusData CheckIban(string iban, bool cleanText)
{
if (cleanText)
iban = Regex.Replace(iban, @"\s", "").ToUpper();
if (Regex.IsMatch(iban, @"\W"))
return new StatusData(false, "The IBAN contains illegal characters.");
if (!Regex.IsMatch(iban, @"^\D\D\d\d.+"))
return new StatusData(false, "The structure of IBAN is wrong.");
if (Regex.IsMatch(iban, @"^\D\D00.+|^\D\D01.+|^\D\D99.+"))
return new StatusData(false, "The check digits of IBAN are wrong.");
string countryCode = iban.Substring(0, 2);
IbanData currentIbanData = (from id in IBANList()
where id.CountryCode == countryCode
select id).FirstOrDefault();
if (currentIbanData == null)
return new StatusData(false,
string.Format("IBAN for country {0} currently is not avaliable.",
countryCode));
if (iban.Length != currentIbanData.Lenght)
return new StatusData(false,
string.Format("The IBAN of {0} needs to be {1} characters long.",
countryCode, currentIbanData.Lenght));
if (!Regex.IsMatch(iban.Remove(0, 4), currentIbanData.RegexStructure))
return new StatusData(false,
"The country specific structure of IBAN is wrong.");
string modifiedIban = iban.ToUpper().Substring(4) + iban.Substring(0, 4);
modifiedIban = Regex.Replace(modifiedIban, @"\D",
m => ((int)m.Value[0] - 55).ToString());
int remainer = 0;
while (modifiedIban.Length >= 7)
{
remainer = int.Parse(remainer + modifiedIban.Substring(0, 7)) % 97;
modifiedIban = modifiedIban.Substring(7);
}
remainer = int.Parse(remainer + modifiedIban) % 97;
if (remainer != 1)
return new StatusData(false, "The IBAN is incorrect.");
return new StatusData(true, "The IBAN seems to be correct.");
}
public static List<IbanData> IBANList()
{
List<IbanData> newList = new List<IbanData>();
newList.Add(new IbanData("AD", 24,
@"\d{8}[a-zA-Z0-9]{12}", false,
"AD1200012030200359100100"));
newList.Add(new IbanData("TR", 26,
@"\d{5}[a-zA-Z0-9]{17}", false,
"TR330006100519786457841326"));
return newList;
}
}
The JavaScript presented in the UN CEFACT TBG5 website contains all the available country specific data that is 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 a status data class.
public class IbanData
{
public string CountryCode;
public int Lenght;
public string RegexStructure;
public bool IsEU924;
public string Sample;
}
public class StatusData
{
public bool IsValid;
public string Message;
}
I thought it would 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 wanted 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 as 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 a COM interop assembly.
public interface IExcelFunctions
{
string CheckIban(string iban);
bool RegexIsMatch(string cellValue, string regexPattern);
string RegexMatchValue(string cellValue, string regexPattern);
string[,] RegexAllMatchValues(string cellValue, string regexPattern);
}
[ComVisible(true), Guid("3....")]
[ComDefaultInterface(typeof(IExcelFunctions))]
public class ExcelFunctions : IExcelFunctions
{
public ExcelFunctions()
{
}
public string CheckIban(string iban)
{
StatusData status = Iban.CheckIban(iban, false);
return status.Message;
}
#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("", System.Environment.SystemDirectory +
@"\mscoree.dll", RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type,
"Programmable"), false);
}
private static string GetSubKeyName(Type type, string subKeyName)
{
System.Text.StringBuilder s = new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
#endregion
}
After registering this managed DLL as a COM interop assembly, 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 the "Insert Function" dialog box.
For more details on developing an Automation Add-in, you can start with the two blog posts by Eric Carter and Gabhan Berry. For registering a managed code DLL as a COM interop assembly, you can start with Regasm.exe and this article (and this tip). And, you should already know this if you plan to do a company wide deployment. Please see below for regex examples.
Most of the correspondences are written in Word in companies. It would be nice to have a SmartTag that notifies 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.
public class IbanTag : SmartTag
{
private ResourceManager rm;
public IbanTag()
: base("http://iban.saltug.net/iban#IbanTag", "IBAN")
{
Microsoft.Office.Tools.Word.Action ibanAction =
new Microsoft.Office.Tools.Word.Action("Validate");
this.Expressions.Add(new System.Text.RegularExpressions.Regex(
@"(?'iban'\D\D\d\d\S+)"));
ibanAction.BeforeCaptionShow +=
new BeforeCaptionShowEventHandler(ibanAction_BeforeCaptionShow);
this.Actions = new Microsoft.Office.Tools.Word.Action[] { ibanAction };
rm = new ResourceManager("IbanSmartTag.string",
System.Reflection.Assembly.GetExecutingAssembly());
}
private void ibanAction_BeforeCaptionShow(object sender, ActionEventArgs e)
{
string iban = e.Properties.get_Read("iban");
CultureInfo ci = new CultureInfo(e.Range.LanguageID.GetHashCode());
IbanStatusData status = IbanChecker.CheckIban(iban, true, ci);
((Microsoft.Office.Tools.Word.Action)sender).Caption = status.IsValid
? rm.GetString("IbanIsvalid", ci)
: rm.GetString("IbanIsNotValid", ci) +
"-> " + status.Message;
}
}
This is a sample in Word.
For developing SmartTag for Word, you can read this how-to.
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 a worksheet function. After developing the above Excel add-in, I added additional functions for the most commonly used regex functions: IsMatch, Match().Value, and all Match().Value.
#region Regex Functions
public bool RegexIsMatch(string cellValue, string regexPattern)
{
return Regex.IsMatch(cellValue, regexPattern);
}
public string RegexMatchValue(string cellValue, string regexPattern)
{
return Regex.Match(cellValue, regexPattern).Value;
}
public string[,] RegexAllMatchValues(string cellValue, string regexPattern)
{
List<string /> resultList = new List<string />();
Match matchResult = Regex.Match(cellValue, regexPattern);
while (matchResult.Success)
{
resultList.Add(matchResult.Value);
matchResult = matchResult.NextMatch();
}
string[,] resultValues = new string[resultList.Count, 1];
for (int i = 0; i < resultList.Count; i++)
resultValues[i, 0] = resultList[i];
return resultValues;
}
#endregion
Sample results:
As you can see, the RegexAllMatchValues function returns a string array. In order to see all the results, you need to apply the Array Formula for cells that can hold your results. You can find many sample regex patterns at RegExLib.com.
