Error! Not a valid filename.

Introduction

Have you inherited a workbook with terrible structure? No range names being used? Hard to tell what is data and what is not?

STAIN will remove those unsightly stains.

No Kidding!

Here are a collection of macros and a set of notes to guide you through the process of reducing an inherited legacy Excel workbook to a clear and unambiguous functional sheet.

Last time I looked I had five videos for STAIN .

Quick Start

Install the latest version of Stain.XLS, load a copy of that awful workbook that landed on your desk this morning, then run the STAIN macros described here.

Cleaning up a Workbook

Where is everything and how does it hang together?

We aim to separate blocks of cells into regions, possibly in new sheets, so that we can truly isolate user-input data from reference data from output data, and can isolate calculations from data.

We like to assign range names to all cells referenced in calculations. To this end we have DefineNameByColumnRow and DefineNameByRowColumn which will seek out appropriate labels and fabricate a range name for a cell.

We need to merge multi-cell labels with MergeCellLabels.

We set alignment of all cells to General and adjust all cells for best-fit, so that we can see everything.

We look for all embedded literals within formulae “=B11*0.53” where the numeric literal “0.53” should be extracted.

We look for prime candidates for user functions such as the repeated expression “=(J7*J8)/(367*J9)” with adjusted references throughout the workbook.

Our work can be classified into broad activities which follow each other in sequence (although each individual macro can be used at any time).

Step-By-Step

After receiving the workbook(s), save them in a folder and mark each file read-only (right-click, Properties).

Make a copy of each file to a state amenable to versioning. For example rename “Braemar inputs rev 2.xls” to be “Brae_002.xls”. Subsequent generations will be 003, 004, 005 etc.

Numbers are free; reruns are expensive.

Staining

Stain formulae with one of the macros StainSelection, StainWorkSHEET or StainWorkBOOK .

This will set those cells holding a formula to a bright-yellow background pattern.

Save your work with a new generation name using the macro SaveAsNewVersion.

Worksheets – Text-Only

Delete any text-only sheets.

Look for worksheets with no formulae (not stained); examine their cells. If the sheet name is “Instructions” or similar and there are reams of text, delete the sheet – it hinders our navigation with the Ctrl-PgDown key.

Worksheets - Reference

Locate worksheets that hold numeric data but no Stains. These are most likely reference data.

Run the macros STAIN.DeleteBlankColumns and STAIN.DeleteBlankRows.

This will pack the reference data tightly in the top-left region of the worksheet.

Recalculate.

Use the macro Stain.FindErrors.

If errors (#REF) were introduced this is a sure sign that a formula made reference to an unloaded cell – an error in worksheet construction.

If the workbook remains error-free Save your work with a new generation name.

Worksheets – Calculation

Repeat the processes for worksheets holding calculations.

A #REF error indicates a problem.

Reload the workbook without saving changes and determine the cause of the #REF error.

Communicate this back to your sponsor.

Worksheets – Block Shuffling

Our objective in this phase is to render the entire worksheet into a two-column structure by manual labor.

Remember that the worksheet is valid; there are no circular references.

The worksheet can therefore be reduced to a column of text labels and a column of data and formulae, with the data at the top, and successive cells holding formulae that calculate values based on preceding cells, ending with the desired output result in the last cells.

Part of the manual process involves recognizing and deleting empty cells in column A, deleting the cells, shift cells left, to bring labels and data into line with columns A and B.

In a neat sheet, column A will consist of text labels; column B will hold null cells, data cells, and stained formulae.

The null cells indicate “section heading” text to the left.

The data cells represent user-input values.

The stained cells represent formulae, with the formulae on the last row indicating a desired result.

In some cases we find three cells arranged vertically as “label”, “explanation”, “value”; Use the macro Stain.MergeCellLabels to re-arrange these horizontally as “label”, “value” and “explanation”.

Where “label” and “explanation” appear in the same cell, we split them; our objective is to produce meaningful labels in column A in preparation for auto-generation of range names.

Styles

Create or import styles for obvious and basic formats:

The workbook STAIN arrives with a set of styles.

Use the Stain.ListStyles macro set to display the names of the supplied styles.

Use Format, Styles, Merge to copy styles into the active workbook.

Constants

Look for obvious constants in expressions.

“=+($B$13-$B$22)*5.87” is a giveaway.

Set up two cells at the top of the sheet

The prefix “Constant_” identifies it as a constant and the tail is our best-guess at the significance.

Use the macro FabNameLeft to define the range name.

Replace all occurrences of “*5.87” with “*Constant_BillingMultiplier” By selecting the “5.87” in the formula and then browsing 9F3 key) to the defined range name, or to the cell that holds the constant value.

User Functions

It is not too soon to identify potential user-defined functions.

STAIN provides a simple function “strFunction” and a driving macro Stain.StringFunction to generate a syntactically-correct user function from a selection of cells.

Developer Functions

Some of the function procedures will be of benefit in cleaning up cells.

For example Function strProperCase(ByVal strInput As String) As String can be used to clarify the text label as follows:

Tutorial

Look for videos on the items with entries in column 2.

Search