Most Useful Add-ins and User Tools
These tools are free to download and use, although you will need admin rights on your PC to install some of them.
|MZ-Tools has a single goal: To make your everyday
programming life easier. * Highly Recommended
MZ-Tools allows you to write code, find code, design your forms, document your applications, and review their quality much faster than you do it today. It saves you valuable time each and every time you use it. If you use VBA then you really should get this add in. It can save you many hours of work. Best of all it is 100% free, although you will need admin rights to install.
|Listed below are a group of Excel utilities
created by MVP Rob Bovey over the years and provided free of charge to the
Excel community. These are not quick hacks, but professionally developed
applications used by thousands of people around the world. The 'Excel
Utilities' addin includes a very handy sheet password cracker and the VBA
Code Cleaner addin is essential for anyone who write VBA Code. Highly
VBA Code Cleaner (New Version)
* Highly Recommended
|Mouse scroll wheel in the Visual Basic
Editor not working? You need "Freewheel"!
* Highly Recommended
What is FreeWheel? FreeWheel provides mouse wheel support for
applications that do not directly support the IntelliPoint mouse wheel. It
runs on Windows 9x or Windows NT as a tray icon, which you can hide if you
dislike clutter in your tray.
All the following tools are from: Office Automation: (http://www.oaltd.co.uk/MVP/Default.htm) (MVP)
* Highly Recommended
To look for links from a workbook, open or install the add-in FINDLINK.XLA, activate the workbook and use Tools / Find Links. It will ask you for a text string to look for. You can specify the name of a specific workbook (e.g. "SOURCE.XLS") or just ".XLS" to find references to any .XLS file. You can even enter "#REF" to find any names which refer to deleted ranges or a range name to find where that named range is used. Optionally the utility will list the occurrences, list and delete (replacing with values where appropriate), or ask you what to do with each occurrence. Version 8.8 handles links in URLs, which have / rather than \ in the path. This is a great addin - I use it all the time.
- Updated to Build 530
* Highly Recommended
If you are in need of a utility to manage defined names in your workbooks, this one is a must-have. List all names in your active workbook. Filter them using 14 filters, e.g. "With external references", "With errors", Hidden, Visible. Show just names that contain a substring. Show just names unused in worksheet cells. Edit them in a simple dialog or make a list, edit the list and update all names in one go. Delete, hide, unhide selected names with a single mouse click. Created in collaboration with Charles Williams, www.decisionmodels.com and Matthew Henson (firstname.lastname@example.org) who ensured the utility works on Mac Excel too. Version 3.2 of the Name Manager in now available. Changes since version 3.1 include: sizable form (including minimise/maximise buttons), moving of splits, sorting of names and RefersTo formulas, new add name dialog, new filter: active sheet, shortcut key (default control-shift-n) to start Name Manager. Mac users, please note: version 3.2 has some problems in the Mac environment that we are still trying to solve. For your convenience, Name Manager 2.3 is still available
(27 January 2005, 264k, 18350 downloads) - Updated to v5.1, build 531
Flexfind eases searching throughout an entire workbook. Enter a string to search for and get a list of all found locations. Click on an entry to go there. Select workbook, sheet or range searching. Replace with another string in the found items you select. At your wish, get a confirmation for each occurrence found (also within one cell). MAC enabled (thanks to Matthew Henson). Ever needed to replace a company name in the title of 25 charts? Or replace just the third occurrence of a cell address in some cells' formulas? Or change a part of the printheader of 6 sheets? Now there is Flexfind version 5, which enables Search and Replace in all these items and more!!! Added in 5.1: S&R capability in Pivottables that are using external data (CommandText and connection string)
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!!
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. >
- Updated to v.3.3 with a fix for a serious bug regarding
userforms in XL2000
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. This utility is freeware only for individual (private) users. Companies and Network Administrators are invited to contact the author for a commercial (network enabled) version. Includes the following languages: English, Dansk, Deutsch, Espańol, Français, Italiano, Nederlands, Norsk.
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.
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.
* Highly Recommended
BtnFaces is an add-in that displays the command bar button control faces built into Excel and their associated FaceID numbers. Developers can use the FaceIDs to add faces to their own command bar button controls. When the add-in opens it adds the Button Faces menu item to the bottom of the Tools menu. This opens a toolbar that displays Excel's button faces in palettes of 100, along with navigation controls to move among the palettes. The FaceID of a button face appears as a tool tip when the mouse pointer is briefly hovered over a button. Clicking a button does nothing as the purpose of the add-in is only to show FaceIDs.
If you're wondering how this is different from John Walkenbach's venerable FaceID Identifier Add-in it is mainly that this add-in displays a dynamic picture on a toolbar of the actual button faces build into the version of Excel you're using. John's utility presents a picture of the button faces in Excel 97. There are about 3,500 faces in Excel 97; Excel 2003 has more than 10,000 (many blank however). John's utility served me well for many years, but when I noticed that in Excel 2003 Microsoft changed the face associated with a FaceID I was using (as well as the look of button faces in general) I knew I needed a way to see the actual button faces dynamically.
RemoveMenus is a utility for removing custom menus created by the Excel 5/95 menu editor from workbooks. When a workbook is converted to Excel 97/2000 format without first removing these menus there is no way to do so in Excel 97/2000. Converting back to Excel 5/95 format may not be a good option as Excel 97/2000 specific enhancements will be lost. Use File, Open to load the add-in. "Remove Menu Editor Edits" on the Tools menu will start it. The utility will work on closed workbooks only.
This bit of wizardry demonstrates how to call the Windows API to show the standard “browse for folder” dialog. The technique demonstrates how to use callback functions with Excel 97 and Excel 2000 in order to pre-select the initial folder. See the “Discussion” sheet in the workbook for a more detailed explanation. Version 2.0 adds support for the Windows 2000 “new style” browse dialog as well as for centring the dialog on screen.
- Updated * Recommended
- Nice password cracker supplied.
This self-installing addin provides a number of useful Excel utilities. As opposed to hundreds of rarely-used but space-taking items, this is a set of 30 routines that Rob uses on a daily basis in his Excel development efforts. The routines supplied in the addin greatly simplify the maintenance of defined names, worksheets and the application settings and also include some nifty selection tools. New features in version 2.0 include sizing the Name box (to the left of the formula bar), updated versions of the Locate Specific Number Format and Move and Size Selected Object features, a new toolbar, one-click access buttons for commonly-used dialogs, a new Help file and numerous bug fixes.
A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler provides this feature for Excel. Once the data labels are applied using the Add Labels utility, the Move Labels utility can be used to move the labels in any direction in 1 point increments. The Manual Labeler allows you to apply labels to individual data points within your chart. Note that the routine works just as well with most chart types.
* Highly Recommended
During the process of creating VBA programs, a lot of "junk" builds up in your files. If these files aren't cleaned periodically you will start getting strange problems caused by this extra baggage. Cleaning a project involves saving all of it's VBComponents out to text files, deleting the components, then importing the components back from the text files.
The latest version of the VBA Code Cleaner provides a number of additional capabilities, including the ability to strip all comments and blank lines from your code, the ability to retain the exported code files, and the ability to choose the locations to which backup files and exported code files are saved. The VBA Code Cleaner also provides the ability to save all settings for each project you clean so that next time you clean a project the code cleaner will be preset for it.
This file utilizes a method for creating, storing and calculating formulas in cell comments. One major advantage to this procedure is that these formulas do not recalculate when a workbook is opened or closed.
One of the more arduous tasks of developing in Excel is identifying the CommandBar name, Control ID and Face ID that your application will use to create its own menu structure, or to modify Excel's own menu structure. The CBList addin greatly simplifies the chore by creating tables of all the commandbars and controls in Excel with their names and IDs and a table of all the built-in toolbar button faces, showing the button image and its ID in an easily-browsable form.
Excel 2002's Task Pane can be set to appear automatically when Excel starts up – or it can be switched off, if you prefer. The “Show at startup” checkbox is situated at the bottom of the New Document Task Pane. The Task Pane is designed to display when Excel starts and then be dismissed when you open or create a workbook. Unfortunately, when add-ins are present, Excel fails to display the Task Pane even if the checkbox is checked. Microsoft introduced a Registry hack to help with this problem but it prevents the Task Pane from closing after opening or creating a workbook. Of course some prefer the Task Pane remain displayed, some prefer it be dismissed after opening or creating a workbook; others do not want it to appear when Excel starts, and there are those who want to see as little of it as possible.
The Task Pane Controller add-in for Excel was designed to help you control the Task Pane without the need to manually hack the Registry or determine which options need to be turned on/off to suit your needs. It's simply a matter of selecting whether you want to see the Task Pane when Excel starts and whether the task pane disappears automatically when you open or create a new document.