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,542 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,501 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,922 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,689 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,609 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,030 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,941 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,215 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,238 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,772 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,386 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,984 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,513 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,525 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,249 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,115 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,522 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,155 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,242 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,935 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,479 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,917 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,297 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,052 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,835 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,506 downloads) For Office 2000-2003. Updated to Version 3.5
IndenterVB6.exe v3.5 (10
February 2005,
260k, 62,822 downloads) For Visual Basic 6. Updated to Version
3.5
Indenter97.exe
v3.5 (10 February 2005, 381k, 26,566 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,415 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,319 downloads)
This COM Addin adds a number of features to the Office VBE, including:
More information and screen shots can be found on the VBE Tools page.
RecordatMark.exe
v1.1
(13 October 2004, 210k, 8,017 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,806 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:
Sub Auto_Start() | is run as soon as FrontPage starts, before any webs have been opened. |
Sub Auto_AfterStart() | is run after FrontPage has finished it's Startup processing. This routine should be used if you intend to automatically Quit FrontPage as soon as your custom code has run (e.g. if you're running scheduled publishing). |
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,682 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,308 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,147 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,326 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,196 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,230 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,349 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,868 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,278 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,747 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,115 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,916 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,020 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,492 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,060 downloads) Self-installing
exe.
CallTree.zip (11 March 1998, 72k,
16,261 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,235 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, 13,003 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,439 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,581 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,237 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,037 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,699 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,321 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,072 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,048 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,695 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