Viewing Workbook Statistics

Hi Allen,

I wanted to check out the total editing time in Excel 2016 and after I followed the steps you've mentioned in your post it showed me 0 (zero) minutes. It seems that Microsoft left this option out somehow in newer versions. Maybe it's missing some file in registry editor. Could that be possible? Is there still some way to figure out the total editing time nevertheless?

Thank you in advance,

Best regards,

Sven

Beth's Ctrl ~ keyboard shortcut is indeed a piece in the puzzle of finding references to other workbooks where you suspect problems. Other possibilities that you may need to explore are to:
- Use the Edit Links box (on Excel 2007 this is on the Data Ribbon, in the Connections section) to see a list of all the workbooks that this workbook is linked to
- Use Find (Ctrl F) to search for all cells (possibly across all the Worksheets/tabs in this Workbook) that contain the filename of the troublesome external workbook (chances are very good indeed that they'll be formulae and links to that external file); click on the Find All button
- Sort by Formula by clicking on the word Formula on the top of the Find and Replace results.

I find that by following these 3 steps, it's a lot easier to figure out what aspect of the formulae is presenting the problem.

Note that you can also fix some problems related to links to external Workbooks by replacing the formulae containing those references with their corresponding values. This is done with the Break Link button in the Edit Links box. One might wish this function to do the least it can do - for instance, if you have a formula =[other workbook]Sheet1!A1 * 10 and the referenced cell has the value 15, the formula value is of course 150. You might wish in this case that when you Break Link to the other workbook, the formula would be changed to =15 * 10. But that's not what Excel does - it replaces the formula entirely with the corresponding "naked" value 150. This is one of the reasons that most people don't use Break Link except in the most rare of circumstances. But those might be the very circumstances in which Deborah found herself.

To view all formulas - Ctrl ~
Use the same to convert back to text.

I have the same issue as Sheldon. In addition, I also get an 'available resources" error message, and then Excel crashes. I searched and found a link to another person's workbook in a formula. I fixed this and it helped, but apparently it was just part of the issue. Is there anyway to find/view all the formulas in a workbook, so I can review for errors/unnecessary links? Thanks in advance for any advice you can provide.

Example that i pRomised yesterday.

Sub statistics()
' VarType constants
'vbEmpty 0 Empty (uninitialized)
'vbNull 1 Null (no valid data)
'vbInteger 2 Integer
'vbLong 3 Long integer
'vbSingle 4 Single-precision floating-point number
'vbDouble 5 Double-precision floating-point number
'vbCurrency 6 Currency value
'vbDate 7 Date value
'vbString 8 String
'vbObject 9 Object
'vbError 10 Error value
'vbBoolean 11 Boolean value
'vbVariant 12 Variant (used only with arrays of variants)
'vbDataObject 13 Data access object
'vbDecimal 14 Decimal value
'vbByte 17 Byte value
'vbUserDefinedType 36 Variants that contain user-defined types
'vbArray 8192 Array

Dim dateCell, usedCell, formulaCell, numericCell, stringCell As Integer
Dim aCell As Range

For Each aCell In ActiveSheet.UsedRange
With aCell
If Not aCell = Empty Then usedCell = usedCell + 1
If IsDate(.Text) Then dateCell = dateCell + 1
If .HasFormula Then formulaCell = formulaCell + 1
If VarType(.Value) = vbString Then stringCell = stringCell + 1
If (VarType(.Value) = vbDouble) _
Or (VarType(.Value) = vbInteger) _
Or (VarType(.Value) = vbLong) _
Or (VarType(.Value) = vbSingle) _
Or (VarType(.Value) = vbDecimal) _
Or (VarType(.Value) = vbLong) _
Then
numericCell = numericCell + 1
End If
End With
Next aCell

MsgBox "Used cells = " & usedCell & vbLf & _
"String cells = " & stringCell & vbLf & _
"Formula cells = " & formulaCell & vbLf & _
"Numeric (Double etc) cells = " & numericCell & vbLf & _
"Date cells = " & dateCell

End Sub

VBA can be used to count such things like:
Number of used cells;
cells with specific formatting; cells with formulas;
etc.

I'll post an example when i get home tomorrow.

Unfortunately the 'statistics' aren't really useful. You can see most of them right clicking the filename in the "file explorer"

I've been running into operational failures with MSO2007 thru MSO2016, announced by a (paraphrased) somethings wrong with the file. Clicking Edit Anyway doesn't always let the file "run".
I have summerized that I have exceeded the maximum allowable formatted cells. I'm sure Excel keeps a number of file statistics that could help us "KNOW" exactly what the "something wrong" is.
I'd like to be able to access these statistics.