Skip to main content

ACBA Mapping - Introduction

 Overview

ACBA Mapping software package provides a methodology for logging the development of a Worksheet (or the whole Workbook) over time. There are three primary elements to the package – the maps of worksheets, the lists of worksheet/workbook characteristics and the development commentary/logs. This detail is held in a separate but connected workbook.

Each of these primary elements will be considered separately. This article considers the worksheet maps.

Worksheet Maps

ACBA Mapping delivers a view of your spreadsheet from a different perspective. In this view, colours map the according to the functionality of each cell. The detail of the cell, its specific formula or current value remain hidden. This means there is less to distract the reviewer keen to understand the spreadsheet’s overall structure and approach.

Geographic or road maps have keys and / or legends designed to illustrate the symbols and their meaning. Similarly, ACBA maps have a key to the mapping categories and two legends.

The key identifies the meaning of the map’s coloured cells.


The main legend shows where all the formulae of the various generic types can be found. Each green formula cell is given a reference which identifies which “generic” formula it contains. 

The second legend shows where all the named regions and validated cells are to be found.


Using the Map

Unlike a real map though, we cannot unfold it and spread it over a large desk (or floor). For a large spreadsheet, and some are very big indeed, you need a way of identifying where a particular formula generic type can be found. 

Formulae of a particular generic type can often be found in several locations on a spreadsheet. Accordingly, we have opted for a method that highlights, where these formulae can be found. The map below shows the locations of the generic formula =IF(COUNT(MyRef:MyRef)=0,1,0), which has been given the reference F2. These have been highlighted in turquoise in the map’s detail.



Named regions are identified by thick black borders. They are normally (but not exclusively) found in a single cell or a discrete range of cells. Each member of the names Legend has a hyperlink to the associated map location. Clicking the hyperlink will select the whole range within a black outline. The formula bar will reveal the name value of the range.

Large Spreadsheets

There are times when it is useful to have a view of the whole map. This is especially important for large complex spreadsheets.


When you highlight the Generating the "ACBA-Mapping" Add-Inwhole map region, the VIEW tab provides a ‘Zoom to Selection’ button. This allows a very quick over view of the shape of the spreadsheet. The named regions are helpful for identifying areas of particular importance. Note that the names on each original worksheet have been altered to prevent conflicts, especially when mapping the development of a worksheet over time. The naming legend however always provides a connection back to the original name.

Downloading ACBA Mapping

The ACBA-Mapping software is distributed as an Excel Add-In and can be downloaded from the link.
For those not familiar with handling Excel Add-Ins, instructions on what to expect and where to load your Add-Ins can be found at Generating the "ACBA-Mapping" Add-In.

Access to Help

There is a discussion group dedicated to providing a forum for help and information - Excel - Cell Mapping. I recommend you join the group.

Alternatively, you can email me directly from the Stephen Allen link within the form.

Acknowledgements

Experts in Excel have offered their advice and I am very grateful for this. My thanks go to Patrick O'Beirne (Systems Modelling), Jan Karel Pieterse (JKP Application Development Services) and Hans Hallebeek (HC & TS).

Spreadsheet Risks

Spreadsheet errors and inconsistencies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG) considers the problems from an academic and professional perspective. The following links provide access to the EuSpRIG website and discussion forum.


Comments

Post a Comment

Popular posts from this blog

Walberswick – June 2023

 Patrick and I chose the hottest week of the year so far to stay at Esme’s caravan in Walberswick. I spent the first few days on my own to check the workings of the caravan. It is old and showing signs of wear and tear, but the absence of curtains in large south facing window was a real irritation. Bryony saved the day by bringing our large picnic table cloth, which we were able to pin over the window for shade. That turned out to be vital given the weather. I claim that it was all the fault of COVID 19 that I am now over weight and unfit. I used these first few days to identify how unfit I was. The answer turned out to be “very”. The walking of the shingle beach from the camp site to the Blythe estuary was a nightmare. In previous years, I would get up early and walk for an hour or so before considering the activity for the day. This was based on a circular walk up the river path and over the heathland that surrounds Walberswick. Morning Walk This took rather longer than I had
Post a Comment
Read more

What is a Cell Map?

It is a simplified, colour coded version of an Excel Worksheet. The map shows the structure of the worksheet, its input cells, constants, primary formula and 'named' ranges. The map is designed for spreadsheet developers who work in isolation (i.e. not part of a team), but who, nevertheless, build complex spreadsheet systems. It highlights oddities and inconsistencies within a spreadsheet structure. Excel has its own built-in auditing and mapping tools. Excel's name manager and formula auditing tools These are useful for auditing one formula or one name, but they become overburdened with detail when trying to audit/review a whole worksheet. The ACBA Mapping   [A revised version of the software is now available. Stephen Allen 05 September 2023] utility generates a map of the original spreadsheet and three map keys to aid the interpretation of the detail. For example, I went on strict weight reducing diet last year where I measured the weight/volume of everythin
Post a Comment
Read more

ACBA Mapping: Employing ‘UsedRange’ in Excel

When reviewing a worksheet the ACBA Mapping software uses VBA’s ‘UsedRange’ function to identify the full scope of the sheet. This function is excellent for identifying separate ranges employed in a worksheet, but has some drawbacks. One of these drawbacks has been identified while reviewing the Enron corpus of files ( https://figshare.com/articles/dataset/Enron_Spreadsheets_and_Emails/1221767 ) The Enron corpus of spreadsheets and other correspondence is huge. Felienne Hermans however has whittled the number of workbooks down to 16,189 unique items.   https://www.felienne.com/archives/3634   The workbook that clearly displayed the UsedRange issue is named ‘benjamin_rogers__938__historical outages.xlsx’. Clearly the files have been renamed to prevent duplicates. In this blog though I will refer to it as ‘Historical Outages’. In the original Mapping software, users were advised when the ‘UsedRange’ exceeded half a million cells. This allows the user to escape from the process and t
Post a Comment
Read more