Authors
Patrick O'Beirne
Abstract
Refactoring is a change made to the internal structure of software to make it easier to understand and cheaper to modify without changing its observable behaviour.
A database refactoring is a small change to the database schema which improves its design without changing its semantics.
The paper shall present example 'spreadsheet refactorings', derived from the above and taking into account the unique characteristics of spreadsheet formulas and VBA code. The techniques are constrained by the tightly coupled data and code in spreadsheets.
Sample
Suggested refactorings.
For worksheets:
- Impose style conventions.
- Follow industry conventions.
- Make it easy to see non-obvious content.
- Duplicated code.
- Overlong formula.
- Simplify formulas.
- Move magic number to a cell.
- Data clumps.
- Shotgun surgery.
- Data envy.
- Keeping regular.
- Convert multiple relative references to absolute.
- Ways to suppress #DIV/0!.
- Access external linked data safely.
- Use the right kind of LOOKUP.
- Check for legacy issues.
For data:
- Clean-up.
- Blank or missing?
For VBA code:
- Extract method.
- Comments.
- Primitive obsession.
- Replace array with object.
- Replace magic number with symbolic constant.
- Consolidate conditional expression.
- Remove control flag.
- Use guard clauses.
- Replace nested conditional with guard clauses.
- Introduce assertion.
- Replace error code with exception.
- Replace exception with test.
- VBE Tools-Options.
- Speed optimisation.
Publication
2010, EuSpRIG
