Logic, computing and people

 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...

We know that our spreadsheets should be functional and error free, but there is  a tension between how we review a spreadsheet to ensure that it is correct and the functionality of the spreadsheet itself. None of us like Big Brother breathing down our necks too closely. In What is a Cell Map?  I discussed the creation of a simplified version of a spreadsheet to aid review and error trapping. However, the map is itself a spreadsheet. If you created maps for each worksheet in your workbook, the workbook would quickly bloat and become unmanageable. The obvious answer is to post your maps somewhere else, but clearly linked to primary workbook. The ACBA-Mapping package does just that. It generates a separate Map Book to hold all the spreadsheet maps (and other analyses) associated with the parent workbook. The parent workbook has a new worksheet that maintains a summary of all the detailed analyses generated for the workbook. This new worksheet is called 'DataList_and_MappingC...

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...

In the first decade of this millennium I developed two significant add-ins for Excel, that guided my approach to the structure of Excel workbooks ( ACBA-EWP.xlam ) and the review of those workbooks for errors etc ( ACBA-Tools.xlam ). The software can be downloaded from the links. Diets would be boring if they never changed. The Electronic Working Papers software assumes that workbooks do NOT stay static in terms of their structure. Rather they change and develop over time. It provides a methodology within which the changes and development can be managed. The reference to working papers is designed to show the rationale for that development. More detail about about operational mechanics of the software are available at ACBA Electronic Working Papers . It is almost inevitable that Excel workbooks developed over time will become corrupted and error prone. The ACBA Tools software provides a series of simple tools that allows the user to review individual worksheets for their f...

Excel has sophisticated methods available for users but it is easy to misunderstand what the program designer really meant. The Intersect method could be one of these. I first used the method a little over 20 years ago. It allowed me to post a value in named column, in respect of specific transaction (formed by a row). At that time, the real advantage of this approach the location of the column could change without my having to rewrite the code. It would continue to be posted to the correct cell for the purposes of the overall program. My current task – one range within another - sounds very similar. Indeed, when searching the web, the Intersect method was offered as a potential solution . However, there are some crucial / fundamental differences. I was not looking for overlapping ranges. Rather, the whole of the smaller range had to be contained within the boundaries of the larger one. It became clear that I had examine each of the boundaries of the larger range individ...

I have programmed in MS Excel using VBA (Visual Basic for Applications) fairly regularly over the past 25+ years. This makes me experienced but not necessarily expert. Frustration over the past several days seems to have demonstrated the lack of expertise, particularly in handling hyperlinks between Excel workbooks. The project involved building a hyperlink bridge between specific pages/worksheets within two workbooks. The environment wasn’t too difficult since both workbooks would be housed in the same directory. However, the two workbooks would have a parent / child relationship. The code was designed to build a new control worksheet in the parent workbook and hyperlink it to a specific worksheet and cell range in a child workbook book. The child workbook would be generated entirely in code before being saved. This didn’t sound too difficult and it wasn’t; until it came to trying to create the hyperlinks between pages of the different workbooks. The first thing to n...

Excel is well known as an environment in which you can create models. Many modellers secure the integrity of their model by a combination of protecting and/or hiding sensitive cells. This is understandable when the model becomes the live or final version. Nevertheless, I have railed against using Excel in this way for 3 decades but have failed to convince colleagues. Using Excel to model solution methods in Sudoku is intended to demonstrate why it is such an excellent environment for exposing the strengths (and weaknesses) of any particular solution method. I concentrate here on two very similar methods referred to as “Intersection”. Background I have published a number of blogs / articles covering the more straightforward elements of users interacting with their Sudoku puzzles. This article builds on the previous publications. Title Source Date Spatial Modelling Techniques in Microsoft Excel EuSpRIG 14th Annual Conference, Greenwich July 20...