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...
Please Login or Register to read the full article
The full article is available to registered AccountingWEB.co.uk 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.
Replies
Please login or register to join the discussion.
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
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.
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
Thanks for the prompt response.
I am using windows XP with Office 2010
Raymond
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
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 royccox...
I've made those changes as per your suggestions.
Further input / feedback most welcome!
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.
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