Logic, computing and people

 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 a

The Mapping software arose out of a need to review some of my own rather complicated spreadsheets. The worksheets contained a variety of functional parts and I wanted a way of viewing how they worked together. The table below shows how these concepts have been developed over time. Date Primary Development Version Publication Date March 2020 The essential map and primary colour coding. It was always intended that the mapping function would be combined with lists of the main generic formulae used in the spreadsheet.  None   June 2020 The first version of the Mapping software was published and included within it some of the List functions from previous ACBA software developments. None 23 June 2020   March 2021 Introduced the shortcut tool bar. 0.000 Not published April 2021 The layout of the map, its legends, the generic fo

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 bee

  Microsoft offers a range of Add-Ins and other extensions to Excel’s normal functionality. These are accessed most easily from the ‘Developer’ ribbon. The Developer Tab Clicking the Add-Ins icon, offers the user a list of all Add-Ins currently available to your Excel program. The problem comes when your particular program isn’t listed there. Even when you know where the Add-In file is and double click it, the functionality is still not activated. According to SuperUser.com , the problem results from security patch in KB31152, released in July 2016. Apparently, Microsoft software engineers have said: "With this update, we changed the behaviour of Excel so that it will not load certain file types (including .xlam) when they are untrusted. The easiest workaround is to find the add-in that is causing you trouble, right-clicking on it in Windows Explorer, and checking Unblock" However, SuperUser.com recommend placing the add-in in a Trusted Location (in Excel, go to File

Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected. The cell mapping software provides a method for recording a reviewer's comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet. In this case, there's just one comment. The comment above refers to the area highlighted towards the bottom of the map below and are hyperlinked.to the specific area of the map through the column headed 'Map/Source Link'. The top left cell the source range provides a link back to the 'Comments. worksheet'. The yellow cells suggest that users should be entering information for collection by a formula, but which one.  At the this point I went back to the orginal worksheet and traced the dependents for some of these 'so called' user entry cells - also coloured yellow as it

  In the Blog ' What is a Cell Map? ', I included an image of a Cell Map. On reflection the presentation was muddled. A simpler and clearer design seemed worthwhile. The result below is more clearly rectangular, with the headings for the map, the formula table and the names/validation table matched along the same row. The validation table is new and highlights where user inputs are limited by validation and the nature of the validation criteria. The revised software is available at ACBA-Mapping . In addition, although software is intended to be intuitive and self explanatory, it rarely is. I have created a user group at    MSExcelCellMapping@groups.io   . You are welcome to join and ask questions about the software. https://drive.google.com/file/d/1w3tHk1l-G9LjmCsOqMBkJpE9kaSj0iX_/view?usp=sharing Spreadsheet errors and inconsistensies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG) considers

 Following the publication of ' What is a Cell Map? ', I was playing with the functionality and found some unexpected mapping presentations. On one worksheet the mapping delivered a block of constants - all coloured purple. Constants are coloured purple. But on the worksheet in question the software delivered the following. Yellow represents user entry cells . But I knew that the original spreadsheet (from which the map was derived) contained numbers in these cells. In fact they were a lookup table. The user should not have been allowed any where near them. On monitoring the code I found that the yellow cells were deemed to be empty, even though they clearly contained a number - in this case 7.  Monitoring code The code in question used the expression MyCell.Text. This did not recognise the 7 as a text value. Changing the expression to MyCell.Value brought about the desired effect. Although I have acheived the 'correct' result, testing has shown that MyCell.Text deliver