PTS Blog

Excel is a mighty powerful application, and yet, there are any number of utilities available to extend its capabilities. I’m sure everyone has their favorites, and here I’m going to talk about mine. My reliance on these utilities is as a developer of Excel applications, that is, solutions that combine add-ins, templates, and regular workbook to accomplish specific objectives within specific operating parameters. My favorite utilities lean toward developer tools, rather than worksheet productivity tools. This is an incomplete list: there must be hundreds of worthy utilities written by dozens of developers, but these are the ones I have tried and kept. If you think I’ve left out an important utility, tell me about it in the comments.

Two of my favorite tools were developed by fellow Microsoft Excel MVP Jan Karel Pieterse. He has a variety of utilities on his web site, JKP Application Development Services, as well as a diverse collection of articles and tutorials. I use Jan Karel’s Name Manager and AutoSafe. Jan Karel has offered me a percentage of sales on these two utilities, but that won’t amount to much, because they are free.

Name Manager

The Name Manager is a high-powered tool for working with all of the Names in your workbooks. You can view all names, or filter by a number of conditions. You can make Names hidden or visible, local to the worksheet or global to the workbook. You can add and delete Names, and see what ranges they refer to. Especially powerful is the ability to work on many Names at once. This utility can save a developer many hours.

AutoSafe

AutoSafe is what Microsoft was thinking when they implemented Autosave. AutoSafe saves a copy of your open workbooks in a directory you specify, without overwriting the workbooks. If Excel shuts down improperly, AutoSafe offers to restore the files in use prior to the crash. When a new copy is saved, the existing copy is removed to the recycle bin. In this way, you can maintain an informal version history in the recycle bin, as well as track your activities. This utility has saved me many times.

Microsoft Excel MVP and author Rob Bovey, of Application Professionals, offers a handful of free utilities. I use Rob’s Chart Labeler and VBA Code Cleaner utilities.

Chart Labeler

Rob calls this the XY Chart Labeler, but it really works for any chart that accommodates data labels on its plotted series. Excel allows you to label points with the series name, category labels (or X values), or Y values, and once you’ve added labels, it allows you to customize those labels. But for some reason Excel lacks the capability to apply a set of labels from the worksheet to a series on the chart. The Chart Labeler addresses this shortcoming, allowing you to apply custom labels one point at a time, or one series at a time. The labels on the points remain linked to the cells containing the labels, so the chart labels change when the labels in the cells are changed. The Chart Labeler also allows fine positioning of one or a series of labels. I’ve heard that this is the most downloaded Excel utility, and I’m not surprised. It’s been around since VBA was introduced to Excel, and it’s useful for anyone from casual users to hard core developers.

VBA Code Cleaner

Rob says it best:

“During the process of creating VBA programs a lot of junk code builds up in your files. If you don’t clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.”

Microsoft Excel MVP and author Stephen Bullen offers many example workbooks and free utilities on his Office Automation web site (Stephen and Rob have collaborated on several books). I use Stephen’s Smart Indenter and VBE Tools utilities when I’m developing in VBA.

The Smart Indenter restores an orderly appearance of your code modules by indenting each line. The indentation style is highly customizable. I use this very frequently while writing code, and often the first thing I do when I receive a workbook from someone else is run the Smart Indenter on its code. The Smart Indenter has versions for VBA and for VB6.

VBE Tools provides a set of enhancements to the Visual Basic Environment. It adds a toolbar that shows the size of the active code module and that adds “Nudge” buttons that provide fine positioning control of objects on a user form. It also wraps the location of files in the References dialog, so you can actually see the entire path and file name of the reference. This feature materialized after Stephen heard me whining about the truncated reference file name problem.

John Walkenbach, Excel MVP and author of dozens of spreadsheet books, has an extensive website with tips for developers and for regular users. “Mr Spreadsheet” also hosts the very popular non-Excel J-Walk Blog, and he has a number of utilities on his site.

J-Walk Chart Tools includes a labeling feature similar to Rob’s (above). It also includes features that export charts as image files, convert charts to embedded pictures, and create a report detailing one or all charts on a sheet.

Power Utility Pak is an extensive set of general-purpose tools that make working in Excel much easier. PUP includes tools for Formatting, Formulas, Charts, Ranges, Worksheets, Workbooks, and much more. This is the only Excel utility listed here with a registration fee, but the fee is nominal, and includes free upgrades for life. For an even smaller nominal fee, the user gains access to the VBA code behind the utilities, for educational purposes only (of course).

ASAP Utilities (”As Soon As Possible”) by Bastien Mensink offers a broad set of tools that enhance your productivity in Excel. It is similar in scope to the Power Utility Pak, but remarkably there is not a lot of overlap between them. When my work was primarily as a regular Excel user, I had both utilities installed, and used them about evenly. I find them less useful now as a developer, but both are worth mentioning here.

A utility I use a great deal as a developer is MZ Tools, written by Microsoft Visual Developer MVP Carlos Quintero. There are free VBA and VB6 versions as well as commercial .Net versions. MZ Tools provides many features that facilitate writing, documenting, and debugging code, and managing VB projects. I don’t even know all the features of MZ Tools; I discover new ones all the time.

For packaging my projects into standard installation executable files, I use Inno Setup by Jordan Russell. This standalone utility uses a versatile script to compile your files into a single EXE file, for simple and flawless installation. Inno places your files into a predetermined or user-defined directory, sets any registry keys, and adds shortcuts during installation, and cleans up after itself when uninstalling programs.

There are some utilities that I don’t use frequently, but I need them on occasion and recommend them to users with specific problems.

FindLinks by Excel MVP Bill Manville helps to find and fix those stubborn links in your workbook.

FlexFind is another handy utility from Jan Karel Pieterse (see above). This utility helps find and replace text throughout the entire workbook, in headers, footers, chart titles, and other places normally out of reach to the Edit menu.

Excel MVP Andy Pope hosts a number of utilities, as well as lots of neat Excel tricks. I use his Button Editor frequently when designing user interfaces.

I may as well mention a few non-Excel utilities I also use frequently.

IrfanView is an outstanding and free image editing utility.

SnagIt by TechSmith is a nicely made program, which can capture images or text from windows or screeen regions. TechSmith also makes Camtasia, which I’ve never used, but which is popular for creating and editing video files. SnagIt and Camtasia are commercial products.

For converting workbooks and other documents to PDF files, there must be dozens of free utilities. I use PDFCreator, which is unique in that it can be controlled using VBA (see Ken Puls’ ExcelGuru site for programming hints), and PrimoPDF, which has a very nice save-as interface.

For FTP file transfers, open source program FileZilla is the best free alternative I’ve come across.