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 3 – DG 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]