On 18/11/2010 09:05, Filips Benoit wrote:

Why overflow-error on mod-function ???

Arguably a bug in Excel. Decimal support has always been pretty flaky.

Public Function CheckIBAN(ByVal strIBAN As String) As Boolean

'1. BE62510007547061
'2. 510007547061 BE62
'3. 510007547061111462
'4. De modulus 97 (remainder of div 97) 510007547061111462 /97 remainder =
1

[snip]
IBANtotalNum = CDec(strAllDigits): MsgBox "decimal = "& IBANtotalNum
iModulus = IBANtotalNum Mod 97

It still fails if you use IBANtotalNum Mod CDec(97) :(

I am afraid CDec in Excel is something of a half hearted bodge.

Mod converts them back to 64bit double precision reals and is just about
smart enough to notice that there are not enough mantissa digits to get
the answer right.

The following code should be OK
IBANdiv = IBANtotalNum / CDec(97)
iModulus = IBANtotalNum - Int(IBANdiv) * 97

I suspect the division here is still flaky but it only has to get the
right number of multiples of 97 to subtract. Multiply and subtract
appear to honour the variant record type declaration as CDec correctly.

You may need to add additional protective code to avoid similar overflow
problems if the IBAN numbers can get much bigger.

Hope this helps.

Regards,
Martin Brown