CLABE Checksum. Determining position of values.

[Maik]

Hi there guys, hope you can help me . First of all, the CLABE number is just a banking standard like IBAN or Routing numbers. I've been trying to write a code which could lead to retrieving the whole Mexican CLABE number + control digit from just the first 17 digits. This is how the checksum works

The thing is I am not an expert, and I did this mainly by googling so I don't really know where is the mistake here. I think is just about I don't achieve to return the position number in order to obtain the weight factor so that would be the question.

Public Function okCLABE(CLABE As String) As String

    Dim Idx As Integer
    Dim ChkSum As Long
    Dim ChkVal As Integer
    Dim Wgt As Long
    Dim MyStr As String
    Dim MyChr As String
    
    For Idx = Len(CLABE) To 1 Step -1    
        [COLOR=#ff0000]MyChr = Mid$(CLABE, Idx, 1)[/COLOR]
        If MyChr Mod 3 = 0 Then Wgt = Val(MyChr) * (3)
        
        If MyChr Mod 3 = 1 Then Wgt = Val(MyChr) * (7)
        
        If MyChr Mod 3 = 2 Then Wgt = Val(MyChr) * (1)
        
        ChkSum = ChkSum + Wgt
    Next Idx
    ChkVal = (10 - (ChkSum Mod 10))
 
    okCLABE = CLABE & Trim(Str(ChkVal))
End Function

Here it is a CLABE code to test: 03218000011835971 9=(Control digit)
Thanks in advance .

 

[shg]

How about just a formula?

      --------A-------- B
  1   03218000011835971 9

In B1:

=10 - MOD(SUMPRODUCT(MOD(MID("37137137137137137", ROW(INDIRECT("1:17")), 1) * MID(A1, ROW(INDIRECT("1:17")), 1), 10)), 10)

 

[Maik]

By the way, I didnt forgot the End If, I just forgot to copy it >_<.

 

[Maik]

How about just a formula?

      --------A-------- B
  1   03218000011835971 9

In B1, confirmed with Ctrl+Shift+Enter:

=10 - MOD(SUM(MOD(MID("37137137137137137", ROW(INDIRECT("1:17")), 1) * MID(A1, ROW(INDIRECT("1:17")), 1), 10)), 10)

You rock. I would like to achieve it by VBA so I can learn how to do it for other bank standards or control digit checksums but that is working great. So if nobody could help I will use that, many thanks

 

[shg]

Probably still not what you want, but this returns the check digit for a 17-digit string, and returns True or False for an 18-digit string:

Function CLABE(sInp As String) As Variant
    Dim sFrm As String

    Select Case Len(sInp)
        Case 17
            sFrm = "10 - MOD(SUMPRODUCT(MOD(" & _
                   "MID(""37137137137137137"",  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * " & _
                   "MID(""" & sInp & """,  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), 10)), 10)"
            CLABE = Evaluate(sFrm)
        Case 18
            CLABE = --Right(sInp, 1) = CLABE(Left(sInp, 17))
        Case Else
            CLABE = "Invalid length!"
    End Select
End Function

 

[shg]

For a non-volatile formula confirmed in the usual way,

=10 - MOD(SUMPRODUCT(MOD(MID("37137137137137137", {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), 10)), 10)

 

[Maik]

Probably still not what you want, but this returns the check digit for a 17-digit string, and returns True or False for an 18-digit string:

Function CLABE(sInp As String) As Variant
    Dim sFrm As String

    Select Case Len(sInp)
        Case 17
            sFrm = "10 - MOD(SUMPRODUCT(MOD(" & _
                   "MID(""37137137137137137"",  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * " & _
                   "MID(""" & sInp & """,  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), 10)), 10)"
            CLABE = Evaluate(sFrm)
        Case 18
            CLABE = --Right(sInp, 1) = CLABE(Left(sInp, 17))
        Case Else
            CLABE = "Invalid length!"
    End Select
End Function

As you said is not exactly what I want but it achieves the same objective, which is validating the CLABE.

I hope I will success by myself with other routing numbers such as ABA or BSB, thank you very much, and you are fast as hell .