Allen Computing and Business Analysis
A series of Microsoft Excel add-ins and code examples, available for downloading free of charge. We recommend that you join the User Group for technical advice and exchange of comments on all ACBA Functions.
These add-ins have been developed to solve particular problems that we have come across in the course of consultancy contracts or developing our range of audit related products.
The add-ins are open sourced and can be tailored to your own design. If you intend to make extensive use of a free ACBA Function (in particular for training purposes), we would be grateful if you would acknowledge ACBA authorship by linking to this site.
|This page is confined to Excel add-ins and the use of VBA as a solution to user issues, but readers may also be interested in the ACBA approach to auditing Su Doku puzzles. The process deliberately avoids the use of VBA code.|
|We also describe a second series of workbooks built in the company's Electronic Working Paper (EWP) software These demonstrate how the package can be employed to validate solutions to technical computing problems.|
The problem Copying data from other software sources e.g. Acrobat and Word files into Excel, usually results in a single column of string segments. The string segments are often not in any precise or predictable order. Putting the string segments back together and posting them to an appropriate range in Excel is time consuming and tedious. 'Select and Post a String' is an Excel Add-In which simplifies the process hugely.
The solution is a form which allows the user to view each string segment in turn and to select it (or de-select it) for adding to a result string. The user can post the resulting string to a specified cell in another named worksheet at any juncture. The function keeps hold of the form, the original string segments and the array position until the user has completed the task.
The add-in is built in Excel '97 and requires 100KBytes of disk space. It is called 'ConCatStr.xla'.
Download 'Select and Post a String'.
The problem Inevitably the copy process for some sources does not result in a single column of string segments.
This function, which is incorporated within 'Select and Post a String', takes the text from each used cell in a row and posts it as a single concatenated string in column A of the equivalent row on a new worksheet.
The problem When copying from a variety of sources (web pages, MS Word. pdf files etc), you can never be sure what formatting is going to be carried over. It can make a real mess of the look of your worksheet.
This function, which is incorporated within 'Select and Post a String', reformats all the cells in a pre-selected range back to standard. In particular it
The problem Usually this can be handled with the standard Excel find and replace process, but when copying from the web, spaces are frequently translated as a special character - ASCII character 160 - rather than the standard space character - ASCII character 32.
The delete double spaces function, which is incorporated within 'Select and Post a String', examines every cell containing a string on the active worksheet. It looks for both types of space character and keeps on removing them from within a string until only 1 standard space exists between words.
The problem When importing numbers from various sources (but particularly PDF files) we find that the values are often interspersed with spaces. This means that Excel will not treat them as numbers but as text strings instead. Excel's normal numeric functions won't work.
The delete all spaces function reviews all the cells in the range specified by the user and removes any space characters that it finds from the cell values. Any cell values which then consist entirely of numbers will be converted into a numeric data type. This function is incorporated within 'Select and Post a String'.
The problem Spaces are not the only characters that cause the occasional problems when importing data or in other contexts.
This function calls a form that allows the user to select the offending character from a drop down list. You can look for the character itself or the character code value if you know it. The function then reviews each of the string values in the pre-selected range and deletes each instance of the character and replaces the cell value with the revised string. This function is incorporated within 'Select and Post a String'.
As a precaution against corrupting your data the function does not touch
The problem You have downloaded a log of activity on your website into Excel and you want to analyse it, say, for search strings. The URL Encoding (e.g. %20 = space) gets in the way of the result being readable in plain English.
A new function in the string manipulation package analyses strings in a pre-selected range and replaces all the URL Encodes is finds with the appropriate normal character (or deletes it altogether if it is non-printing). This function is incorporated within 'Select and Post a String'.
As a precaution against corrupting your data the function does not touch
The problem Since 1997 the Excel processes for posting hyperlinks onto worksheets have gradually changed. As at 2003, the user interface requires that you post a target cell or range for a hyperlink within the active workbook to a specific named worksheet. This means that when the user copies the worksheet to a new workbook the hyperlink points either to the original worksheet or is deemed invalid. I want it to point to the same cell in the active worksheet irrespective of the worksheet name or the container workbook.
A new form in the 'For Spreadsheet Designers' add-in does that.
The problem One of the most important processes in maintaining the integrity of worksheets is to protect the 'locked' cells, whilst still permitting users to enter data in the 'unlocked' cells. How do you ensure that you have unlocked all the right cells and only those cells?
This function relies on the designer identifying the cells that the user is permitted to edit by a (unique) background colour. It expects the designer to pre-select the cell whose background colour denotes that it is suitable for editing, then 'unlocks' all cells with the same colour in the active worksheet. If you have created a 'style' with this background, which also uses a cells 'unlocked status', then the user dialog lists these and you can choose to apply all the elements of the 'style' to the cells. This is a new function in 'For Spreadsheet Designers'.
The problem You are building a spreadsheet for a client that includes cell comments. As you insert comments manually, your name (as the comment author) is presented in bold at the beginning of the comment text. You would prefer that the author appeared as your company name rather than you personally. You could edit comments manually but it's time consuming and tedious.
A new function in 'For Spreadsheet Designers' allows the user to automatically edit all comments within the active workbook (associated with you as author) displaying your company name (or other suitable reference - including none at all). Comments in cells in protected worksheets are not changed.
The problem You are seeking to create a development profile of individual pieces of code. Normally, small code changes get lost in the melee of development. This process is designed to create individual and specific names for text files that hold your old VBA code.
The solution takes the form of a user defined worksheett function - CreateTextNm. It is contained in 'For Spreadsheet Designers'. It has two parameters.
These are a range (in a single dimension - normally a column) which lists
The second parameter denotes the version number. This is specifically designed to permit the analysis of different versions of the same code, in order to demonstrate the progression of the associated concepts and ideas.
The Problem You have posted your strings onto cells within a single worksheet. Some of the strings however are very long and hang off the edge of the printed page. The option of squeezing down the print size is not practical. You want to merge the cells over the width of a standard printed page and wrap the text within that width, if it is still too long. MS Excel cannot cope with this process automatically. It will not increase the row height to deal with multiple lines of text.
This function examines each string character in turn and estimates the space it needs. It then evaluates the total space needed against the width of the specified range and determines the number of standard rows needed to display the whole text. Additionally it
This function is called 'Merge and Wrap' and can be downloaded from the link. There are two versions of the same function.
The problem You have downloaded data from a print file (or similar) which you want to analyse. It is a big file - too big to tackle deleting extraneous rows/records (e.g. those that are blank or carry page header information) by hand.
This function invites you to identify the value of the cell in the first column/field of the database where the record is not wanted and deletes all rows whose first cell has that value. The process can be iterated until all extraneous rows have been removed from the database.
This is a series of "user defined" worksheet functions available free of charge. In general, they are short coded functions which are available for users to view in the VB Editor. The code includes explanatory commentary.
Description of the Worksheet Function
|Date of the next Monday. Returns the same day if already a Monday. The return value is an integer so you can format the date in the way that suits you best.||NextMon||A date that can be recognised by Excel or a number that can be converted into a date.||23 Feb 2007|
|The Nth non-numeric character in a string. It excludes spaces, commas and full stops from consideration. It will return the string value "#Error" if the function cannot resolve the position value.||NthNonNum||A string or a value that can be converted into a string. A whole number that represents the Nth character you want to find.||26 Feb 2007|
|The number of cells containing a formula in a workbook.||NumFormulae_WB||None - NB this means that the formula calcullates once only unless you force a re-calculation||19 Oct 2008|
|The number of cells containing a formula in the worksheet.||NumFormulae_WS||None - NB this means that the formula calculates once only unless you force a re-calculation||19 Oct 2008|
|Returns a string value with all those elements that look like or resolve to dates (English only for text) removed from the string. The function is designed to leave those numeric values that do not resolve to a date within the string at there original position.||DelDate_frm_String||The string from which dates are to be excluded. If the return value is a nil length string or comprises spaces only the function returns a string value of "Empty String".||02 Dec 2008|
|Returns a specified number of whole words before the position of a numeric value within a string.||TextBeforeNum||NumPos - the position of the start of the numeric value within the string. WordNum - the number of words you want to return. MyString - the string value from which you wish to extract the information. An '#Error' string is returned if the instruction cannot be completed.||02 Dec 2008|
|Returns a specified number of whole words after the position of a numeric value within a string.||TextAfterNum||NumPos - the position of the start of the numeric value within the string. WordNum - the number of words you want to return. MyString - the string value from which you wish to extract the information. An '#Error' string is returned if the instruction cannot be completed.||02 Dec 2008|
|Returns the number of dimensions in an array||NoDimInArray||AnArray - this variable is classified as variant. The array can be numeric, string or mixed. If a standard string or number value is givien as the parameter, the function returns zero. This should be considered an error value.||20 Apr 2010|
Download ACBA Worksheet Functions.xla
The problem The size of a VB project within Excel can grow very quickly. Also this author, at least, cannot always remember just how he has constructed a process and the associated code. When it comes to changing code, sometimes several years later, it is a real challenge. The following example shows how a user can review the code in the whole of his Excel project for instances of particular code string value (e.g. '.CurrentRegion').
Warnings To use this code