Excel 2007 calculation bug test code

Here is a rough couple of VBA procs to test for any Excel 2007 calculation bug problems where your spreadsheet may be showing 100,000 when it really means 65,535. (eg 850*77.1)

The code looks at formula cells to see if the displayed value is the same as the underlying val (truncated to longs) if not it warns.

Its as rough as, only tested very basically, don’t sue me if its rubbish style. Feel free to post back with any improvements. This just punts possible probs to the immediate window.

(note alt T M M works in 2k7 to list and run macros)

Cheers

Simon

EDIT – link to .txt added at bottom

Edit2 – this isn’t going to work if you have special number formatting that presents the numbers different from the underlying value – like rounded thousands for example (Thanks Ed). Try saving a temp ‘test only version’ and remove the formatting then don’t save. Ugly I know, Anyone got better code?

EDIT 3DG has an explanation of the issue and what they are doing about it on the Excel Blog.

[have I got enough disclaimers??]

Option Explicit

Sub findCalcBlunder()
‘// courtesy of http://www.codematic.net – no warranties blah blah…
‘// feel free to modify adapt and improve – post better code at smurfonspreadsheets.co.uk
‘// checks displayed no == cell value.
‘// rushed and flaky – likely to fail and or be slow on big sheets
Dim rFormulas As Range
Dim clTest As Range
Dim ws As Worksheet
Dim lBlundercount As Long
Dim bHash As Boolean
Dim sText As String

On Error GoTo err_h
Application.Calculation = xlCalculationManual

For Each ws In ActiveWorkbook.Worksheets
Set rFormulas = safewrapFormulas2(ws) ‘limit of 8000 ranges?
If Not rFormulas Is Nothing Then
For Each clTest In rFormulas.Cells
sText = clTest.Text
If InStr(1, sText, “#”, vbTextCompare) = 0 Then
If IsNumeric(sText) Then
If CLng(sText) <> CLng(clTest.Value) Then
lBlundercount = lBlundercount + 1
Debug.Print “sht: ” & ws.Name, _
“cell: ” & clTest.Address, _
“formula: ” & clTest.Formula, _
“text val: ” & sText, _
“real val: ” & clTest.Value2
Else
‘leave it
End If
Else
‘nan leave it
End If
Else
bHash = True ‘ col width
End If
Next clTest
Else
‘blank sheet
End If
Next ws

If lBlundercount > 0 Then
MsgBox lBlundercount & ” potential errors were found” & vbCrLf & _
“Check the immediate window for more info”, vbExclamation
Else
MsgBox “no obvious errors were found this time”, vbExclamation
End If

If bHash Then
MsgBox “Some cells could not be checked as the values were not displayed” & vbCrLf & _
“This is probably due to some columns being so narrow they display ‘###’”, vbExclamation
Else
‘no hash probs spotted
End If

Exit_proc:
Application.Calculation = xlCalculationAutomatic
Exit Sub

err_h:
MsgBox “Error ” & Err.Number & vbCrLf & _
” (” & Err.Description & “) ” & vbCrLf & “in findCalcBlunder”
Resume Exit_proc

End Sub

Private Function safewrapFormulas2(s As Worksheet) As Range
On Error GoTo err_h
Set safewrapFormulas2 = s.Cells.SpecialCells(xlCellTypeFormulas)
exit_here:
Exit Function
err_h:
Set safewrapFormulas2 = Nothing
End Function

[Edit - text file here as WP seems to have mangled the code a bit]

About these ads

7 Responses to “Excel 2007 calculation bug test code”

  1. Rob Bruce Says:

    Do you always leave empty (save for comments) else clauses like that, Simon? Maybe – no, probably – it’s only a matter of style, but i’d /never/ do that.

  2. Simon Says:

    Yep I always put an else, and just a comment if I can’t find something better to do.

  3. Dana DeLouis Says:

    Hi. Not necessary, but just an idea. When a user checks his sheet, you are changing his calculation state. You may want to return it in the same condition as you found it.
    Here’s a general idea that I use:

    Sub Demo()
    Dim Remember As Long

    ‘// Remember the Calculation State
    Remember = Application.Calculation
    ‘// Set to Manual
    Application.Calculation = xlCalculationManual

    ‘// Do main program

    ‘// Return to previous Calculation State
    Application.Calculation = Remember
    End Sub

    Again, just an idea… :>)

  4. Simon Says:

    Dana Yeah that was pretty lazy of me.
    I have a proper state library for all that stuff usually – this was just a quick and dirty to get something out there.
    I saved what? 10 seconds? 2 lines?
    I’ll maybe do a neater version (I’m keeping the else’s though Rob) after people have had chance to break it.
    thanks for the suggestion
    cheers
    Simon

  5. Jon Peltier Says:

    Simon – “note alt T M M works in 2k7 to list and run macros”

    So does Alt+F8.

    Rob – “Do you always leave empty else clauses”

    I always leave Case Else even if it’s empty, and I often leave the empty Else in the if/then statement. They remind me that I’ve considered the Else condition, even if I do nothing specific under that condition.

    Dana -

    I do this all the time with all manner of settings. Especially ScreenUpdating, so a sub called from another sub doesn’t turn it back on at the end, just back to the way it was. This prevents lots of flashing that ScreenUpdating = False was intended to prevent.

  6. Excel 2007 bug shows wrong answers to simple multiplications « Getting IT Right Says:

    [...] Murphy has published some code which you can use to test your files for occurrences of the bug. He also discusses how important this bug is overall, in terms of actual [...]

  7. Bad spreadsheet « Smurf on Spreadsheets Says:

    [...] *Of course I can really – its the Excel 2007 calculation presentation bug – I coded a test proc here [...]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: