Director FD4Cast Ltd
Share this content
Tags:

Checksum tool: A simple routine to cure Excel hell

11th Oct 2013
Director FD4Cast Ltd
Share this content
Kashflow logo

In a previous article, I wrote about how a simple checksum structure can greatly improve the auditability and robustness of financial models, and indeed Excel workbooks in general.

The focus of much current spreadsheet research centres on methods to reduce and identify errors. While checksums will not guarantee an error-free workbook, they will certainly perform a very useful ‘triage’ role for spreadsheet authors.

Checksums are particularly effective for large spreadsheets containing multiple worksheets because they alert users to real-time errors – so if a change to a single input cell produces an error elsewhere in the workbook this will be highlighted immediately.

As a result, checksums are especially useful during the construction phase of financial models, as well as for monitoring of workbook integrity when inputs and assumptions are changed later by users.

I was pleased to see readers liked the idea of using checksums in their Excel workbooks, but the feedback suggested that many accountants simply didn’t have the time to build a checksum structure into each of their workbooks.

This article sets out to respond to that issue by offering an automated way to create checksum structures within Excel workbooks that will also help avoid implementation errors.

The solution is based on some VBA code that gives users the ability to add a basic checksums structure and audit sheet (like the one in the screenshot below) to any Excel workbook or financial model of their choice - typically in less than 60 seconds per workbook.

Here’s how to do it:

  1.   Open the VBA Editor of the workbook you are using (Alt + F11)
  2.   Insert a new module into the VBA project like in the screenshot below

  1.   Copy and paste the code from this text file into the new module you have just added
  1.   Run the subroutine called sChecksumsSheetInsert by highlighting the title of the macro and clicking on the green Play button on the VBA Editor toolbar (under the option Debug as in the screenshot below).

  1.   You will have now created the basic Checksums structure for every worksheet within the workbook. Time taken should have been around 30–60 seconds.

You will be alerted to any errors in the workbook by a conditionally formatted flag in Cell A1 of every sheet that turns red as soon as an error is detected in real-time on any worksheet within the workbook.

There is also a hyperlink for each worksheet that allows quick navigation to the worksheet with the error in question.

These are checksums in their simplest format. But you can also add more advanced custom checksums in the appropriate row of column F of the Checksums sheet, as indicated in the screenshot above.

You could, for example, create multiple checksums for a single sheet, perhaps referencing various important ranges rather than whole sheet ranges. This will help to pinpoint errors much more quickly and effectively.  Furthermore, you can include any kind of formula such as those to identify mistakes or to aid reconciliations. An example of this could be:-

“=IF(SUM(RANGE1)<>SUM(RANGE2),FALSE,TRUE)”.

Finally, if your workbook is in a format such as ‘.xlsx’ – which means it cannot contain VBA code – then you will need to remove the VBA module with the Checksums code before saving your workbook with the newly created Checksums structure and audit sheet.

Alternatively you can leave the module in and simply save your workbook in a macro-enabled or binary format (e.g. ‘.xlsm’ or ‘.xlsb’).

James Power is the principal and development director of FD4CAST. If you need any help in implementing the Checksums code you can contact him via email at james[AT]FD4Cast.com.

Tags:

Replies (10)

Please login or register to join the discussion.

avatar
By RAYC
18th Oct 2013 20:34

Excellent

James

Having gone through the above post it looks excellent and certainly something I could use quite a bit. However I am having problems adding it to spreadsheets already created and even a blank new spreadsheet. The macro 'hangs' at the  "Do While Range("Audit_Start").Offset(intIndexNumber - 1).Formula <> ""

Any assistance to get past this stage would be appreciated.

 

Raymond

Thanks (0)
Replying to stevo5678:
James Power profile image
By James Power
22nd Oct 2013 09:38

RAYC Issue solved...

Just to let other readers know that RAYC's issue has been solved. The code needs to be run from a module for the workbook in question that you want to create checksums for, not from a module inside your Personal.xlsb.

Thanks (0)
James Power profile image
By James Power
18th Oct 2013 21:27

Hi Raymond,

Not sure why you are getting this error.

If you are having issues with adding to even blank workbooks then I suspect it will probably be a Windows/Office configuration issue.

Could you possibly let me know what version of Windows and MS Office you are using so that I can try to troubleshoot further?

Rgds,

James

Thanks (0)
avatar
By RAWC
18th Oct 2013 22:00

Versions
Thanks for the prompt response.
I am using windows XP with Office 2010

Raymond

Thanks (0)
James Power profile image
By James Power
18th Oct 2013 22:39

Hi Raymond,

You have exactly the same PC spec as me, so I am really struggling to see why you would get the issue.

I am happy to do a screen-share session with you in order to try and troubleshoot the issue for you and other readers who might potentially have the same problem.

Why don't you email me via the FD4Cast website so that we can arrange a time to troubleshoot?

Rgds,

James

Thanks (0)
avatar
By royccox
19th Oct 2013 10:49

code improvement

The code could be speeded up by not selecting ranges & sheets.

You assume calculation is automatic & at the end of the code set it to automatic. It's much better to get the setting & restore it to the user's setting.

 

Also an error handler is needed because if the code errors before resetting the Application then the user's original settings must be restored. 

 

Sub sChecksumsSheetInsert()     Dim intChecksumRow As Integer, intIndexNumber As Integer, intCalc As Integer    Dim lngNumberOfRows As Long    Dim sht As Worksheet, shtAuditSheet As Worksheet    Dim rng As Range    On Error GoTo exit_proc    With Application        .ScreenUpdating = False        'get current calculation setting        intCalc = .Calculation        MsgBox intCalc        .Calculation = xlManual        .EnableCancelKey = xlDisabled        .DisplayAlerts = False 'rest of code  MsgBox ("Checksums Sheet Created!")exit_proc:        .ScreenUpdating = True        .Calculation = intCalc        .EnableCancelKey = xlErrorHandler        .DisplayAlerts = True    End WithEnd Sub

Thanks (0)
avatar
By Farkhem Hall
19th Oct 2013 16:36

       

       

Thanks (0)
James Power profile image
By James Power
19th Oct 2013 16:38

Thanks royccox...

I've made those changes as per your suggestions.

Further input / feedback most welcome!

Thanks (0)
avatar
By royccox
23rd Oct 2013 07:41

Try this amended code. 

Try this amended code. 

 

It successfully works on any activeworkbook so can be saved as an addin or placed in the Personal workbook

 

CheckSum Code

 

I have created an addin version that will work in versions of Excel from 2003. It adds a button to the Tools menu in 2003 or a new tab in the Ribbon of Excel 2007 onwards.

 

I'll make it available when I have finished testing it.

Thanks (0)
avatar
By royccox
05th Nov 2013 08:16

I have converted the code to an Excel addin so that once installed you can use it any open WorkBook.  

 

The download contains two versions of the addin, one for versions prior to Excel 2007 and one that works with the later versions of Excel.Installation is slightly different for each version but the Read me file contains links to explain how to install them.

 

Download

Thanks (1)