Checksum tool: A simple routine to cure Excel hell

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...

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Excellent

RAYC | | Permalink

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

FD4Cast's picture

Hi Raymond,

FD4Cast | | Permalink

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

Versions

RAWC | | Permalink

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

Raymond

FD4Cast's picture

Hi Raymond,

FD4Cast | | Permalink

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

code improvement

royccox | | Permalink

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

       

saltimbamba | | Permalink

       

FD4Cast's picture

Thanks royccox...

FD4Cast | | Permalink

I've made those changes as per your suggestions.

Further input / feedback most welcome!

FD4Cast's picture

RAYC Issue solved...

FD4Cast | | Permalink

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.

Try this amended code. 

royccox | | Permalink

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.

I have converted the code to    1 thanks

royccox | | Permalink

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