Is there a VBA to calculate the key of a bank account?

Question

I want to Calculate the Key Of a current bank Account (CCP Algeria); using a VBA in excel; I’ve Managed to do it in excel using the following formula:

=TEXTE(SI(((MOD(97-((89*(7)+15*(99999)+3*E6));97))+30)>97;(MOD(97-((89*(7)+15*(99999)+3*E6));97))+30-97;(MOD(97-((89*(7)+15*(99999)+3*E6));97))+30);"00")

But in VBA, when the Account is eight digits, for example ‘24378883’ I got an error message. Also for an account: (1593132) that the key should be ‘97’ , is gives me ‘00’.

The Code in VBA I used is:

Public Function rip_ccp(y)

 x1 = y * 100#

  X = x1 Mod 97

 ' MOD CCP MOD 99999000000000000= 85=w

  w = 85

  If (X + w) > 97 Then c = (X + w) - 97 Else c = (X + w)

  d = 97 - c

  If d < 10 Then k = "0" & d Else k = d

 rip_ccp = k

 End Function

For a Belgian account number, you just put the numbers next to each other and calculate mod 97, why does it look so difficult in Algeria? — Dominique, May 16 '20 at 20:50
I don't know, perhaps each country has its methods of calculations — snollygoster, May 16 '20 at 21:07

Naresh · Accepted Answer · 2020-05-16T20:40:13.777

1

Try the following; your formula translated into VBA function

Function rip_ccp(y)
a = 97 - (89 * 7 + 15 * 99999 + 3 * y)
b = (a - 97 * Int(a / 97)) + 30

If b > 97 Then
    rip_ccp = Format(b - 97, "00")
    Else
    rip_ccp = Format(b, "00")
End If

End Function

edited May 16 '20 at 20:40

answered May 16 '20 at 20:24

Naresh

2,984
2
9
15

refer vba mod function [here](https://stackoverflow.com/a/50394381/9808063) – Naresh May 16 '20 at 20:44

Is there a VBA to calculate the key of a bank account?

1 Answers1