1. I need to be using a checksum to validate IBAN numbers as they are exported to a csv file ready for sending.
However, the number ends up at 28 digits long (for UK IBAN size).

The checksum is to have a mod 97 remainder of 1
Typically this could be: 3214282912345698765432161182 mod 97 = 1

I am having difficulty trying to incorporate this in Excel.
Each time I use the value (in sheet) it is being truncated to 3.21428E+27
Using in VBA I get 3.2142829123457E+27

TIA
Alan
Cheshire
cloudy, 14C, showers

2. Excel uses 15 significant digits for calculations - it cannot handle numbers with 28 digits without losing a lot of information.
You'd have to store the IBAN numbers as text and program the calculations yourself, or use an add-in such as xlPrecision.

3. [quote name='HansV' post='780975' date='22-Jun-2009 12:26']Excel uses 15 significant digits for calculations - it cannot handle numbers with 28 digits without losing a lot of information.
You'd have to store the IBAN numbers as text and program the calculations yourself, or use an add-in such as xlPrecision.[/quote]

Many thanks for that Hans.
Until now it hasn't been a problem as we use independent app for IBAN

Using the cell value have managed to run checksum using good ol'fashioned long division iteration.
myCheck = 97
mylen = Len(ActiveCell.Value)
For i = 1 To mylen
myrem = (myrem & Mid(ActiveCell.Value, i, 1)) Mod myCheck
Next i

'use myRem to post any non 1 errors

4. Hi Alan

In addition to what Hans states even if you could get it to work the MOD function returns an error if the divisor multiplied by 134,217,728 is less than or equal to the number being evaluated.

It looks like your routine is probably the easiest at the moment

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•