Welcome to my Excel utilities page. This page contains a number of example files for download, which are often mentioned in the Microsoft Excel support sites. If you can not find the file you are looking for, please e-mail me at: stephen@oaltd.co.uk.

There have been over 2,000,000 downloads from these pages since I started counting in July 1998. Thank you.

The popular Smart Indenter and VBE Tools have been rewritten as C# addins to work in both 32-bit and 64-bit version of Microsoft Office and are available to download from our sister site OfficeAddins.co.uk.

ModelessForm.zip (7 June 1999, 20k, 34,523 downloads) Updated
There have been many, many requests for Excel to support modeless userforms, which have been provided in Excel 2000.  I recently discovered what can only be described as a bug in Excel 97, which results in a normal userform going modeless.  Rob Bovey and Russell Davis identified the cause as the EnableWindow API call. This update now uses that API call to change a userform's modal state, and works both for Excel 97 userforms and Excel 95 dialogsheets.  Note, however, that doing a cell drag-and-drop while the modeless form is visible will cause Excel to GPF (thanks go to Dave Mullins for pointing this out)!  This trick does not emulate Excel 2000's real modeless forms in terms of code being able to run side-by-side, and should be treated as nothing more than a curiosity.

GetFonts.zip (8 July 1997, 7k, 18,483 downloads)
This file demonstrates one technique for retrieving the list of installed fonts from Windows, using API calls. We can't just enumerate through the list, since that requires using a callback function, which is not supported by VBA. Instead, we can use a proxy program (e.g. Character Map) to retrieve the list into a list box, which we can then locate and read the font list from.

CentrD.zip (28 May 1997, 10k, 19,904 downloads)
This file demonstrates how to use Windows API calls to display a custom dialog centered on the screen. Both 16-bit and 32-bit versions of the routine are included. Now updated to handle dialog sheets in Excel 97, where the class name changed!

EBAlign.zip (16 May 1997, 18k, 15,670 downloads)
This file demonstrates how to align a standard dialog sheet edit box to the left, center or right. The technique uses characters of different width (e.g. spaces and underscores) to fill out the gap at the left of the edit box. This uses 32-bit Windows API calls to make things very fast, allowing for real-time alignment as data is typed into the box.

ListCols.zip (16 Dec 1996, 53k, 24,588 downloads) Updated to use Win 32 API.
This file demonstrates how to show information in columns in a standard Excel list box. The technique uses characters of different width (e.g. spaces and underscores) to fill out the gap between the columns. Judicious use of the two characters enables the columns to be aligned to the nearest pixel. This update uses 32-bit Windows API calls to make things much faster in Excel 7. API calls don't work in Excel 5 due to font irregularities.

FloatWnd.zip (21 Nov 1996, 11k, 23,011 downloads)
This file uses Windows API calls to create a window that floats above the normal Excel window, allowing the programmer to display messages, while the user is still working with Excel. In that respect, it is a modeless message window. I haven't yet worked out how to put buttons etc. on the window, or trap mouse clicks.

WksPrgrs.zip (16 Nov 1996, 23k, 25,925 downloads)
This file contains a VBA routine to draw a 'modeless' dialog box to the Excel window. The dialog box contains a percentage-complete progress bar, which runs for example while a routine is completing. It works by directly drawing and writing to the Excel window, using Windows API calls. Includes versions for both Excel 5 (using 16-bit API calls) and Excel 7 (using 32-bit calls).

EBProgrs.zip (12 Nov 1996, 16k, 17,195 downloads)
This file contains a VBA routine to convert a normal edit box on a standard Excel 5/7 dialog box into a percentage-complete progress bar, which runs for example while a button-activated routine is completing. It works by directly drawing and writing to the dialog box window, using Windows API calls. Includes versions for both Excel 5 (using 16-bit API calls) and Excel 7 (using 32-bit calls). Note that this routine is intended for those developers with good knowledge of using Windows API calls. See the file WKSPRGRS.zip above for a (better) worksheet equivalent of this routine.

CheckKey.zip (20 Aug 1996, 8k, 19,220 downloads)
This file demonstrates how to check for a key press during a lengthy looping routine. For example, your routine could display the message "Press the space bar to cancel this operation.". The routine could check if the space bar had been pressed at the start of each loop. It uses Windows API calls to check the message buffer. Both 16-bit and 32-bit versions of the routine are included.

EnumDlg.zip (21 Oct 1996, 10k, 13,754 downloads)
This file uses a recursive VBA routine to cycle through all the open windows in Windows, returning the hWnd ID, class name and window text for each one. If started while a dialog box is visible, it shows that most of the standard Excel dialog controls are not exposed to Windows and hence can not be manipulated using API calls. This file now includes both 16 and 32-bit versions, but the 16-bit gives an Out-of-stack space error, due to using a recursive function.

Chrono.zip (9 Oct 1998, 4k, 24,365 downloads)
If you have a worksheet updated continuously with a DDE link (e.g. a stock price feed), you may want to record the time during which the price is above or below a threshold value (e.g. over the past 4 hours, the price was below 500 for a total of 20 minutes).   This file shows how to use sets of circular references and normal worksheet formulae to implement such chronometers (one chronometer for each price).  Control cells allow us to reset, start, stop, pause and update the chronometers.  There is no VBA involved.

GS_Demo.zip (31 Mar 1997, 5k, 25,966 downloads)
There is very little written about the positive uses of circular references. Most people view them as an annoying part of spreadsheet life, usually the result of doing something wrong! This spreadsheet demonstrates a few techniques for using circular references to your advantage. The example is a worksheet-based goal-seeking algorithm, done entirely using standard worksheet formulae. The example iterates through a number of guesses, remembering the guess and result at each iteration. Yes, worksheets can have 'memory'.

LastChng.zip (12 Oct 1996, 5k, 22,494 downloads)
Here's the deal: You have a number of cells that are changing on a regular basis (e.g. DDE stock price feeds). At any point in time, you want to know which was the last cell that changed, what was its value before the change, and what is its value now. This worksheet does exactly that, using circular references. Full explanations included in cell notes in the sheet.

Life.zip (20 Nov 1996, 8k, 25,507 downloads)
This file is a worksheet implementation of Conway's Game of Life. There are three rules to life: 1. A living cell with fewer than two neighbours dies of isolation. 2. A living cell with more than three neighbours dies of overcrowding. 3. New life is generated in an empty square with exactly three neighbours. This worksheet contains ONE formula and no VBA. The solution uses a circular-reference array formula to calculate each generation.

RandSort.zip (20 Nov 1996, 4k, 20,230 downloads)
This file demonstrates how to use worksheet formulae to randomise a list of numbers or text (for example to generate a list of random numbers without repeats), and also how to sort a list.

Previous.zip (9 Oct 1996, 5k, 18,094 downloads)
If you have a worksheet updated continuously with a DDE link (e.g. a stock price feed), is it possible for the worksheet to automatically remember previous values as new ones come in? Yes it is, using a complex set of circular references. This worksheet will remember the previous 10 entries typed into the changing cell. Full explanations included in cell notes in the sheet.

FunChrt7.zip (4 February 2000, 15k, 51,488 downloads)
This spreadsheet demonstrates the power arising from using defined names as the source for your charts. When charting data from a large data set (say more than a few hundred data points), it can help to be able to zoom and scroll through the data.  I.e. to set a window of say 50 items and scroll through the data showing only that number of items.  This example uses a chart and two scroll bars (for the zooming and scrolling), while the link from the scroll bar values to the chart display is done entirely using defined names - no code required.

ChtFrmla.zip (1 Oct 1999, 5k, 74,128 downloads) Updated
This spreadsheet demonstrates the power arising from using defined names as the source for your charts. The example shows how you can plot a function, such as y=exp(x)*sin(x^2) over a range of x values, without putting them all into separate cells. All the hard work is done in two defined names.  This update fixes a problem with charting functions containing 'x', such as exp().

FunChrt6.zip (27 February 1998, 6k, 35,218 downloads)
This chart shows how to create polar plots in Excel. It uses one set of defined names to dynamically refer to the data points entered (so new points are automatically included in the chart) and a second set to perform the conversion from polar to cartesian coordinates, which are then plotted using a standard XY chart.

FunChrt5.zip (6 November 1997, 8k, 37,915 downloads)
This chart shows how to create the effect of having variable-width columns in your column chart. The example shows a plant's production volume vs. production cost. The x axis shows the cumulative production volume for the plants, with the width of each column being that plant's production volume.

ScoreFrq.zip (12 Dec 1996, 8k, 27,464 downloads)
This is a chart which uses stacked bars to display attitude data. For example, if you want to show how many people chose which score for a particular statement, and what was the weighted average of them all, this chart does it all. It shows the scores, average score and number of people who chose each one, all on the one chart.

FunChrt4.zip (21 Oct 1996, 4k, 41,900 downloads)
This spreadsheet contains two charts showing how different sets of columns can be compared. The first has a column as a background, with stacked columns in the foreground. This gives the impression of some amount gradually being filled (e.g. utilization of a loan facility). The second chart shows side-by-side stacked columns, i.e. A stacked on B, next to C stacked on D.

FunChrt1.zip (20 Aug 1996, 4k, 43,273 downloads)
This file demonstrates how to set up a chart so that it automatically includes new data as you type it in. The trick is to use defined names to link the chart to the data, instead of absolute references. The defined names use the OFFSET() and COUNT() functions to refer to the data.

FunChrt2.zip (20 Aug 1996, 4k, 26,034 downloads)
This shows how to set up an 'attitude' chart on a worksheet. The chart has attitude descriptions on the left axis, values along the bottom axis and vertical lines linking the values for each respondent. Easier to see than explain!

FunChrt3.zip (20 Aug 1996, 4k, 25,818 downloads)
This spreadsheet contains two charts showing horizontal bars overlaid with time series data. The time series can be lines or columns. The example shown is to correlate drug administration periods with average daily temperatures.

IndenterVBA.exe v3.5 (10 February 2005, 260k, 117,471 downloads) For Office 2000-2003. Updated to Version 3.5
IndenterVB6.exe v3.5 (10 February 2005, 260k, 62,783 downloads) For Visual Basic 6. Updated to Version 3.5
Indenter97.exe v3.5 (10 February 2005, 381k, 26,544 downloads) For Excel 97. Updated to Version 3.5
These COM Addins for the Office 2000-2003, Excel 97 and Visual Basic 6 IDEs are the latest updates for the popular Smart Indenter utility. It adds the ability to rebuild all the indenting for a VBA procedure, module or project (i.e. indenting after each If, For etc and outdenting before each End If, Next etc.).  The routine handles all valid VBA constructs, including line continuations, multi-statement lines and conditional compilation items and works within all of the Office products and any other VBA6 host.  Please let me know of any valid VBA syntax that it does not correctly indent.

FormFun.zip (11 January 2005, 36k, 90,390 downloads) Updated
This download demonstrates all the fun you can have with an Office userform.  The example form is modeless, sizeable and with or without a caption, icon, maximize, minimize and close buttons!  Thanks to Tim Clem for working out how to set the userform's window style and get Excel to notice. This update fixes a problem when showing the userform's icon in the task bar and adds some usage notes as comments in the code.

VBETools.exe v2.0  (15 October 2004, 214k, 55,298 downloads)
This COM Addin adds a number of features to the Office VBE, including:

• Close all open windows in one click
• Display the size of the active module on the toolbar
• Rename controls and update the code that uses them
• Modify the Tools > References dialog to display the full path of any references
• Accurately Move and size userform controls pixel-by-pixel

More information and screen shots can be found on the VBE Tools page.

RecordatMark.exe v1.1  (13 October 2004, 210k, 8,001 downloads) Updated to v2.0
This COM Addin for Excel 2000-2003 provides a faithful copy of the 'Record at Mark' feature found in Excel 5 and 95, but which was dropped by Microsoft in the transition to Excel 97.  Record at Mark allows you to record code directly into existing macros, instead of always recording into a new procedure. 

FPAutoStart.exe v2.0  (29 May 2000, 244k, 7,788 downloads)  Updated
FrontPage 2000 is now a host for VBA, allowing the power-user to define their own macros and automate common web management tasks.  While the other Office applications have a document-centric approach to VB Projects (i.e. the VBA code is associated with a document), FrontPage uses an application-centric approach in which the VBA routines are associated with the FrontPage application, not any specific Web or Page.  FrontPage 2000 exposes a number of properties and events for the developer to utilise, but it is not possible to tell FP to run a routine when it starts up.  This file is a COM Addin which runs two routines when starting FrontPage, if they exist in the FrontPage project:

Within the Auto_Start() or Auto_AfterStart() routines, the power-user can create hooks into other FP events using the standard VBA WithEvents techniques.  To use the addin, just run the installation routine and create a procedure called Auto_Start or Auto_AfterStart in your FrontPage project.  The Office 2000 Developer source code is included in the download.

VBEMRU.exe v1.0  (10 July 1999, 298k, 6,665 downloads)  
In Office 2000 Developer, we have the ability to create standalone VBA Projects which can be compiled into ActiveX DLLs.  These projects are opened, saved, compiled and closed entirely within the VBE.  Unfortunately, Microsoft did not provide a most-recently-used list for these projects, nor a default file location for use in the Open and Save dialogs.  This COM Addin corrects those two omissions, providing a VBA Project MRU list, and a default project directory.  The source code project is included partly as a .vba file, for use with Office 2000 Developer and partly as a VB6 project.

MultiFindReplace.exe v1.0  (17 June 1999, 316k, 7,291 downloads)  
This is a COM Addin developed using Office 2000 Developer which extends the built-in VBE Find/Replace dialog to perform the operation across a selection of all the open projects.  This is very useful when developing an application with workbooks/documents that reference each other; you can now rename a procedure or global variable across all the projects in one go.  The source code project is included as a .vba file, for use with Office 2000 Developer.

AddinConnector.exe v1.0  (17 June 1999, 203k, 7,128 downloads)  
When developing COM Addin projects in Office 2000 Developer, a slight change to the code in the addin project will often cause a recompile of the addin, and hence the loss of any commandbar button event handlers you may have set up - the result being that you click on your Addin's menu item and nothing happens.  Restarting the addin requires you to open the Addin Manager, set your addin to Unloaded, close the Manager, open it again, set your addin to Loaded and finally close the Manager.  This COM Addin for the VBE adds a menu item to the Project Explorer popup menu to do the Unloading/Loading for you.  Two clicks and you're reconnected.

Resizer.zip (18 September 2000, 23k, 27,307 downloads)
This file demonstrates how to make your Excel 97 and Excel 2000 userforms resizable and includes a simple class module to handle the userform's resizing and the repositioning and resizing of all the userform's controls.  Make your userforms resizable with just three lines of code!

VBEOnKey.zip (11 May 2000, 36k, 13,181 downloads) Updated
When developing routines to add functionality to the VBE, there is no built-in way of assigning shortcut key combinations to your routines.  In theory, the answer is to subclass the main VBE window, so you can hook into and interpret the low-level Windows messages.  In practice, this leads to GPFs if you have more than one addin trying to do it.  There is, however, a safe alternative that doesn't touch the VBE window, preventing addins from conflicting with each other.  This download contains an example of that alternative, providing the user with a new VBEOnKey function.  This is used in exactly the same way, and takes the same parameters as Application.OnKey in Excel, but operates within the VBE.  Thanks go to VB MVPs Karl Peterson and Randy Birch for their help in showing me the way through the maze of API calls required to get it working.

CustomDragDrop.zip (11 August 1999, 15k, 18,209 downloads)
If you drag/drop a range using the right mouse button, Excel provides a list of alternative operations to perform, such as Move, Copy, Copy as Values, etc. This example demonstrates how to add our own items to this menu, identifying the drag-source and drop-target ranges, and performing our own drag/drop operations.

CellWatch97.exe (7 August 1999, 288k, 19,331 downloads) v1.2. Self-installing exe.
This routine provides the ability to add a 'watch' expression to worksheet cells. The contents of the cells are displayed on toolbar buttons and are updated as the cells' values change. This provides a very easy way to constantly watch the values in a number of cells in different sheets of different workbooks at the same time. Only available for Excel 97 and Excel 2000, as it uses class modules and the extended event model. This update stores cell watch information in defined names in the workbook (instead of a separate sheet) to avoid problems with protected workbooks.

PastePicture.zip (9 April 1999, 34k, 51,833 downloads) Updated - Saves a chart or range as a bitmap or metafile
The userforms in Excel 97 are great, apart from one BIG letdown - you can't put charts on them!  A kludgy workaround has been to export the chart as a gif or jpg, then load it into an Image control using LoadPicture.  That works ok-ish for charts, but it can't handle word art, shapes or other pictures created on the fly.  This file includes code to create a Picture object from whatever is on the clipboard.  Display a chart on a userform is now as easy as a copy/paste.  Thanks to VB MVPs Karl Peterson, Randy Birch and Brad Martinez for their help.  You can paste the image as either a bitmap (better for same-size copies) or a metafile (better when zooming/stretching), whichever best suits your circumstances.  This update includes an example of saving the picture to disk as a bitmap or metafile.

Comdlg32.zip (7 Apr 1999, 7k, 18,259 downloads) Updated to work in XL97 - and under NT4
While the GetOpenFilename VBA function is quite useful, it is limited in what it can do - the file must exist for example. For more flexibility, we can use the Windows common file dialogs.  Thanks to Werner Haussman for suggesting the NT fix.

VBETools.zip (16 January 1999, 29k, 12,732 downloads)
This is a relatively small addin to the Excel 97 VBIDE, adding the standard workbook File menu items to the VBE menu.  With this addin, you can open, close and create new workbooks and modify their file properties directly from the VBE. It also removes the blank lines that Excel adds to the top and bottom of code modules when an Excel 5/95 file is opened.

FindCirc.zip (4 Jan 1999, 22k, 16,094 downloads) Updated to support Excel 97
Has Excel ever told you that it "Cannot resolve circular references"? You've looked in the status bar and seen a cell reference that Excel thinks contains the circular reference? It obviously doesn't and you've spent the next 2 days trying to find it? If that's you, then you need this file. It contains a routine to locate the circular reference for you, tracing back from a cell you select. It shows the complete circular reference path (across multiple sheets and workbooks) and even colours the cells involved. The zip file contains an add-in and the source code. While this update works in Excel 97, you can get similar functionality by showing the "Circular Reference" toolbar (which only shows when you have disabled Iteration and have a circular reference).

NoCloseButton.zip (25 November 1998, 13k, 19,897 downloads)
This is a small demonstration of how to remove the [x] close button from a dialog sheet and userform, using Windows API Calls.  Unfortunately, the user can still press Escape in a dialog sheet (not userform) to close it.

RecAtMark97.exe (5 June 1998, 244k, 11,004 downloads) Self-installing exe.
One of the tools which went missing during the transition from Excel 95's modules to the Excel 97 VBE was the "Record at Mark" feature.  Some of you may have seen an article in January's Microsoft Office Developer magazine purporting to give a Record at Mark solution.  Unfortunately, the routine was little more than an automation of the cut and paste that you have to do manually at the moment and the authors used SendKeys to do it, which is never a good idea.   This alternative (prompted during discussions with my colleagues Bill Manville and John Green) provides a faithful reproduction of the Record at Mark feature found in Excel 5 and 95, using VBA to manipulate the recorded code.

DragDrop.exe (30 April 1998, 451k, 16,473 downloads) Self-installing exe.
This is an example of how to program the TreeView and ListView controls found in the Windows Common Controls OCX, paying particular attention to the drag-and-drop features of these controls.  Requires Excel 97.

CallTree97.exe (1 April 1998, 268k, 14,040 downloads) Self-installing exe.
CallTree.zip (11 March 1998, 72k, 16,243 downloads)
This routine reads all of the modules in selected open workbooks, extracting a complete list of procedures and the calls between them. From this it generates some pivot tables, enabling you to easily identify which procedures call, or are called by another procedure and how many calls there are between the modules. Finally, it displays a procedure flow diagram for the workbook in a tree structure. The zip file contains a single xls file, which is hidden when opened. This update fixes a few problems reported by some users and also handles multi-workbook projects (when referenced using Tools, References). Documentation is included in the zip file.

Indenter.zip (28 Jan 1998, 79k, 13,217 downloads)
Building on Rob Bovey's excellent Code Cleaner, this file adds the option to reconstruct all the indenting in your VBA code, in a similar way to the existing option of stripping the comments and spaces from the modules. The routine handles all legal VBA constructs, including multi-statement lines and line continuations. Note that this file only works with English VBA. This version does not work with Excel 97, but I'm working on it.

Hier_PT.zip (4 October 1997, 120k, 12,985 downloads)
This file demonstrates how to produce a hierarchical set of page fields for a pivot table. The example uses data from the NWind sample database (not required), showing freight charges for each order by country, city and company. The pivot table has page fields for the country, city and company in that order. When the user selects a country, only the cities in that country are shown, and only the companies in the selected city are shown.

TreeList.zip (19 Mar 1997, 7k, 18,421 downloads)
This spreadsheet contains a VBA routine to display a list of parent/child relationships in a tree structure in a list box. The routine assumes that the list is organised such that each branch is navigated to its full extent before moving on to the next branch.

ExptChrt.zip (5 Feb 1997, 11k, 14,561 downloads)
In Excel 97, it is now possible to export charts as gif or jpg images. This spreadsheet shows how to continuously export a chart each x minutes. This could be used, for example, to publish data from a 'live' DDE feed onto an internet or intranet page.

UpdatCht.zip (12 Jan 1997, 10k, 14,219 downloads)
Often when using charts in presentations, many of the charts should have the same Y axis scale. The axis scaling can not be linked directly to a cell, so it has to be updated manually. This workbook demonstrates a different solution, whereby a custom function is used to link a list of charts to a set of data. An OnCalculate routine ensures that the axis scales for all the charts in the list are the same, using the maximum and minimum values from the data set.

QuckSort.zip (11 Jan 1997, 15k, 20,019 downloads)
This file implements a "Quicksort" algorithm in VBA. It is very fast and handles both 1D and 2D arrays.

ComboBox.zip (21 Oct 1996, 7k, 24,680 downloads)
The standard linked edit-list box in Excel is annoying in that the selection in the list box does not reflect what is typed in the edit box. This file uses standard VBA code to simulate a linked edit-list box similar to the combo box in MS Access and the standard Help Index screen. Thanks to Jim Rech for his assistance in perfecting the routine.

GoalSeek.zip (20 Aug 1996, 11k, 17,302 downloads)
The standard goal seek function in Excel is OK, but can not cope with large, complex spreadsheets. This is because it only does a simple recalc during each iteration, and can not cope with circular references. The routine in this file is a simple linear-interpolation goal seek written in VBA. The goal seek calls a separate calculation procedure during each iteration, in which you can include any code you like. For example, you could refresh a pivot table, filter a database, consolidate data, copy and paste etc.

ShowHelp.zip (14 Nov 1996, 5k, 12,055 downloads)
If you try to use a custom help file in Excel 7, you will notice that the Contents and Find buttons bring up the Excel help file, not your custom one. To correct this requires the use of the Windows Help API functions, instead of Application.Help. This file contains a routine to invoke your custom help file using those calls.

XL5HlpID.zip (20 Aug 1996, 42k, 8,033 downloads)
This file lists all of the help IDs for the main Excel 5 help file. You can use these in your own help files to show, for example, the standard Excel help page for Page Setup.

XL7HlpID.zip (20 Aug 1996, 7k, 9,678 downloads)
This file lists all of the help IDs for the main Excel 7 help file.

All of the files available for download are provided as-is, without any warranty or support. All use of these utilities is at your own risk. The files have been tested with Excel versions 5.0c, 7.0a (aka 95), 97, 2000, 2002 and 2003 where appropriate. This does not guarantee that they will work on your computer. You may use and adapt these utilities for your own projects, including commercial applications. I would appreciate being informed if these utilities are used in commercial applications. You may not include these utilities in any shareware or freeware catalogues, books, articles or periodicals without the written permission of the author. If you have any comments about this page, or the files herein, please send an email to Stephen Bullen.

Stephen Bullen
Office Automation Ltd.
40 The Charter Road
Woodford Green
Essex  IG8 9RE
England

stephen@oaltd.co.uk