אקסל | דרושה נוסחה לבדיקת תקינות מס חשבון בנק

[מטעמים]

ראיתי שיש כזה דבר כאן באקסס הבנוי על קוד מאקרו.

האם יש לכם מידע על נוסחה באקסל כגון זה?
תודה

[OdedDvir]

היינו הך
זה כתוב ב-VBA, אז פשוט תעתיק את המודול מקובץ האקסס לתוך האקסל
ואז הפונקציות יהיו זמינות לך שם

[מטעמים]

א. הקוד ההוא מאוד ישן. לא עדכני לחוקים החדשים (שמשתנים כל הזמן...)

חוקיות מספרי חשבונות בנק.pdf

ב. ראיתי שמישהו רשם שם בפורום משפט שלא הבנתי את משמעותו:

ה"long" של אקסס (VBA) שונה משאר השפות.

ג. זה שרשור ישן מאוד שם (לפני 6 שנים) אני מאמין שיש עוד כמוני שאולי היו צריכים כזה דבר ומצאו\יצרו משהו יותר מתקדם ומותאם לאקסל. לכן אני שואל כאן.

[ארי]

מצאתי אצלי משהו, אבל נראה לי די ישן, אז כנראה לא מתאים למה שהתחדש.

Public Function fCheckBankAccountValidity(lngBankNumber As Long, _
                                          lngBranch As Long, _
                                          lngAccount As Long) As Boolean
  On Error GoTo Err_בדיקה
    
    fCheckBankAccountValidity = 0

    Dim lngRequiredAccountLength As Long
    Dim lngTotal As Long

    Dim strArrayAccount() As String
    Dim strArrayBranch() As String

    Dim strAccount As String
    Dim strBranch As String

    Select Case lngBankNumber
        Case 10, 13, 34
            lngRequiredAccountLength = 8
        Case 12, 4
            lngRequiredAccountLength = 6
        Case 11, 17
            lngRequiredAccountLength = 9
        Case 20
            lngRequiredAccountLength = 6
        Case 31, 52
            lngRequiredAccountLength = 9
        Case 9
            lngRequiredAccountLength = 9
        Case 22
            lngRequiredAccountLength = 9
        Case 46
            lngRequiredAccountLength = 9
        Case 14
            lngRequiredAccountLength = 9
        Case 54
            lngRequiredAccountLength = 9
    End Select
    strBranch = lngBranch
    strAccount = String(lngRequiredAccountLength - Len(CStr(lngAccount)), "0") & CStr(lngAccount)
'    strAccount = Right("0000000000" & CStr(lngAccount), lngRequiredAccountLength) ' This is an alternative to the previous line
    strAccount = StrConv(strAccount, vbUnicode)
    strArrayAccount = Split(strAccount, vbNullChar)

    If lngBankNumber = 20 And lngBranch > 400 Then    ' אם מספר הסניף בבנק מזרחי-טפחות גדול מ400
        strBranch = CStr(lngBranch - 400)    ' לפני החישובים יש להפחית 400
    End If
If lngBankNumber = 20 And lngBranch < 400 Then    ' אם מספר הסניף בבנק מזרחי-טפחות לא גדול מ400
        strBranch = CStr(lngBranch)    ' לפני החישובים אל תבצע כלום
    End If
    If lngBankNumber = 12 Or lngBankNumber = 4 Then
        strBranch = lngBranch
    End If

    If Len(strBranch) < 3 Then
        strBranch = String(3 - Len(strBranch), "0") & strBranch    ' מוסיף אפסים להשלים ל3 ספרות
    End If
     strBranch = StrConv(strBranch, vbUnicode)
     strArrayBranch = Split(strBranch, vbNullChar)

    Select Case lngBankNumber
        Case 10, 13, 34
    
        lngTotal = CLng(strArrayBranch(0)) * 10 + _
                   CLng(strArrayBranch(1)) * 9 + _
                   CLng(strArrayBranch(2)) * 8 + _
                   CLng(strArrayAccount(0)) * 7 + _
                   CLng(strArrayAccount(1)) * 6 + _
                   CLng(strArrayAccount(2)) * 5 + _
                   CLng(strArrayAccount(3)) * 4 + _
                   CLng(strArrayAccount(4)) * 3 + _
                   CLng(strArrayAccount(5)) * 2 + _
                   right(lngAccount, 2)
          Select Case right(lngTotal, 2)
              Case 90, 72, 70, 60, 20
             fCheckBankAccountValidity = 1
          End Select
        
        Case 12
            lngTotal = CLng(strArrayBranch(0)) * 9 + _
                       CLng(strArrayBranch(1)) * 8 + _
                       CLng(strArrayBranch(2)) * 7 + _
                       CLng(strArrayAccount(0)) * 6 + _
                       CLng(strArrayAccount(1)) * 5 + _
                       CLng(strArrayAccount(2)) * 4 + _
                       CLng(strArrayAccount(3)) * 3 + _
                       CLng(strArrayAccount(4)) * 2 + _
                       CLng(strArrayAccount(5)) * 1
            Select Case lngTotal Mod 11
                Case 0, 2, 4, 6
                    fCheckBankAccountValidity = 1
               
            Case Else
                    fCheckBankAccountValidity = 0
            End Select
            
           Case 4
            lngTotal = CLng(strArrayBranch(0)) * 9 + _
                       CLng(strArrayBranch(1)) * 8 + _
                       CLng(strArrayBranch(2)) * 7 + _
                       CLng(strArrayAccount(0)) * 6 + _
                       CLng(strArrayAccount(1)) * 5 + _
                       CLng(strArrayAccount(2)) * 4 + _
                       CLng(strArrayAccount(3)) * 3 + _
                       CLng(strArrayAccount(4)) * 2 + _
                       CLng(strArrayAccount(5)) * 1
            Select Case lngTotal Mod 11
                Case 0, 2
                    fCheckBankAccountValidity = 1
            Case Else
                    fCheckBankAccountValidity = 0
            End Select
        Case 11, 17
            lngTotal = CLng(strArrayAccount(0)) * 9 + _
                       CLng(strArrayAccount(1)) * 8 + _
                       CLng(strArrayAccount(2)) * 7 + _
                       CLng(strArrayAccount(3)) * 6 + _
                       CLng(strArrayAccount(4)) * 5 + _
                       CLng(strArrayAccount(5)) * 4 + _
                       CLng(strArrayAccount(6)) * 3 + _
                       CLng(strArrayAccount(7)) * 2 + _
                       CLng(strArrayAccount(8)) * 1
            Select Case lngTotal Mod 11
                Case 0, 2, 4
                    fCheckBankAccountValidity = 1
            End Select
        Case 20
            lngTotal = CLng(strArrayBranch(0)) * 9 + _
                       CLng(strArrayBranch(1)) * 8 + _
                       CLng(strArrayBranch(2)) * 7 + _
                       CLng(strArrayAccount(0)) * 6 + _
                       CLng(strArrayAccount(1)) * 5 + _
                       CLng(strArrayAccount(2)) * 4 + _
                       CLng(strArrayAccount(3)) * 3 + _
                       CLng(strArrayAccount(4)) * 2 + _
                       CLng(strArrayAccount(5)) * 1
            Select Case lngTotal Mod 11
                Case 0, 2, 4
                    fCheckBankAccountValidity = 1
            End Select
        Case 31, 52
            lngTotal = CLng(strArrayAccount(0)) * 9 + _
                       CLng(strArrayAccount(1)) * 8 + _
                       CLng(strArrayAccount(2)) * 7 + _
                       CLng(strArrayAccount(3)) * 6 + _
                       CLng(strArrayAccount(4)) * 5 + _
                       CLng(strArrayAccount(5)) * 4 + _
                       CLng(strArrayAccount(6)) * 3 + _
                       CLng(strArrayAccount(7)) * 2 + _
                       CLng(strArrayAccount(8)) * 1
            Select Case lngTotal Mod 11
                Case 0
                    fCheckBankAccountValidity = 1
                Case 6
                    fCheckBankAccountValidity = 1
                Case Else
                    lngTotal = CLng(strArrayAccount(3)) * 6 + _
                               CLng(strArrayAccount(4)) * 5 + _
                               CLng(strArrayAccount(5)) * 4 + _
                               CLng(strArrayAccount(6)) * 3 + _
                               CLng(strArrayAccount(7)) * 2 + _
                               CLng(strArrayAccount(8)) * 1
                    Select Case lngTotal Mod 11
                      Case 0
                    fCheckBankAccountValidity = 1
                      Case 6
                    fCheckBankAccountValidity = 1
                     End Select
             End Select
        Case 9
            lngTotal = CLng(strArrayAccount(0)) * 9 + _
                       CLng(strArrayAccount(1)) * 8 + _
                       CLng(strArrayAccount(2)) * 7 + _
                       CLng(strArrayAccount(3)) * 6 + _
                       CLng(strArrayAccount(4)) * 5 + _
                       CLng(strArrayAccount(5)) * 4 + _
                       CLng(strArrayAccount(6)) * 3 + _
                       CLng(strArrayAccount(7)) * 2 + _
                       CLng(strArrayAccount(8)) * 1
            If lngTotal Mod 10 = 0 Then
                fCheckBankAccountValidity = 1
            End If
        Case 54
                 fCheckBankAccountValidity = 1
       Case 22
            lngTotal = CLng(strArrayAccount(0)) * 3 + _
                       CLng(strArrayAccount(1)) * 2 + _
                       CLng(strArrayAccount(2)) * 7 + _
                       CLng(strArrayAccount(3)) * 6 + _
                       CLng(strArrayAccount(4)) * 5 + _
                       CLng(strArrayAccount(5)) * 4 + _
                       CLng(strArrayAccount(6)) * 3 + _
                       CLng(strArrayAccount(7)) * 2
            If 11 - (lngTotal Mod 11) = CLng(strArrayAccount(8)) Then
                fCheckBankAccountValidity = 1
            End If
        Case 46
            lngTotal = CLng(strArrayBranch(0)) * 9 + _
                       CLng(strArrayBranch(1)) * 8 + _
                       CLng(strArrayBranch(2)) * 7 + _
                       CLng(strArrayAccount(3)) * 6 + _
                       CLng(strArrayAccount(4)) * 5 + _
                       CLng(strArrayAccount(5)) * 4 + _
                       CLng(strArrayAccount(6)) * 3 + _
                       CLng(strArrayAccount(7)) * 2 + _
                       CLng(strArrayAccount(8)) * 1
              Select Case lngTotal Mod 11
                Case 0
                    fCheckBankAccountValidity = 1
                Case 2
                    Select Case strBranch
                        Case 154, 166, 178, 181, 183, 191, 192, 503, 505, 507, 515, 516, 527, 539
                            fCheckBankAccountValidity = 1
                    End Select
                Case Else
                    lngTotal = CLng(strArrayAccount(0)) * 9 + _
                               CLng(strArrayAccount(1)) * 8 + _
                               CLng(strArrayAccount(2)) * 7 + _
                               CLng(strArrayAccount(3)) * 6 + _
                               CLng(strArrayAccount(4)) * 5 + _
                               CLng(strArrayAccount(5)) * 4 + _
                               CLng(strArrayAccount(6)) * 3 + _
                               CLng(strArrayAccount(7)) * 2 + _
                               CLng(strArrayAccount(8)) * 1
                    If lngTotal Mod 11 = 0 Then
                        fCheckBankAccountValidity = 1
                    Else
                        lngTotal = CLng(strArrayAccount(3)) * 6 + _
                                   CLng(strArrayAccount(4)) * 5 + _
                                   CLng(strArrayAccount(5)) * 4 + _
                                   CLng(strArrayAccount(6)) * 3 + _
                                   CLng(strArrayAccount(7)) * 2 + _
                                   CLng(strArrayAccount(8)) * 1
                        If lngTotal Mod 11 = 0 Then
                            fCheckBankAccountValidity = 1
                        End If
                    End If
            End Select
        Case 14
            lngTotal = CLng(strArrayBranch(0)) * 9 + _
                       CLng(strArrayBranch(1)) * 8 + _
                       CLng(strArrayBranch(2)) * 7 + _
                       CLng(strArrayAccount(3)) * 6 + _
                       CLng(strArrayAccount(4)) * 5 + _
                       CLng(strArrayAccount(5)) * 4 + _
                       CLng(strArrayAccount(6)) * 3 + _
                       CLng(strArrayAccount(7)) * 2 + _
                       CLng(strArrayAccount(8)) * 1
            Select Case lngTotal Mod 11
                Case 0
                    fCheckBankAccountValidity = 1
                Case 2
                    Select Case strBranch
                        Case 347, 361, 362, 363, 365, 385
                            fCheckBankAccountValidity = 1
                    End Select
                Case 4
                    Select Case strBranch
                        Case 361, 362, 363
                            fCheckBankAccountValidity = 1
                    End Select
                Case Else
                    lngTotal = CLng(strArrayAccount(0)) * 9 + _
                               CLng(strArrayAccount(1)) * 8 + _
                               CLng(strArrayAccount(2)) * 7 + _
                               CLng(strArrayAccount(3)) * 6 + _
                               CLng(strArrayAccount(4)) * 5 + _
                               CLng(strArrayAccount(5)) * 4 + _
                               CLng(strArrayAccount(6)) * 3 + _
                               CLng(strArrayAccount(7)) * 2 + _
                               CLng(strArrayAccount(8)) * 1
                    If lngTotal Mod 11 = 0 Then
                        fCheckBankAccountValidity = 1
                    Else
                        lngTotal = CLng(strArrayAccount(3)) * 6 + _
                                   CLng(strArrayAccount(4)) * 5 + _
                                   CLng(strArrayAccount(5)) * 4 + _
                                   CLng(strArrayAccount(6)) * 3 + _
                                   CLng(strArrayAccount(7)) * 2 + _
                                   CLng(strArrayAccount(8)) * 1
                        If lngTotal Mod 11 = 0 Then
                            fCheckBankAccountValidity = 1
                        End If
                    End If
            End Select
    End Select

Exit_fCheckBankAccountValidity:
    Exit Function

Err_בדיקה:
    
     fCheckBankAccountValidity = 2
    Exit Function

End Function

Public Function fCheckBankAccountValidityTest()
Debug.Print fCheckBankAccountValidity("4", "129", "100477")
End Function

[OdedDvir]

די מהר מצאתי את הפרוייקט הזה בגיטהב, מלפני שנתיים, נראה לגיטימי: https://github.com/soryy708/il-bank-account-validator. היתה רק בעיה קטנה: הוא כתוב ב-JS.

אז בגלל שזה נראה לי דבר שימושי, או בגלל שהיה קשה לי להאמין שזה לא זמין לציבור, אולי בגלל השעה... בכל מקרה, נתתי ל ChatGPT לעשות את התרגום הראשוני ל-VB, כמובן שאח"כ הייתי צריך להוסיף ולתקן כמה דברים (חריגות אינדקסים במערכים, הוספת פונקציה במקום השמה ישירה למערך, החלפה של Integer ל-Long, ריפוד באפסים, ועוד כמה שטויות של בורות מלאכותית)
בסופו של דבר, בדקתי את הקוד מול כמה מספרי חשבונות בנק שמפורסמים ברשת (למשל כאן) וקיבלתי תוצאה תקינה.
אז אם אתם משתמשים בקוד, ראוי שתתנו כוכב בגיטהב לפרוייקט הנ"ל. (אין לי שום קשר אליו)
צריך לפתוח מודול חדש ולהדביק את הקוד הבא:

Option Explicit

Function ValidateAccount(bankNumber As Long, branchNumber As Long, accountNumber As Long) As Boolean
    ' Input validation:
    If Not IsNonNegativeLong(bankNumber) Then
        ValidateAccount = False
     Exit Function
    End If
    If Not IsNonNegativeLong(branchNumber) Then
        ValidateAccount = False
     Exit Function
    End If
    If Not IsNonNegativeLong(accountNumber) Then
        ValidateAccount = False
     Exit Function
    End If

    ' Supported banks
    Const YAHAV As Long = 4
    Const POST As Long = 9
    Const LEUMI As Long = 10
    Const DISCOUNT As Long = 11
    Const HAPOALIM As Long = 12
    Const IGUD As Long = 13
    Const OTSAR_AHAYAL As Long = 14
    Const MERCANTILE As Long = 17
    Const MIZRAHI_TEFAHOT As Long = 20
    Const CITIBANK As Long = 22
    Const BEINLEUMI As Long = 31
    Const ARAVEI_ISRAELI As Long = 34
    Const MASAD As Long = 46
    Const POALEI_AGUDAT_ISRAEL As Long = 52

    ' Adjust branch number For Mizrahi Tefahot
    If bankNumber = MIZRAHI_TEFAHOT Then
        If branchNumber > 400 Then
            branchNumber = branchNumber - 400
        End If
    End If

    Dim accountNumberDigits() As Long
    Dim branchNumberDigits() As Long

    accountNumberDigits = NumberDigitsToArr(CLng(accountNumber), 9)
    branchNumberDigits = NumberDigitsToArr(CLng(branchNumber), 3)

    ' Account number validation
    Dim sum As Long
    Dim remainder As Long

    Select Case bankNumber
     Case LEUMI, IGUD, ARAVEI_ISRAELI

        sum = ScalarProduct(GetSubset(accountNumberDigits, 8), ToIntArray("1,10,2,3,4,5,6,7"))
        sum = sum + ScalarProduct(GetSubset(branchNumberDigits, 4), ToIntArray("8, 9, 10"))
        remainder = sum Mod 100
        ValidateAccount = ArrIncludes(ToIntArray("90, 72, 70, 60, 20"), remainder)

     Case YAHAV, MIZRAHI_TEFAHOT, HAPOALIM
        sum = ScalarProduct(GetSubset(accountNumberDigits, 6), ToIntArray("1, 2, 3, 4, 5, 6"))
        sum = sum + ScalarProduct(GetSubset(branchNumberDigits, 4), ToIntArray("7, 8, 9"))
        remainder = sum Mod 11

        Select Case bankNumber
         Case YAHAV
            ValidateAccount = ArrIncludes(ToIntArray("0, 2"), remainder)
         Case MIZRAHI_TEFAHOT
            ValidateAccount = ArrIncludes(ToIntArray("0, 2, 4"), remainder)
         Case HAPOALIM
            ValidateAccount = ArrIncludes(ToIntArray("0, 2, 4, 6"), remainder)
        End Select

     Case DISCOUNT, MERCANTILE, BEINLEUMI, POALEI_AGUDAT_ISRAEL
        sum = ScalarProduct(GetSubset(accountNumberDigits, 9), ToIntArray("1, 2, 3, 4, 5, 6, 7, 8, 9"))
        remainder = sum Mod 11

        Select Case bankNumber
         Case DISCOUNT, MERCANTILE
            ValidateAccount = ArrIncludes(ToIntArray("0, 2, 4"), remainder)

         Case BEINLEUMI, POALEI_AGUDAT_ISRAEL
            If ArrIncludes(ToIntArray("0, 6"), remainder) Then
                ValidateAccount = True
            Else
                sum = ScalarProduct(GetSubset(accountNumberDigits, 6), ToIntArray("1, 2, 3, 4, 5, 6"))
                remainder = sum Mod 11
                ValidateAccount = ArrIncludes(ToIntArray("0, 6"), remainder)
            End If
        End Select

     Case POST
        sum = ScalarProduct(GetSubset(accountNumberDigits, 9), ToIntArray("1, 2, 3, 4, 5, 6, 7, 8, 9"))
        remainder = sum Mod 10
        ValidateAccount = remainder = 0

     Case 54
        ' Jerusalem
        ValidateAccount = True ' wtf?

     Case CITIBANK
        sum = ScalarProduct(GetSubset(accountNumberDigits, 8), ToIntArray("2, 3, 4, 5, 6, 7, 2, 3"))
        ValidateAccount = (11 - sum Mod 11) = accountNumberDigits(1)

     Case OTSAR_AHAYAL, MASAD
        sum = ScalarProduct(GetSubset(accountNumberDigits, 6), ToIntArray("1, 2, 3, 4, 5, 6"))
        sum = sum + ScalarProduct(GetSubset(branchNumberDigits, 4), ToIntArray("7, 8, 9"))
        remainder = sum Mod 11

        If remainder = 0 Then
            ValidateAccount = True
        End If

        If bankNumber = MASAD Then
            If remainder = 2 And ArrIncludes(ToIntArray("154, 166, 178, 181, 183, 191, 192, 503, 505, 507, 515, 516, 527, 539"), branchNumber) Then
                ValidateAccount = True
            End If

            sum = ScalarProduct(GetSubset(accountNumberDigits, 9), ToIntArray("1, 2, 3, 4, 5, 6, 7, 8, 9"))
            remainder = sum Mod 11

            If remainder = 0 Then
                ValidateAccount = True
            Else
                sum = ScalarProduct(GetSubset(accountNumberDigits, 6), ToIntArray("1, 2, 3, 4, 5, 6"))
                remainder = sum Mod 11
                ValidateAccount = remainder = 0
            End If
        End If

        If bankNumber = OTSAR_AHAYAL Then
            If ArrIncludes(ToIntArray("0, 2"), remainder) And ArrIncludes(ToIntArray("385, 384, 365, 347, 363, 362, 361"), branchNumber) Then
                ValidateAccount = True
            Elseif remainder = 4 And ArrIncludes(ToIntArray("363, 362, 361"), branchNumber) Then
                ValidateAccount = True
            Else
                sum = ScalarProduct(GetSubset(accountNumberDigits, 9), ToIntArray("1, 2, 3, 4, 5, 6, 7, 8, 9"))
                remainder = sum Mod 11
                If remainder = 0 Then
                    ValidateAccount = True
                Else
                    sum = ScalarProduct(GetSubset(accountNumberDigits, 6), ToIntArray("1, 2, 3, 4, 5, 6"))
                    remainder = sum Mod 11
                    ValidateAccount = remainder = 0
                End If
            End If
        End If

    End Select

End Function

Function ScalarProduct(arr1() As Long, arr2() As Long) As Long
    Dim product As Long
    Dim i As Long
    Dim maxIndex As Integer
    maxIndex = UBound(arr1)
    If maxIndex > UBound(arr2) Then maxIndex = UBound(arr2)

        For i = 1 To maxIndex
            product = product + arr1(i) * arr2(i)
        Next i
        ScalarProduct = product
End Function

Function ArrIncludes(arr() As Long, val As Long) As Boolean
    Dim i As Long
    If Not IsEmpty(arr) Then
        For i = LBound(arr) To UBound(arr)
            If arr(i) = val Then
                ArrIncludes = True
             Exit Function
            End If
        Next i
    End If
End Function

Function NumberDigitsToArr(num As Long, length As Long) As Long()
    Dim digitsArray() As Long
    ReDim digitsArray(1 To length)

    Dim zeroPadding As Integer
    zeroPadding = length - Len(CStr(num))

    Dim i As Long

    For i = 1 To zeroPadding
        digitsArray(i) = 0
    Next i
    For i = zeroPadding + 1 To length
        digitsArray(i) = num Mod 10
        num = Int(num / 10)
    Next i
    NumberDigitsToArr = digitsArray
End Function

Function IsNonNegativeLong(num As Variant) As Boolean
    If VarType(num) = vbLong And num >= 0 Then
        IsNonNegativeLong = True
    End If
End Function

Function GetSubset(arr() As Long, count As Long) As Long()
    Dim result() As Long
    ReDim result(1 To count)
    Dim i As Long
    For i = 1 To count
        If i <= UBound(arr) Then
            result(i) = arr(i)
        End If
    Next i
    GetSubset = result
End Function

Function ToIntArray(list As String) As Long()
    Dim temp() As String
    temp = Split(list, ",")

    Dim result() As Long
    ReDim result(1 To UBound(temp) + 1)

    Dim i As Integer

    For i = 0 To UBound(temp)
        result(i + 1) = CLng(temp(i))
    Next i
    ToIntArray = result
End Function