On Thu, 23 Sep 2010 17:23:00 +0200, "Charlotte E" wrote:

Is is possible to create an UDF, which validates an IBAN number accoring to
this:

http://en.wikipedia.org/wiki/Interna...ing_the_I BAN

TIA,


Yes you can.

The algorithm is fairly straight forward.

You need a list of valid ICAO two letter country codes (that use the IBAN
system) to set up a lookup table which includes the length of the IBAN for that
country. I don't have time to do that part, but the valid lengths and
countries are in the article you cite, and the ICAO codes should be somewhere
on the Internet.

Once you've got that set up, do a lookup using the first two characters of the
IBAN and get the proper length. Then remove all the spaces and check that the
length is valid. If either the length is invalid or the country code is
non-existent, then the test fails.

The next step is to rearrange the code as described, do the two-digit for
letter substitution and then do the mod97 computation.

The only problem is the mod97 computation.

VBA precision, even using the CDec data type, is not sufficient to include the
possible lengths of all possible IBAN numbers which, at present, can go up to
32 (and that is without the two-digit for letter substitution).

I assumed a maximum digit length of 50, but it could be longer.

There are at least two possible solutions. The first is to use the table in
the article. But you will need to manually code that table as excel will not
be able to generate values greater than about 10^28

I chose to use the Xnumbers add-in which is a freely available (on the
Internet) add-in which allows up to 250 digit precision. You can search for
it. Once you install it, you can set a reference to it (under
Tools/References) and use it directly in your VBA routine.

Here is an example that seems to work for the UK. You can see in the code
where you'll need to add in the "lookup routine" to validate the country code
and obtain the proper length.

==================================================
'need to set reference to xnumbers
Option Explicit
Function IbanValidate(s As String) As Boolean
IbanValidate = False
Dim cc As String
Dim lenIBAN As Long
lenIBAN = 22 'good for UK only for testing
Dim n As String
Dim R As Long
Const DigitMax As Long = 50
'Step 1: Validate length per country
'Get country code
cc = Left(s, 2)
'do lookup in country code table and
'also obtain valid length of IBAN
'If lookup fails, then cc invalid
s = UCase(Replace(s, " ", ""))

If Len(s) < lenIBAN Then Exit Function

'Rearrange string
s = Mid(s, 5) & Left(s, 4)
'replace letters with numbers
For R = Len(s) To 1 Step -1
If Mid(s, R, 1) = "A" And _
Mid(s, R, 1) <= "Z" Then
s = Left(s, R - 1) & CStr(Asc(Mid(s, R, 1)) - 55) & Mid(s, R + 1)
End If
Next R

'Mod 97 check

n = xdiv(s, 97, DigitMax)
R = xmult(xsub(n, xInt(n), DigitMax), 97, DigitMax)

If R = 1 Then ValidateIBAN = True

End Function
===================================
--ron