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,

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

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,


Actually, the valid two letter country codes can be deduced from the IBAN
fields listed in the article you cite, so it should be relatively simple for
you to set up a lookup table.
--ron

On 9/23/2010 3:15 PM, Ron Rosenfeld wrote:
On Thu, 23 Sep 2010 17:23:00 +0200, "Charlotte 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,


Actually, the valid two letter country codes can be deduced from the IBAN
fields listed in the article you cite, so it should be relatively simple for
you to set up a lookup table.
--ron

If you create what looks like a free acc't there's a file here you may
be able to use:

http://www.utteraccess.com/forum/IBA...ode=linearplus

I needed a function to validate IBAN numbers but couldn't find one.
Came across some difficulties but found help on UtterAccess (Brent
Spaulding (UtterAccess: datAdrenaline) )
So here it is for anyone that may need an IBAN validator.
I also created a couple of icons to show near the IBAN textbox.
A check for a Valid IBAN and a Cross for an Invalid IBAN.
Enclosed in attachment:
* 2007 format
* 2000 format (only tested on Access 2007)
* Valid and Invalid Icons in png format.
Attached File Valid IBAN.zip ( 58.56K ) Number of downloads: 174