Page Content

Related Pages

Exhibition Workbooks in EWP

The Electronic Working Papers (EWP) software, although originally constructed for auditors, is mulri-functional. It is particularly suited to the analysis of and commentary on technical problems from an educational perspective. One of the first workbooks we published was our approach to solving Su Doku puzzles. This page contains descriptions of and links to a series of workbooks built in the EWP.

Deleting Duplicates

This sounds easy, but in this case the request came from an organisation who only wanted two to three fields from their original database extracted. The exclusion of duplicates was secondary. An additional requirement was that the original database should remain unchanged. The workbook Deleting Duplicates contains both an analysis of the problem and the code appropraite to a possible solution. NB - Contains VBA Code.

Analysing Lists

This particular workbook analyses lists (paragraphs of free text) for the names of British birds that they contain. It then looks for any numeric content associated with the birds found. The workbook Birds - Analysis of WebLogs demonstrates and evaluates the potential for employing ACBA Tools functionality for analysing lists.

Arithmetic Combinations

This project stems from a request submitted to one of the myriad of Excel on-line discussion groups. The workbook Arithmetic Combinations outlines two potential solutions to the questions "How many combinations of a named group of variables are there?" and "What is the value associated with each combination?". The project deconstructs the primary elements of the process for creating combinations and considers how these might be applied to a solution wiich uses formulae only (through Excel's user interface) and one which employs VBA Code. This workbook also considers that rather curious relationship between Arithmetic Combinations, binary numbering and the maximum number of rows in a worksheet (Excel 2003 or earlier).NB - Contains VBA Code

Management and Control of VBA Coded Projects

Over the years, the development of code published on our Oddments page has grown and become difficult to monitor and control. The workbook created and prepared at Oddments Development Record is a first attempt to draw together and evaluate the separate developments and activites that are publicised under this generic heading.

This particular file demonstrates the use of both ACBA-EWP style templates (to generate fairly complex worksheet presentations) and the Documentary Evidence Index. The evidence index, which links directly to separate code snippets and associated form layout pictures, shows the development of code without including coded modules within the project file itself. This approach for presenting VB code to the potential user overcomes the frequent barrier that commercial entities employ to stop their staff downloading inappropriate files from the internet.

Using Templates within the ACBA-EWP software

The employment of templates is fundamental to the functional flexibility of the ACBA-EWP software. Examples of downloadable templates are listed at our web page concerned with Audit Resources. This workbook ACBA-EWP_Templates.xls demonstrates how the templates might be employed in practice within the context of the company software.

Using Named Ranges within Excel

Using names or 'named ranges' within Excel causes considerable controversy. As a company ACBA (UK) is in favour of their use, although we limit the scope of usage to those elements that meet our specific functional needs. This demonstration workbook NamedRanges.xls shows how we employ the naming of ranges at worksheet level to carry data from individual worksheets to a separate summary sheet. It also provides some technical background on the approach used.

Listing the Formulae contained in a Workbook

The ACBA Tools software provides a range of methodologies for listing the formulae errors and numeric values etc. within a workbook. Recent work using the tools suggested that, in certain circumstances, they were too slow to be used effectively. After consultation we have prepared a revised approach to the lisitng of formulae. This is much quicker. The linked workbook CountingSpecialcells.xls outlines the code and the changes incoporated to make it quicker. Users are welcome to test the code and provide feedback to ACBA on their results. Where appropriate this data will be included in the project. NB - Contains VBA Code

Analyzing Array Formulae

As a company we have to date (June 2011) sought to avoid using ArrayFormulae. The ACBA Audit Tools set was not originally designed to cope with these sophsticated constructs. We found the prospect of a single formula being capable of delivering a range of valid answers difficult and unnerving. However, a 2011 project designed to consider the whole gamut of practical uses and structures to which spreadsheet workbooks could be put, brought us into direct contact with this approach. We determined to review our Audit Tools software so as to offer an explicit analysis of those formulae on a spreadsheet which appeared to comply with the characteristics of an ArrayFormula. The analysis is designed to show the formulae, their range(s) of operation (both in terms of cell addresses and relative locations) and an evaluation of their validity/results.

A downloadable ACBA project file ArrayFormulaAnalysis.xls demonstrates the results of this analytical approach. It also exposes the limitations of our source material for testing the methodology. Accordingly we invite readers to submit their own files for analysis of the array formulae contained within them. We will undertake to up date our project file with analytical details of those files submitted. Please send any Excel files containing ArrayFormulae to Stephen Allen

Working with ACBA-EWP "Controlled Statements"

In practice the only extended use of "Controlled Statements" has been inside the company. In January 2012, we decided to relaunch the concept by presenting the process to the Excel DevCon 2012 seminar held in London. The original intention was to include a working Excel file that demonstrated the use of the statements in situ, but there wasn't time for such an approach. Readers may be interested to see this demonstration project.

TicTacToe (or Noughts and Crosses)

This project was constructed to demonstrate the nature of control that a designer can achieve over input. It uses the game TicTacToe (or Noughts and Crosses in the UK) to show how we can design the operating environment of a worksheet to manage and control input parameters. It presents two versions of the game. The first version only uses controls available at Excel's user interface (e.g. cell validation and text based formulae). The second version uses Excel's VBA functionality to achieve very close control over the game play and the presntation of results.NB - Contains VBA Code.

Sudoku - Electronic Working Papers in practice

This exhibit is of an active working file or project - current version 04 Dec 2012. The central subject is about how to employ EWP to solve a Sudoku puzzle. It is functional rather than pretty. We review the model from several different perspectives and employ the EWP indexing functionality to keep track of our comments and analysis.

However, the main role of the project is to show how fundamentally eccentric this model is when compared with most models in Excel. The workbook contains a series of linked worksheets that demonstrate the solution to a Sudoku puzzle published in the The Guardian, 18 February 2012. We then deconstruct the model to show how it works. This deconstruction takes the form of listing all the formulae within each fundamental element of the model, opening out each of the templates (on which the model is based) to show the hidden elements and providing a commentary on  how and why we chose those particular structures and methods.

Listing the Unique Formulae contained in a Workbook

The capacity to list each of the fundamental formula types within a worksheet or workbook seems to be a common thread in a wide variety of commercially available spreadsheet audit and analysis software. This project considers how the software developers might have achieved their goal.

The EWP file UniqueFormulae.xls reviews a simple methodology for substituting the cell references generated from listing all the formulae with a standard string 'MyRef'. It then lists all the unique string values for the formula constructs. The workbook contains its own internal commentary that discusses some of the technical issues associated with generating a useful list.

This methodology was first developed in response to a query in the 'Microsoft Excel and VBA Users Group (LinkedIn). Reposting in the EuSpRIG forum generated some vigorous criticism. In particular, I was reminded that mosts analysts in this area employ the 'R1C1' notation for cell references. Nevertheless I took the view that examining a problem from a slighly different technical perspective could generate new insights.  In particular, I considered how a phased approach to analysis to illuminate different aspects of the results for interpretation.

It is clear that the analysis of formulea within a workbook and the myriad of other concepts and structures is not simple. Commercial analytical software makes real progress towards reviewing workbooks from a variety of approaches. However, there is nearly always room for other perspectives. I commend this approach to reviewing 'unique formulae' as a potential alternative.