Web Design
ACBA Oddments

Select and Post a String, Convert to Single Column, Reset Formatting, Delete Double Spaces, Delete All Spaces, Delete Any Character, Remove URL Encoding, Relative Hyperlinks, Unlock Selected Cells, Change Comments Author, Name Text-files, Merge and Wrap, Auto-delete Rows, ACBA Worksheet Functions, Review VB Code

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.


Select and Post a String

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

Convert to a Single Column

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.

Reset Formats to Standard

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

  • unmerges any merged cells
  • removes wrapped text formatting
  • sets the horizontal alignment to "general" (i.e. left for text and right for dates and numbers)
  • set the vertical alignment to "bottom"
  • Autofits all the row heights within the range

Delete Double Spaces

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.

Delete All Spaces

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

Delete any Printing Character

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

  • any string value generated by formula
  • any numeric value
  • any value which has been specially formatted (this covers dates as well as any other values like formatted telephone numbers)

Remove (replace with Acsii characters) URL Encoding from Strings

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

Relative Hyperlinks

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.

Unlock Selected Cells

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

Change Comments Author

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.

Generate Text-file Name

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 Excel Workbook/AddIn name
  • the VBS Form/Module name
  • the name of the Object (and associated action) / the sub-routine or function.

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.

Merge And Word Wrap Cells within specified Ranges

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 breaks down strings of greater than 1,000 characters into two or more manageable chunks. Strictly speaking the maximum recommended string size in a cell is 255, but for the vast majority of Excel operations it will handle strings of up to 1,000 characters quite happily. Above that and Excel begins to wobble at the knees. This function looks at string sizes and breaks them up if they are too big, takes account of the size of font used in each cell as regards both the standard row height required and the character spacing required, and takes account of the common fonts by name including the bold and italic versions of those fonts. This function is called 'Merge and Wrap' and can be downloaded from the link. There are two versions of the same function.

  • Version 1 - you must pre-select the range of cells over which you wish the 'Merge and Wrap' to take place.
  • Version 2 - Asks you in which column the text is and how many columns to the right of it you want the text to wrap. The function then evaluates the whole worksheet.

Auto-delete Rows

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.

Download 'AutoDeleteRows.xla'.

ACBA Worksheet Functions

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


Parameters required


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.


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.


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.


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.


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.


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.


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.


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


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

Review VB Code - Automatically

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

  • you must copy and paste it into the VB project under review.
  • you must set Option Base 1 in the 'General Declarations'.
  • you must set a library reference to the correct extensibiliy .DLL - see Chip Pearson's site for an authoritative source. Each project must have its own reference.
  • the code creates text files of your modules with a names like 'Mod1' - where 1, 2, 3 are sequential - within the windows 'Current Directory'. The code will overwrite any existing files with the same name.

The code reports its findings on a new worksheet within the active workbook.

Sub ReviewMyVBCode()

Dim vbProj As VBProject
Dim vbComp As VBComponent
Dim vbCode As CodeModule
Dim wb As Workbook
Dim ListSubsArray() As Variant
Dim ModNmArray() As String
Dim FoundCodeArray() As Variant
Dim PrintArray() As Variant
Dim r As Long, s As Long, t As Long, u As Long
Dim a As Long, b As Long, c As Long, d As Long
Dim FileNumber
Dim ArrayCount As Long
Dim CodeStr As String
Dim LineCount As Long
Dim FindStr As String
Dim FindStrNo As Integer
Dim ws As Worksheet
Dim PrintRng As Range
Dim LstAttribLine As String, FstAttribLine As String, DiscLineNo As Integer

'Get the string value from the user.
FindStr = InputBox("Enter the code String that you want to review.", "Auto Review VBA Code")
If FindStr = "" Then Exit Sub

'First create a list of all the Modules and Procedures inside them

Set wb = ThisWorkbook

Set vbProj = wb.VBProject

For Each vbComp In vbProj.VBComponents

        Set vbCode = vbComp.CodeModule

        s = vbCode.CountOfLines
        r = vbCode.CountOfDeclarationLines + 1
        With vbCode
            Do Until r >= s
                ArrayCount = ArrayCount + 1
                ReDim Preserve ListSubsArray(4, ArrayCount)
                ListSubsArray(1, ArrayCount) = vbComp.Name
                ListSubsArray(2, ArrayCount) = .ProcOfLine(r, vbext_pk_Proc)
                ListSubsArray(3, ArrayCount) = r
                ListSubsArray(4, ArrayCount) = r + .ProcCountLines(ListSubsArray(2, ArrayCount), vbext_pk_Proc)
                r = r + .ProcCountLines(ListSubsArray(2, ArrayCount), vbext_pk_Proc)
        End With
    'Now export the Modules to the local directory
    t = t + 1
    vbComp.Export "Mod" & t
    ReDim Preserve ModNmArray(t)
    ModNmArray(t) = vbComp.Name


'Now review each of the exported Modules in turn

For r = 1 To t
    FstAttribLine = "n"
    LstAttribLine = "n"
    DiscLineNo = 0
    LineCount = 0
    FileNumber = FreeFile    ' Get unused file number.
        Open "Mod" & r For Input As #FileNumber    ' Create file name.
        Do While Not EOF(FileNumber)    ' Loop until end of file.
            LineCount = LineCount + 1
            Line Input #FileNumber, CodeStr    ' Read line into variable.
            'The Export process adds in extra information lines about the code
            'First find out where the code proper starts
            'Setting a value for DiscLineNo tells us we've started the real thing
            If DiscLineNo = 0 Then
                If FstAttribLine = "n" Then
                    If InStr(1, CodeStr, "Attribute ") = 1 Then
                        FstAttribLine = "y"
                    End If
                    If InStr(1, CodeStr, "Attribute ") = 0 Then
                        LstAttribLine = "y"
                        DiscLineNo = LineCount - 1
                    End If
                End If
                'Review the code for instances of your string
                FindStrNo = InStr(1, CodeStr, FindStr)
                If FindStrNo > 0 Then
                    a = a + 1
                    ReDim Preserve FoundCodeArray(4, a)
                    FoundCodeArray(1, a) = ModNmArray(r)
                    For s = 1 To ArrayCount
                        If ModNmArray(r) = ListSubsArray(1, s) And _
                        LineCount - DiscLineNo > ListSubsArray(3, s) And _
                        LineCount - DiscLineNo < ListSubsArray(4, s) Then
                        FoundCodeArray(2, a) = ListSubsArray(2, s)
                        Exit For
                        End If
                    Next s
                    FoundCodeArray(3, a) = LineCount - DiscLineNo
                    FoundCodeArray(4, a) = CodeStr
                End If
            End If
    Close #FileNumber ' Close file.

'Check whether any instances of the string have been found
'If not, notify the user
If a = 0 Then
    MsgBox "The code review has not found any instances of '" & FindStr & "'."
    Exit Sub
End If
ReDim PrintArray(a, 4)
For s = 1 To a
PrintArray(s, 1) = FoundCodeArray(1, s)
PrintArray(s, 2) = FoundCodeArray(2, s)
PrintArray(s, 3) = FoundCodeArray(3, s)
PrintArray(s, 4) = FoundCodeArray(4, s)

'Print out the findings onto a new worksheet in the active workbook

Set ws = ActiveWorkbook.Worksheets.Add
With ws

    .Cells(1, 1).Value = "Report of Code Location for '" & FindStr & "' contained in VB Project '" & ThisWorkbook.Name & "'."
    .Cells(2, 1).Value = "Printed out on " & Format(Now, "General Date")
    .Cells(4, 1).Value = "Module"
    .Cells(4, 2).Value = "Procedure"
    .Cells(4, 3).Value = "Line No."
    .Cells(4, 4).Value = "Code"
    .Cells(4, 4).EntireRow.Font.Bold = True
End With
Set PrintRng = ws.Range(ws.Cells(5, 1), ws.Cells(4 + a, 4))
PrintRng.Value = PrintArray

End Sub


Last updated 10 October 2016


[Home] [wp] [tools] [toolsprag] [oddments] [sudoku] [audres] [contact] [DesignFacilty]