Subscribe in a readerDownloads
Acknowledgements
Some of the files on this page are also available at Stephen Bullen's website.
Excel versions
These files should all work in Excel 97, 2000, XP, 2003, 2007 and 2010, unless stated otherwise. For some downloads a dedicated version for Excel 2007/2010 is available.
Donations
The files
| Name of download | Description and download link |
RefTreeAnalyser |
Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be! Now there is the RefTreeAnalyser! |
Name Manager |
The Name Manager An excellent utility to manage defined names in your workbooks. |
FlexFind |
Flexfind eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more. |
Autosafe |
Autosafe.zip (Build 132 , 04 Sep 2011, downloaded: 33902 times) New in Autosafe: Autosafe now also works in the 64 bit version of Excel 2010! Version 3.5 of Autosafe enables use in environments with long paths/filenames. The standard Autosave (note the spelling) utility that ships with Excel just saves workbooks at a set interval, overwriting the file on disk. This is not very convenient if you planned to leave the master file intact and save the changed workbook using a different filename. It also does nothing to simplify recovery of unsaved/changed documents after a system crash. This Autosafe utility creates copies of open workbooks at regular intervals in a separate (user-selectable) directory. It does not overwrite the master file(s), that is up to the user to do, using normal methods. As soon as a workbook is closed the backup copy is deleted from the backup directory. If an abnormal termination of Excel occurs, the backup copies remain on disk, and Autosafe finds them the next time Excel is started and presents recovery options to the user. Autosafe comes with an autoupdate function which checks for updates every week. Includes the following languages: English, Dansk, Deutsch, Español, Français, Indonesia, Italiano, Nederlands, Norsk, Hrvatski, Slovenščina, Korean, Русский (Russian), Lithuanian and Hungarian. The previous version is still available: Autosafe34.zip (13 January 2003, 228k, downloaded: 3981 times) Note that this one shows a nag screen on networked computers. Autosafe does not work on a Mac. |
GoBack |
GoBack.zip (8 February 2005, downloaded: 7095 times)
Version 1.0 build 003.
Sometimes one has to edit a large workbook, with many worksheets. This tiny utility keeps a record of the ranges you have visited and gives you the opportunity to return to previous selections using two hot keys: control-alt-p to go to a previous selection and control-alt-n to go to the next. It also creates a toolbar with a dropdown to select a previous selection and to disable/enable the utility. |
FollowCellPointer |
FollowCellPointer.zip (12 September 2010, downloaded: 3401 times)
Version 1.0 build 007.
A small tool which follows your cell pointer by placing two arrows on top of your sheet pointing to the active cell. Does not change your formatting. |
HeaderFooter |
HeaderFooter.zip (28 January 2008, downloaded: 7150 times) Version 1.0 build 006. Managing headers and footers in Excel can be a drag. This little tool helps you to change headers and footers in your files easily by means of a toolbar that shows dropdown/edit boxes in which you can type the header/footer codes directly or select them from the list of currently present header/footer codes in your file. |
SetupUtility |
SetupUtility.zip (Updated May 5, 2008, downloaded: 9546 times) If you have created a nice add-in (see this article how to do that), a setup utility is an easy way for your users to install your addin. This free utility just needs you to change 4 cells to roll your own setup file. Update: Now automatically removes invalid addins from addin list (courtesy: http://tinyurl.co.uk/zde9). |
UpdateAnAddin |
UpdateAnAddin.xls (Updated February 19, 2007, downloaded: 9515 times) If you have created a nice add-in (see this article how to do that), an updating mechanism is an easy way to ensure your users get any bugfixes you did automatically . This download demonstrates how you can implement this functionality in your addin. |
ShowTableOnUserform |
ShowTableOnUserform.zip (14 May 2008, downloaded: 4768 times) For a project I needed a quick way to display the content of an array to the user. I didn't want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. So I figured I'd put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown. That proved far from easy... See this article on how it works. |
FixLinks2UDF |
FixLinks2UDF.zip (02 June 2008, downloaded: 2642 times) A demo file that handles #Name! errors for workbooks that use UDFs that reside in an add-in. See this article on how that works. |
QueryManager |
QueryManager.zip (Build 017, 23 September 2010, downloaded: 9727 times) This utility has been developed together with Dick Kusleika. It eases the editing of queries and Pivottable connections. One can: 1. Edit the SQL string and the connect string of queries and PivotCaches 2. Add parameters 3. Change the path to the data source All in a single dialog. |
AutosafeVBE |
AutosafeVBE.zip (build 026, 6 Aug 2007, 260k, downloaded: 13982 times) This utility makes backup copies of VBA components to a user-defined directory. It keeps a user selectable number of copies of each component. It thus keeps a number of generations of your code as your work progresses, enabling you to return to a previous copy when things go wrong. Because it just exports the VBA components, it is unobtrusive because this process is relatively fast compared to saving your workbook or document. Excel and Word version included!! |
ExcelVBEMultilineSR |
ExcelVBEMultilineSR.zip (Version 1.0, Build 004, 27 Nov 2008, 634k, downloaded: 4345 times) This utility enables you to do Search and Replace operations
in the Visual Basic Editor of Excel. What is special about this
tool is that you can search for multiple lines of code and replace
with multiple lines of code. |
CopyVBAProject |
CopyVBAProject.zip (Version 1.0, Build 007, 05 Oct 2007, 56k, downloaded: 4549 times) This utility enables you to copy the components from the VBAProject
of workbook A to Workbook B |
ExportVBAProject |
ExportVBAProject.zip (Version 1.0, Build 001, 14 Oct 2011, 24k, downloaded: 491 times) This utility enables you to copy the components from a VBAProject to a single text file. Very useful if you need to compare the VBA code of two Excel files. Use this handy tool to compare differences in the exported files: ExamDiff |
ObjectLister |
Objectlister.zip (Version 1.0, Build 003, 1 October 2008, 56k, downloaded: 6468 times) Lists objects, properties and methods of the selected object and enables you to quickly build code that uses many properties of an object. See the ObjectLister page for more information. |
AutoChrt |
AutoChrt.zip (10 November 2000, 7k, downloaded: 14468 times) Automates the process of creating graphs from database-like datasets, where you need to chart various columns against each other in x-y scatter charts to determine relationships between them. It consists of a sheet where to copy the data into and a sheet that holds the chart and some spinner-buttons to control which data are charted. |
ChartAnEquation |
ChartAnEquation.zip (May 1, 2005, 5K, downloaded: 7603 times) Demonstrates a method to chart a mathematical equation using
just defined names. |
ControlHandler |
ControlHandler.zip (17 June 2005, 26k, downloaded: 6179 times) Demonstrates a method to handle the events for multiple controls on a worksheet using a single class module. See this article for an explanation. |
CatchPasteDemo |
CatchPasteDemo.zip (17 Dec 2007, 21k, downloaded: 3712 times) Demonstrates how to intercept paste operations in a workbook to prevent users from wrecking your validation. The download contains two workbooks; one for Excel 2003 and earlier, the other for Excel 2007 and 2010. See this article for an explanation. |
UndoHandler |
UndoHandler.zip (8 March 2006, 24k, downloaded: 6054 times) Demonstrates a method to enable the user to undo changes made by your VBA code. See this article for an explanation. |
WatchOtherCell |
WatchOtherCell.zip (9 February 2006, 25k, downloaded: 6077 times) This workbook simplifies looking at data in a sheet with a lot of columns. It shows the value of a cell on the same row in a column one can enter in a textbox. This way you can scroll all over the sheet and always (e.g.) have the value of a cell in column BE in view. Start the watcher by opening the file and using its entry in the Tools menu. |
GetARange |
GetARange.zip (4 May 2006, 26k, downloaded: 8351 times) This workbook demonstrates a bug in the VBA Application.InputBox function as described here. The workbook also contains a userform and sample code to work around that bug. |
EditOpenXML |
EditOpenXML.zip (5 September 2011, 26k, downloaded: 3412 times) Wouldn't it be useful to be able to edit the contents of an Office 2007 OpenXML file from within VBA? Well, now you can using this demo file. Find a full description here. |
Arg2Name |
Arg2Name.zip (8 February 2001, 10k, downloaded: 13129 times) This workbook demonstrates a trick to pass (range) arguments to defined name formula's. As published in David Hagers' EEE #9, available from John Walkenbach's web site. See the Excel names page. |
xlMenuFunDict |
xlMenuFunDict.zip (29 Jan 2003, 810k, downloaded: 14916 times) International versions of Office have the menu system in their local language. Also the Excel worksheet functions are (mostly) listed in local language. This complicates communication with the users with such a version. To aid in this process a utility has been devised that creates and shows a translation list of the Excel built-in command bars and controls and the Excel worksheet functions. This workbook can also list command bars of other Office software packages. It thus enables the international user who is using a different language version of Excel to quickly find translations for sequences of menu commands and function names. |
Comments
Showing last 8 comments of 164 in total (Show All Comments):Comment by: Henry Lee (10/13/2011 8:05:06 AM)Hi Jan,
I just downloaded your utility ExportVBAProject. When I tried to execute it, I get Error 438: Object doesn't support this property or method in modMenu.MenuHandler. Debugging the code, it turns out the problem is in the subroutine InitForm() for ufSelectWB. The reference "oAddin.IsOpen" is valid only for Excel 2010 and I was using Excel 2007. So you should add this requirement to the description for this utility.
Regards,
Henry Lee
Comment by: Jan Karel Pieterse (10/14/2011 1:59:12 AM)HI Henry,
Well spotted. I have updated the code so it works for all versions: Excel 2003 up to Excel 2010.
Comment by: Donald Nelson (11/28/2011 10:08:06 AM)I am new to VB and programming. I can write add-ins and create menus and toolboxes, wrote macros to edit macros, add-ins to customize workbook functionality and more, so you might say I'm at an intermediate level with VB. Is there any way I could see all or some of the NameManager code. I do not want to sell or redistribute your code I just want to see how you sorted the names by external/errors/filter/etc... also when I try to write a macro to delete some names it comes up with errors. How do you handle the errors.
Recommend changing (or allowing user to select where) the menu to be available in the actual names menu of excel in 2003. Insert/Name/Name Manager. Also, I needed to delete names that do not refer to anything. In a post you mentioned to be careful not to delete hidden names that excel uses. Could you provide a list of names that users should not delete? Is it possible to create a menu that snaps to the side of the application similar to how the insert image or thesaurus looks? At work I have multiple monitors but at home I do not. It is semi-inconvenient to view this form window and the workbook at the same time with one monitor.
I am really interested in learning more. Are there any books aside from Excel 2007 VBA Programming For Dummies or any other ways to understand. I visit forums but they don't say how the code really works just try this or that. I would like to understand not just use their suggestions. Should I learn more than VB for manipulating MS Office?
Comment by: Jan Karel Pieterse (11/29/2011 7:02:50 AM)Hi Donald,
Thanks for the suggestions.
Sometimes names cannot be deleted from VBA, these have somehow been corrupted. In newer versions of Excel you can remove them by using the Name Manager dialog that's built-in.
I cannot share the Name Manager code, it is code I developed together with Charles Williams. Hundreds, if not thousands of man-hours have gone into it. Why? To make it absolutely robust in all circumstances. There are many quirks we had to work around to get this all working properly.
You can turn off the system names option so the system names of Excel are not shown and hence not removed.
This function tells you if a name is a system name:
If sName Like "*_FilterDatabase" Then
IsSysName = True
Exit Function
End If
If sName Like "*Print_Area" Then
IsSysName = True
Exit Function
End If
If sName Like "*Print_Titles" Then
IsSysName = True
End If
If sName Like "*.wvu.*" Then
IsSysName = True
End If
If sName Like "*wrn.*" Then
IsSysName = True
End If
If sName Like "*!Criteria" Then
IsSysName = True
End If
End Function
Mind you, it does not know about names inserted by queries.
Comment by: Barry McFarlane (1/5/2012 5:31:00 PM)Having lost several hours of Excel work due to not saving the file correctly, I did a search for autosave tools and found AutoSafe. I have installed this tool and initial testing is good but it would be more useful to me if it had the following options:
1. always make a backup copy at the nominated interval irrespective of activity or skipped saves.
2. option to keep the backup file in the backup directory even if the file is saved correctly (ie don't move it to the recycle bin). The rationale for this is that I often edit files that are on networked drives and these files occasionally get trashed by other people. It would be easier to recover my work by only having to look in the backup directory instead of searching through the recycle bin.
Barry
Comment by: Jan Karel Pieterse (1/9/2012 1:07:32 AM)Hi Barry,
Version 3.2 has option 1 by default.
For option 2, I'd have to create a special version for you. Please send me an email if you want me to quote how much that would cost.
Comment by: andre (1/19/2012 4:09:17 PM)Hi Jan Karel
Does the ChartAnEquation work in excel 2007. I get an error when Trying to use it that says a furmla has invalid references.
Rgds
Andre
Comment by: Jan Karel Pieterse (1/20/2012 8:39:32 AM)Hi Andre,
It did for me some time ago!
What happens if you hit control+shift+alt+F9?
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.