Skip to main content

Generating the "ACBA-Mapping" Add-In

Background

This Add-In is designed to map the underlying structure of an Excel worksheet. It. provides the means of reviewing the way that a worksheet operates both in terms of its layout and the relationship between blocks of formulae.

The program for the Add-In can be downloaded from the link 'ACBA-Mapping'. Please read the following description of the process before attempting to activate it. 

Generating 'ACBA-Mapping'

The 'ACBA-Mapping' link will download the Add-In file to your personal Downloads folder. The File Explorer icon on your Task Bar, is the entry point.

From your TaskBar

Clicking this will open File Explorer. You want the Downloads folder - encircled in green.

File Explorer normally opens to the 'Quick access' panel in the first instance.

The Downloads folder is usually ordered youngest to oldest. So 'ACBA-Mapping' should be right at the top.

The Downloads Folder

'ACBA-Mapping' contains active code, which will have been disabled during the Download process to protect your computer from malicious viruses. 

The code must be enabled if the mapping process is to become functional. Click the Properties icon on the Home ribbon (encircled in the image above) and choose 'Properties' from the dropdown list. This will generate a dialog form - shown below.

The Properties Dialog

The Unblock box (shown above encircled in green) must be ticked.

Where to store the Add-In

In theory you can run code files from any directory on your hard disk (including the Downloads folder), but Microsoft provides a special folder for your Excel Add-Ins. Excel monitors that folder and allows the user to activate and deactivate his add-ins, at will, from the Developer tab. This is a good place to store 'ACBA-Mapping'.

However, Microsoft does not make it easy to find. The following instrctions will guide you through the process, if you need it.

  1. Open a new window in File Explorer .- go to the File tab and select -> Open new window
  2. In the new Explorer window select 'Hard disk (C:\)' from the left hand list.
  3. Work down the directory tree until the Address Bar reads > This PC > Local Disc (C:\) > Users > User (the directory User may replaced by your name or a shortened version of it).
  4. At this point click inside the Address Bar it will change to read 'C:\Users\User'.
  5. After User (or your name) add a back slash '\'.
  6. You will be offered a selection of subdirectories to choose from.
  7. Select 'C:\Users\User\AppData\Roaming\Microsoft'.
  8. You will be presented with another list of subdirectories.
  9. Select 'AddIns' - normally the first one.
  10. Go back to the 'Downsloads' window and move 'ACBA-Mapping' from there to the 'AddIns' window

Ready to 'Activate'

The Add-In file is now in the right location, but will not activate automatically unless you instruct Excell to do so. Go to the 'Developer' tab on the Ribbon - see below. 

Now click the Add-Ins icon. This will generate the Add-Ins interface form.

Only now are you ready to activate the 'ACBA-Mapping' by clicking on it. Even so, Microsoft Excel wants to make doubly sure that you really mean to activate the code.

If you wish to use the Mapping software, you must 'Enable Macros'.

Where is the 'Map and Review' software?

On activating 'ACBA-Mapping' you will notice an extra tab has been introduced into your Excel Ribbon. It appears between the 'VIEW' and the 'DEVELOPER' tabs and is called 'ACBA-Mapping'.

Initially, the ACBA-Mapping appears mostly inactive.

Most of the Ribbon buttons appeared greyed out (inactive). This is intentional. You cannot create a map or any other analysis until the system has generated a linked workbook in which to store the map. Both parent file and map file are always hyperlinked to each other in both directions.

Rather than clutter up the directories of your working files, all MAP files are stored in a separate directory under your 'My Documents' folder. It is called '\ACBA Mapping\MapFiles\'.

Checking your version of 'ACBA-Mapping'

The most recent versions of the 'ACBA-Mapping' software have an 'About Me' icon within the Ribbon.



This opens a form giving details about the software, including the version number and the date of creation - see the image below.


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.


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