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.