IBAN validation on sheet

[dolyschaf]

I have been trying to emulate the Validating IBAN number using mod 97 directly on a worksheet here: Wikipedia - Validating an IBAN using mod97

All is fine until I need to "Compute remainder" i.e. change to 3214282912345698765432161182 to a number and use with MOD.

How do I get a text string that long to appear as a number in the cell or am I looking at this all the wrong way? Is this at all possible without VBA ?

Any help appreciated!

 

[ShawnPCooke]

From that Wikipedia page: "Any programming language or software package that is used to compute D mod 97 directly must have the ability to handle integers of more than 30 digits." Sadly, Excel does not. A Long integer in Excel tops out at 1,048,576.

So we are left with the piece-wise calculation suggested on the same page. That is doable, though without VBA it will be tremendously cumbersome.

Can you go the VBA route? If so, it's a reasonably straightforward program, which I can help with.

 

[ShawnPCooke]

And here is that program (thought it might be fun to write, and it was):

Public Function BigMod(ByVal strNumber As String) As Long

    'This function accepts as large integer as a string, and calculates
    'the mod according to the given base.  It then returns the remainder.
    'Errors result in a return value of -1
    
    
    'Declarations
    Dim strSub As String
    
    Dim i As Long
    Dim lngCount As Long
    Dim lngLoopCount As Long
    Dim lngSubMod As Long
    Dim lngModulo As Long
    
    Dim boolError As Boolean
    
    'Initialization
    lngCount = Len(strNumber)
    lngModulo = 97
    boolError = False
    
    'Checking for illegal characters in string
    For i = 1 To lngCount
        
        'Examine each character
        strSub = Mid(strNumber, i, 1)
        
        'If it's not a digit, set to error and exit loop
        If Not strSub Like "#" Then
            boolError = True
            Exit For
        End If
        
    Next i
    
    'If there was an error value, return -1 and exit the function
    If boolError Then
        BigMod = -1
        Exit Function
    End If
    
    'Now, check to see if we even need this algorithm.  We'll say
    'if it's nine digits or less, we don't need to bother
    If lngCount <= 9 Then
        BigMod = CLng(strNumber) Mod lngModulo
    Else
    
        'Otherwise, let's see how many times we need to loop through this.
        lngLoopCount = Application.WorksheetFunction.Ceiling((lngCount - 9) / 7, 1) + 1
        
        'Do the first iteration of the loop
        strSub = Left(strNumber, 9)
        lngSubMod = CLng(strSub) Mod lngModulo
        
        For i = 2 To lngLoopCount
        
            'Take the ith 7 characters
            strSub = Mid(strNumber, 3 + (i - 1) * 7, 7)
        
            'Append the previous to the beginning
            strSub = Format(lngSubMod, "00") & strSub
        
            'Take the modulo of the result
            lngSubMod = CLng(strSub) Mod lngModulo

        Next i
        
        'Once we've iterated all the way through, the last result
        'is our answer
        BigMod = lngSubMod
        
    End If

End Function

I don't know this algorithm well enough to know if it's extensible to other modulo values than 97, but now I want to find out.