Error! Not a valid filename.

Explore

Obtain lists of worksheets, styles and so on; a read-only pass to understand the debris.

Worksheets

Make a preliminary scan of the workbook.

ListWorksheets

What worksheets are visible?

Use the macro ListWorksheets to produce a count and list of all worksheets in the workbook.

In this case we have no hidden sheets.

HomeEachSheet

Use the macro HomeEachSheet to set the cell pointer to cell A1 in each worksheet.

ListStyles

Use the macro ListStyles to obtain a pop-up list of all styles defined in the workbook.

In most cases the list will be a straightforward as the example above, since few users have caught on to the glory of Styled formatting.

But every now and then you will stumble across an astute user.

Use the macros ListStylesBuiltin and ListStylesUser to produce sub-sets of the main list.

DumpStyles

Use the macro DumpStyles to d

CAUTION: Although this macro is listed as “exploratory” it will make a change to the current workbook.

I generally open a now worksheet immediately before running the macro DumpStyles.

Tap the F9 function key to recalculate cells as a cheap way of checking that the styled formatting suffices for most purposes.

Use the macros DumpStylesBuiltin and DumpStylesUser to produce sub-sets of the main list.

Errors

FindErrors

Your proposal stipulates that workbooks will be error-free, or that errors will be fixed at hourly rates, right?

Use the macro FindErrors to identify the first error in a workbook.

If no errors are found, the workbook appears undisturbed.

When an error is found, the macro jumps to the offending cell and terminates.

Range Names

ListAllRangeNames

Use the macro ListAllRangeNames to produce a pop-up list of all named ranges in the workbook.

ListUnusedRangeNamesInWorksheet

Use the macro ListUnusedRangeNamesInWorksheet to produce both a pop-up list of unused range names AND place a copy of the list in the clipboard.

ListUnusedRangeNamesInWorkSHEET

Use the macro ListUnusedRangeNamesInWorkSHEET to obtain a pop-up list of all range names in the workbook which are not referenced in the currently active worksheet.

ListUnusedRangeNamesInWorkBOOK

Use the macro ListUnusedRangeNamesInWorkBOOK to obtain a pop-up list of all range names in the workbook which are not referenced in the currently active worksheet.

DisplayIllegalReferences

Use the macro DisplayIllegalReferences to obtain a pop-up list of all range names in the workbook which are based on non-existent spreadsheet cells. Such range names will appear with #REF in the Insert, Names, define dialog.

See also the companion macro DeleteIllegalReferences.

WhatIsMyRangeName

Use the macro WhatIsMyRangeName to determine what ranges are defined on the first cell of a selection.

In the example above, we have selected cell B1 of the sheet Tables in the workbook Example012.

The macro WhatIsMyRangeName tells us that two ranges are define don this cell.

Multiple range names for a range are not evil, but they do suggest an element of confusion.

In particular when we are reading excel formulas, we usually expect that each range name refers to a unique cell, and that “Arena” and “Gymnasium” might be referring to different places – both in the workbook and in the real world..

WorkbookFindAll

Use the macro WorkbookFindAll to locate all occurrences of a string, and select the last found occurrence.

This macro can be used effectively to scour a workbook for occurrences of a range name.

Search