Skip to main content

ACBA-Mapping - Development, Version Control and Publication Dates

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 formula listings and named regions were all reorganised, to make it easier to view and understand the map. Hyperlinks to the original worksheets were included. The 'Commentary' worksheet was more fully developed to show how any desirable amendments had been implemented. 0.000 25 April 2021
August 2021 The Map books now stored together in a separate directory away from the parent workbooks. We have introduced a method of naming mapbooks with their creation date so as to prevent the over writing of identically named parent workbooks located in different directories on the user's computer. The Mapbook software itself is also version controlled and new code introduced to load and up date the Add-In to the user's Add-In folder automatically. 1.012 10 August 2021
August 2021 Remove Add-In menu items and tool bar icons on closing 'ACBA-Mapping'. 1.013 11 August 2021
August 2021 First trial of bona fide Ribbon commands. Icons yet to be sorted. 1.014 14 August 2021
August 2021 Ribbon icons now revised. Version created for testing. 1.015 18 August 2021
August 2021 Safety version. Considering major change to generic formula development. 1.016 20 August 2021
September 2021 Ribbon controls fully developed, with special reference to environmental sensitivity. Export function developed and tested. 1.017 27 September 2021 
November 2021 Latest version made available for download. 1.019 5 November 2021 
May 2022 Small upgrade - covering cell based errors (not formulae errors). The software also recognises 'Chart Sheets', but there is no associated analysis. 1.023 20 May 2022 
May 2022 Reorganisation of the key in preparation for multiple formula search criteria - not published. 1.024 30 May 2022 
June 2022 Introducing multiple formula search criteria. This is a major development that allows the user to consider the spatial relationships between different formula categories. It is especially useful for identifying those ranges where the constructor has adapted the formulae so that the results appear superfically consistent. 2.001 1 June 2022 
June 2022 Recognising that worksheet names may derive from a sheet other than the mapping parent. Allowing for this within programming. A minor amendment that allows worksheet names to have a space character. 2.002 8 June 2022 
August 2023 Potential issues resolved when operating 'Mapping' at the same time as 'Electronic Working Papers'. This necessitates a change of code file name to 'acbaMapping'. 2.005 20 August 2023 
Septembet 2023 New functions added that permit the comparative analysis of maps and other data sheets. This is especially useful for analysing the development of mapped worksheets over time. 2.006 04 September 2023 

I will update date this blog from time to time, as new functionality is added to the ACBA-Mapping software.

Stephen Allen, 04 September 2023

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