J-Walk & Associates, Inc.

Get The PUP Functions

My Power Utility Pak add-in includes 53 new worksheet functions that you can use in your formulas. Here's the best part (for you): You can use these functions even if you haven't purchased a license for PUP.

From the PUP license agreement:

The PUP FUNCTION LIBRARY component of PUP copies VBA macros to an Excel workbook file. The PUP license allows such workbooks to be distributed to others. However, the VBA macros may not be included as part of any other commercial product.

Here's how to get the PUP worksheet functions:

1. Download the trial version of PUP. If you use Excel 2007, get PUP v7. Otherwise, get PUP v6.
2. Install the add-in. You can use the trial version for for 30 days.
3. Create an empty workbook.
4. Access the PUP Function Library dialog box. It's in the Formula Tools menu (or Ribbon control in Excel 2007).
   
   
   
5. Click the All button, to select all functions.
6. Click the Add Selected button to add the selected functions to your workbook.

All of the functions will be available in the workbook you created in Step 3. To use the functions in other workbooks, just copy the VBA module named modPUPFunctions to your workbook. Or, you can just copy and paste the functions that you use. Although the trial version of PUP expires after 30 days, the functions will work forever.

Using PUP to add specific functions to individual workbooks is a lot easier, but the method described here also works.

Keep in mind that if your formulas use any of the PUP functions, you most open the workbook with macros enabled. If you use Excel 2007, you must save your workbook in something other than XLSX format (for example, XLSM or XLS format).

Free Upgrades?

Debra Dalgleish has a blog entry today called Free Upgrades For Life. She posted a link to an Excel workbook that compares free upgrades vs. non-free upgrades. Debra wrote:

I can't imagine why anyone would offer free software upgrades for life. Certainly, if there are minor upgrades or patches to the current version, or a major upgrade is released shortly after a purchase, then I would expect to get those at no charge. Otherwise, if I want to enjoy the new features that a developer has worked hard to create, I'd pay for the upgrade.

Well, I happen to offer free upgrades for PUP. That product was born back in 1994, and I knew it was full of bugs. I didn't like the idea of charging people for my mistakes, so I decided to provide free upgrades for life. Back then I never would have dreamed that PUP would still be around 14 years later, so I didn't give it much thought.

At one point, I added a $5 upgrade processing fee, fearful that it would lead to a massive uprising among PUP customers. In fact, only one person has ever complained about the $5 fee.

As it turns out, I think offering free upgrades was a good decision. I have no idea what the actual upgrade rate is, but it seems to be pretty high -- probably much higher than it would be without free upgrades.

Favorite PUP Features?

I'm always curious to know which features in PUP are used most often.

In the next version, I think I'll add some type of automated counter that increments every time a PUP utility is accessed. Then I could invite users to share their counters.

The PUP features that I use most frequently are:

• PUP Bookmarks -- it's probably the most useful thing I've ever written.
• Text Tools -- I tend to import a lot of data, and this utility is really helpful in cleaning up the data

• View Active Workbook's Folder -- When I'm working on a file, I often need to access other files in the same directory. This utility is the fastest way to get to those files.
• Save Sheet As HTML -- It's nice to be able to create an unbloated HTML table.
• Chart Data Labeler -- Every chart user needs to do this at one time or another. Amazingly, Excel still doesn't allow you to specify an arbitrary range for data labels.
• Workbook Link Finder -- Really useful when someone sends me a workbook with lots of links.
• Worksheet Map -- Useful for getting an idea of how an unfamiliar worksheet is laid out.

What are your favorites?

Why PUP Is Modular

I got an email this morning about PUP:

This is more a question of philosophy than anything else: is there any particular reason (other than size) that you decided to assemble the PUP utility as a number of individual add-ins, rather than one large one?

My reply:

The primary reason was size. It would be a HUGE add-in and gigantic memory hog if it were all in one file. And besides, most users only use a few of the utilities. Plus, because it's modular I can provide bug fixes and updates to a single module rather than requiring the user to download the entire file again. And finally, it's just much easier to work on small pieces.

The original version of PUP, developed back in 1994, was contained in a single file. But I added so many new features to PUP v2, that I had to figure out a way to use less memory. So I wrote code in the main add-in file that loads the other add-ins when needed. This memory management was improved in PUP v5, with a method to let the user specify the maximum number of PUP utilities to remain in memory (the default is three). The utility files are closed on a first-in-first-out basis. When a new utility is loaded (and the maximum number would be exceeded), the "oldest" one is unloaded.

PUP v7 Currency Conversion Fixed!

If you use PUP v7, you can download an update for the Currency Conversion utility. The ability to refresh the rates has been broken for quite a while, and today I figured out how to fix it.