Modulo 11 checksum verification in VBA

Modulo 11 is a method to calculate checksums or verification digits to numeric data. Its used extensively throughout the world in cases such as bankaccount numbers, creditcard numbers, ISBN numbers and the like. The basic idea is not as much encryption or something similar, but more a protection against human errors. By calculating and verifying the checksum you can quickly detect cases like changing a single digit, switching digits, missing a digit, etc. By verifying a number as early as possible, you can decrease the amount of rubish entering a business process, and hence ease operations.

[ad name=”468×60 Banner”]
For a project (that never went live) I created a class that uses the modulo-11 algorithm to verify incoming numbers, to calculate checksum for outgoing numbers and to search free format text for valid numbers. The intent was to use it with Outlook and to scan incoming emails for valid numbers in the subject and messagebody, and if found, direct the messages to the correct department.

Example use

  • Invoice number; load text descriptions of your bankaccount transactions and search them for numbers that can be verified.
  • Serial numbers of equipment
  • Customeraccount numbers

Case example

An invoice number ‘2008/24/11-023’ could be extended with a verification number and end up looking like this: ‘2008/24/11-023-43’.

How it works

For every 8 characters input there will be 1 character verification. Make sure to use enough numbers in your numbering scheme, this enhances correctness. Minimum of 6 characters is recommended. Characters that are allowed inbetween the numbers can be provided, eg ‘/’ (slash) or a ‘-‘ (dash). These will be ignored when calculating the checksums, but be sure to set them properly for the searching function to work correctly.

The ‘weight’ factors can be set in the ‘VerifyNumber’ property (see comments in the code on how to use it). The calculations are based on the the ‘VerifyNumber’, so a different ‘VerifyNumber’ result in different checksums, hence its important to use the same ‘VerifyNumber’ when creating and checking. The advantage is that a different ‘VerifyNumber’ can be used for a similiar but different series of numbers; eg. product serial numbers for 2 products. Each product has its own ‘VerifyNumber’, but they use the same algorithm.

The code and download

The code consists of a single class and is used to create, verify and search for reference numbers that include a verification. Its contained in the example workbook. The example workbook contains a macro called ‘Test’ which shows how to use it. The output will be directed to the VBA editor immediate window (output is below).

Download it here (downloaded 703 times). Beware: The code has seen some testing, but was never used in production. Let me know if you run into any issues.

[ad name=”468×60 Banner”]

Here’s the output of the example:

[code lang=”xml”]
Loading ‘1972/05/26-‘ in the DataOnly property results in the following:
  DataOnly property       : 1972/05/26-
  DataComplete property   : 1972/05/26-44
  IsValid property        : True
  CheckCalculated property: 44
  CheckFound property     :  (empty because nothing was found, is was only calculated)

Loading ‘2001/12/05-51’ in the DataComplete property results in the following:
  DataOnly property       : 2001/12/05-
  DataComplete property   : 2001/12/05-51
  IsValid property        : False   (the found and calculated verifications don’t match!)
  CheckCalculated property: 42
  CheckFound property     : 51

Now searching through text ‘hello world 1972/05/26-44 here we are again 2008/24/11-023-43 what will this deliver?’
FIND results in a valid number found at position: 13
  DataOnly property       : 1972/05/26-
  DataComplete property   : 1972/05/26-44
  IsValid property        : True
  CheckCalculated property: 44
  CheckFound property     : 44
FINDNEXT results in a valid number found at position: 45
  DataOnly property       : 2008/24/11-023-
  DataComplete property   : 2008/24/11-023-43
  IsValid property        : True
  CheckCalculated property: 43
  CheckFound property     : 43
FINDNEXT results in: 0
  So there are no more valid numbers in the text being searched


Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *


Subscribe without commenting