Dear all,

Why overflow-error on mod-function ???


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

On Error GoTo Errhandling

Dim strTemp As String
Dim strAllDigits As String
Dim strToken As String
Dim iLoop As Integer
Dim IBANtotalNum As Double
Dim iModulus As Integer

CheckIBAN = False

strTemp = Replace(strIBAN, " ", "")
strTemp = right$(strTemp, Len(strTemp) - 4) & Left$(strTemp, 4): MsgBox
strTemp
For iLoop = 1 To Len(strTemp)
strToken = UCase(Mid$(strTemp, iLoop, 1))
If Not IsNumeric(strToken) Then
If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", strToken) = 0 Then
Exit For
Else
strToken = CStr(Asc(strToken) - 55)
End If
End If
strAllDigits = strAllDigits & strToken
Next iLoop

MsgBox strAllDigits & " len=" & Len(strAllDigits)

IBANtotalNum = CDec(strAllDigits): MsgBox "decimal = " & IBANtotalNum
iModulus = IBANtotalNum Mod 97
If iModulus = 1 Then CheckIBAN = True

Exit Function

Errhandling:
MsgBox err.Number & " " & err.Description
End Function

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

On Thu, 18 Nov 2010 10:39:23 +0000, Martin Brown
wrote:

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

IBAN numbers can be large enough to overflow the CDec logic. At
present, they can go up to 32 digits plus a two letter country code.
Since the validation algorithm calls for substituting two digits for
each of the letters, we are at 36 digits, well outside VBA's level of
precision even with CDec. And they could grow longer.

There is a free Excel add-in: xnumbers.xla which can handle extended
precision, and a few months ago I proposed a solution which used this
and also required coding a lookup table so as to get the appropriate
specification for the appropriate country.

Thanks,

Sorry, I posted it on an Excel-newsgroup but it should work in Access. ( The
response is much faster in Excel-newsgroups)
My solution now is limited to Belgium ( stays inside VBA's level ) and the
user gets a msg that there is no check on foreign bank nummers.
For the present Company that is no problem.
I have now 3 functions to check IBAN and Check Bic individualy and one to
find the Bic from the IBAN in a table (BicFromPrefix) I downloaded from the
national Bank Belgium !

Filip
-------------------------------------------------------------------------------------------
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

On Error GoTo Errhandling

Dim strTemp As String
Dim strAllDigits As String
Dim strToken As String
Dim iLoop As Integer
Dim vDecTotalNum As Variant
Dim iModulus As Long

CheckIBAN = False

strTemp = Replace(strIban, " ", "")
strTemp = right$(strTemp, Len(strTemp) - 4) & Left$(strTemp, 4)
For iLoop = 1 To Len(strTemp)
strToken = UCase(Mid$(strTemp, iLoop, 1))
If Not IsNumeric(strToken) Then
If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", strToken) = 0 Then
Exit For
Else
strToken = CStr(Asc(strToken) - 55)
End If
End If
strAllDigits = strAllDigits & strToken
Next iLoop

vDecTotalNum = CDec(strAllDigits) / 97
iModulus = (vDecTotalNum - Int(vDecTotalNum)) * 97
If iModulus = 1 Then CheckIBAN = True

Exit Function

Errhandling:
MsgBox err.Number & " " & err.Description
End Function

----------------------------------------------------

Public Function CheckBicBelgium(ByVal strBic As String, ByVal strIban As
String) As Boolean
On Error GoTo Errhandling

Dim strTemp As String
Dim strBankPrefixNum As String
Dim strBicFromList As String

CheckBicBelgium = False
strTemp = Replace(strIban, " ", "")
strBankPrefixNum = Mid$(strTemp, 5, 3)
strBicFromList = DLookup("Biccode", "BicFromPrefix",
"((BicFromPrefix.T_Identification_Number)='" & strBankPrefixNum & "')")
MsgBox strBankPrefixNum & " " & strBicFromList
If strBic = Replace(strBicFromList, " ", "") Then CheckBicBelgium = True

Exit Function

Errhandling:
MsgBox err.Number & " " & err.Description
End Function
---------------------------------------------------------------------------
Public Function FindBicBelgium(ByVal strIban As String) As String
On Error GoTo Errhandling

Dim strTemp As String
Dim strBankPrefixNum As String

strTemp = Replace(strIban, " ", "")
strBankPrefixNum = Mid$(strTemp, 5, 3)
FindBicBelgium = DLookup("Biccode", "BicFromPrefix",
"((BicFromPrefix.T_Identification_Number)='" & strBankPrefixNum & "')")
FindBicBelgium = Replace(FindBicBelgium, " ", "")
Exit Function

Errhandling:
MsgBox err.Number & " " & err.Description
End Function
-----------------------------------------------------------------


"Ron Rosenfeld" wrote in message
...
On Thu, 18 Nov 2010 10:39:23 +0000, Martin Brown
wrote:

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

IBAN numbers can be large enough to overflow the CDec logic. At
present, they can go up to 32 digits plus a two letter country code.
Since the validation algorithm calls for substituting two digits for
each of the letters, we are at 36 digits, well outside VBA's level of
precision even with CDec. And they could grow longer.

There is a free Excel add-in: xnumbers.xla which can handle extended
precision, and a few months ago I proposed a solution which used this
and also required coding a lookup table so as to get the appropriate
specification for the appropriate country.

On Thu, 18 Nov 2010 14:48:01 +0100, "Filips Benoit"
wrote:

Thanks,

Sorry, I posted it on an Excel-newsgroup but it should work in Access. ( The
response is much faster in Excel-newsgroups)
My solution now is limited to Belgium ( stays inside VBA's level ) and the
user gets a msg that there is no check on foreign bank nummers.
For the present Company that is no problem.
I have now 3 functions to check IBAN and Check Bic individualy and one to
find the Bic from the IBAN in a table (BicFromPrefix) I downloaded from the
national Bank Belgium !

Filip

It sounds like you are all set then.

Yes, all run fine, thanks², Filip

"Ron Rosenfeld" wrote in message
...
On Thu, 18 Nov 2010 14:48:01 +0100, "Filips Benoit"
wrote:

Thanks,

Sorry, I posted it on an Excel-newsgroup but it should work in Access. (
The
response is much faster in Excel-newsgroups)
My solution now is limited to Belgium ( stays inside VBA's level ) and the
user gets a msg that there is no check on foreign bank nummers.
For the present Company that is no problem.
I have now 3 functions to check IBAN and Check Bic individualy and one to
find the Bic from the IBAN in a table (BicFromPrefix) I downloaded from
the
national Bank Belgium !

Filip

It sounds like you are all set then.