QTP: IBAN validation in VBScript

IBAN stands for International Bank Account Number and is the old new toy of the banking community. Also hot in Europe because of SEPA. IBAN should make life easier, and maybe it does. For IT guys, IBAN is just another standard. And despite IT guys like standards (that is why they have so many of them), IBAN is a standard designed by the banking people making things a little more complicated.

The things you want to do with IBAN is validate it or just calculate the checksum on your own. The formula for the checksum is not very complex, but has some twists in it. For example, when dealing with Alpha characters, the A is transposed to 10, the B to 11 etc. In the IT world, we would transpose A to 65, B to 66… The things you don't want is validate them exactly right for for every country on this little planet. Maybe they want it, but definitely, you don't. And if they want it, get yourself a new toy called SOAP and connect to it through a service.

After searching the internet, I discovered that code for IBAN validation through any Visual Basic language was rare. I gathered the snippets I found useful and created my own IBAN functions.

How it works is all in the comments in the code, keeping your scripts maintainable and documented if you want to use it:

' This code was created by Bas M. Dam and first published on
' http://automated-chaos.blogspot.com
' You can use and distribute this code freely, as long as you
' keep this commentblock intact.

' RETRIEVING THE CHECKSUM
' There are two methods to get the checksum. The first is the
' one used in automated processes where there is an iban prototype.
' the checksum is replaced by zeros:
' MsgBox getIBANchecksum("LC00BANK1234567890", empty) 'returns 86

' The other way is a more user fiendly appraoch if only the separate
' segments are known like bank code or clearing house:
' MsgBox getIBANchecksum("BANK1234567890", "LC") 'returns 86

' CREATE AN IBAN NUMBER
' This is implemented in the makeIBAN() function for your convenience
' Msgbox makeIBAN("LC", "BANK", empty, "1234567890")    
    ' returns LC86BANK1234567890

' Or just the simple implementation:
' Msgbox makeIBAN("LCBANK1234567890", empty, empty, empty)
    ' returns LC86BANK1234567890

' CHECK AN IBAN NUMBER
' And finally, you want to check if something is IBAN. You can
' use the getIBANchecksum function for it. If the result is 97,
' then you have a real IBAN, when it returns -1, there is something
' wrong with the IBAN and if it returns another number, the checksum
' is not correct
' Msgbox getIBANchecksum("LC86BANK1234567890", empty) 'returns 97
' Msgbox getIBANchecksum("LC68BANK1234567890", empty) 'returns 18
' Msgbox getIBANchecksum("LC68BANK1234567891", empty) 'returns 88
' Msgbox getIBANchecksum("LC86BANK123456789%", empty) 'returns -1

' To do this the simple way, you can make use of the isIBAN() function
' that simply returns True or False:
' Msgbox isIBAN("LC86BANK1234567890") 'returns True
' Msgbox isIBAN("LC68BANK1234567890") 'returns False
' Msgbox isIBAN("LC86BANK123456789%") 'returns False


' SPECIAL CHARACTERS
' You can use typographical characters as stated in the skipChars string.
' For now, the following characters can be used: space.-_,/
' These characters are often used to make an IBAN more readible, but are
' not taken into the checksum calculation. between the landcode
' and checksum, never a typographical character can be used.
' Msgbox isIBAN("LC86 BANK 1234 5678 90") 'returns True
' Msgbox isIBAN("LC86BANK1234.56.78.90")  'returns True
' Msgbox isIBAN("LC-86-BANK-1234-567890") 'returns False, there can not
                    'be a separation char between
                    'landcode and checksum.
' Msgbox isIBAN("LC*86*BANK*1234*567890") 'returns False, * is not a special char

' Function to check on an IBAN
Public Function isIBAN(sIban)

  isIBAN = (getIBANchecksum(sIban, empty) = 97)
   
End Function

' Function to create an IBAN. Any of the arguments can be empty, as
' long as the first not empty argument starts with the landcode
Public function makeIBAN(landcode, bankcode, sortcode, accountnr)

    dim realLandcode, sPurged

    sPurged = mid(landcode & bankcode & sortcode & accountnr, 3)
    realLandcode = left(landcode & bankcode & sortcode & accountnr, 2)
    
    makeIBAN = realLandcode & getIBANchecksum(sPurged, realLandcode) & sPurged
   
End Function

' Function to get an IBAN checksum. Landcode can be empty, but then, the landcode
' must be included in the first two characters of sIban, followed by two zero's
Public Function getIBANchecksum(sIban, landcode)

  Dim sLCCS        'Land Code and Check Sum
  Dim sIbanMixed
  Dim sIbanDigits
  Dim char
  Dim i
  Dim skipChars
  skipChars = " .-_,/"
    
  ' Marginal length check
  If Len(sIban) < 5 Or Len(sIban) > 35 Then
    getIBANchecksum = -1
    Exit Function
  End If

  If landcode = empty Then
    sLCCS = Left(sIban, 4)     '   Extract land code and check sum
    sIbanMixed = Right(sIban, Len(sIban) - 4) & UCase(sLCCS)
  else
    sLCCS = landcode & "00"
    sIbanMixed = sIban & UCase(sLCCS)
  End If
   
    For i = 1 To Len(sIbanMixed)
        char = Mid(sIbanMixed, i, 1)

        'Check on digits
        If IsNumeric(char) Then         
           sIbanDigits = sIbanDigits & char    
    
        'Check on typographical characters
        elseif instr(skipChars, char) Then  
           'skip this character, but continue
    
        'Check on non-uppercase other characters
        elseif Asc(char) < 65 OR Asc(char) > 90 then
           getIBANchecksum = -1
           Exit function
   
        'Transform characters to digits
        else
           sIbanDigits = sIbanDigits & (Asc(char) - 55)
        End If
    Next

    getIBANchecksum = 98 - largeModulus(sIbanDigits, 97)
      
End Function

' Calculates the modulus of large integers that are actually
' strings. Also usefull for implementation in Excel VBA
' (there is a known bug in Excel and large number modulus)
Private Function largeModulus(sNumber, modulus)
    Dim i, sRebuild(), j, r
    
    j = 0
    sNumber = cStr(sNumber)
    
    For i = 1 To Len(sNumber) + 6 Step 6
        ReDim Preserve sRebuild(j)
        
        sRebuild(j) = Mid(sNumber, i, 6)
        j = j + 1
    Next

    r = sRebuild(0) Mod modulus
    
    For i = 0 To UBound(sRebuild) - 1
        r = (r & sRebuild(i + 1)) Mod modulus
    Next
    
    largeModulus = r
    
End Function

The knowledge about the IBAN validation and some code tricks I retrieved from the internet, so it is my turn to to give it back to the community. The functions are also useful in Excel VBA, but not extensively tested. The isIBAN() function is great to use it in your spreadsheet itself, or use it as conditional formatting: