J-Walk & Associates, Inc.

Attention 64-Bit Excel 2010 Users

I've been working on PUP v7.1, and it's almost ready to go. I've tested it on Excl 2007, 32-bit Excel 2010 Beta (running on Vista), and 64-bit Excel 2010 Beta (running on Windows 7).

Before I release it to the world, I'd like a few other 64-bit Excel users to try it out. If you're interested, send me an email at walkenbach (at) gmail.com.

PUP v7.1 Revisited

About a week ago I made a blog post about updating my PUP v7 add-in so it works with 64-bit Excel. See: PUP v7.1 is coming.

I indicated that it would be fairly easy to update the code. That was based on some preliminary tests I did using Excel 2007, Excel 2010 and 64-bit Excel 2010. But I was wrong. Updating the code will require more work than I had thought.

Today I learned something about VBA: When you install Office 2010, you get VBA 7. And VBA 7 replaces your previous VBA version. I have three versions of Excel installed on my main system (Excel 2003, Excel 2007, and Excel 2010 Beta). So now, all three of them are running VBA 7 (even though the Help - About dialog tells me it's VBA 6.5 in Excel 2003 and 2007).

What this means is that my tests led me astray. I assumed that I could simply add "PtrSafe" to my API function declarations. That solution works (most of the time) only for VBA 7. Earlier versions of VBA don't recognize the PtrSafe keyword. So, in order to keep PUP v7 compatible with Excel 2007 (running VBA 6.5), I'll need to use the vba7 and win64 compiler directives, and provide two sets of API function declarations. It's still not incredibly difficult, but it's a lot more work than I thought it would be.

Fixing Excel’s Dual Monitor Problem

I posted a tip that solves an annoying problem: Dual Monitors and UserForms.

One of the things I'll be doing in PUP v7.1 is to add this fix to all of the UserForms.

PUP v7.1 Is Coming

Several people have pointed out to me that PUP v7 does not work with 64-bit Excel 2010 Beta.

The problem is with the API function calls.

NOTE: I deleted some of this post because the information is wrong.
See: PUP v71. Revisited.

 Fortunately, it's an easy fix. All of the API function declarations need to be modified by adding PtrSafe between Declare and Function. For example, the declaration that starts like this...

Private Declare Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" ...

... must be changed so it starts like this:

Private Declare PtrSafe Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" ...

PUP v7 consists of 87 files, and thirteen of them (including pup7.xlam) need to be modified. 64-bit Excel users can identify these files because you'll get an error message when you try to run the utility contained in that file. If you've purchased PUP v7 with the source code option, you can make the changes yourself, if you're so inclined (make sure you save the file after changing it). Otherwise, just be patient.

I hope to have a new version available within a few weeks a month or so.

The new version (called PUP v7.1) will replace the current version, and it will be compatible with both 32-bit and 64-bit versions of Excel. It will have no other new features, and there will be no charge for current PUP v7 licensed users to upgrade. There's really no reason to upgrade unless you use (or plan to use) the 64-bit version of Excel 2010.

I'm curious... How many of you have tried the 64-bit version of Excel 2010 Beta? Do you plan to? If so, why?

PUP Blowout Sale On Tuesday

Power Utility Pak sales this year have been absolutely dismal. If the current trend continues, this will be the worst year for PUP sales since 1996. In an attempt to turn that around, we're having a GIANT TWO-HOUR BLOWOUT SALE!

Check out these prices:

• PUP v7: $7.77 (normally $40.00)
• PUP v6: $6.66 (normally $39.95)
• The complete VBA source code is available for an additional $20.00

The sale will be on Tuesday, November 3 between the hours of 11:00 am and 1:00 pm U.S. Eastern Time (that's 4:00 pm - 6:00 pm GMT).

Plus, you might win a free book. Three books will be given away. Everyone who orders PUP during this 2-hour period will be entered in a drawing to win a free Excel book. Winners choose their book (subject to availability). Each license ordered = one chance to win. Order 100 licenses, and you're practically guaranteed to win at least one book -- maybe all three. Actually, anyone who orders 100 licenses should just ask me for a book.

On Tuesday, here's where you go to order: Special 2-Hour Sale Order Form.

PUP Customer Names

Today I processed some Power Utility Pak orders, and noticed that three in a row were placed by people named David. So I became curious about the most popular names of people who purchase PUP.

The top 20:

1. John (3.81%)
2. Robert (3.22%)
3. David (2.67%)
4. Michael (2.59%)
5. James (2.14%)
6. Richard (1.63%)
7. Mark (1.46%)
8. William (1.44%)
9. Paul (1.27%)
10. Thomas (1.14%)
11. Charles (1.12%)
12. Peter (0.94%)
13. Steve (0.82%)
14. Gary (0.80%)
15. Tom (0.76%)
16. Jim (0.76%)
17. George (0.75%)
18. Stephen (0.70%)
19. Frank (0.69%)
20. Steven (0.67%)

Notice that these are all male names. The first female appears in the list at #94, Karen -- who happens to be tied with Susan. Sandra, Patricia, and Elizabeth aren't far behind.

Cleaning Up Data

A common spreadsheet task is to clean up inconsistent data. For example, you might need to ensure that all phone numbers are in a common format, email addresses are in lowercase, dates are formatted consistently, and so on.

My PUP add-in contains a few useful tools for data clean-up. Probably the most useful one is Text Tools.

Text Tools has the following options that work with the selected cells:

• Change the case of the text (UPPER CASE, lower case, Proper Case, Sentence case., and tOGGLE cASE).
• Add new characters to the text (at the beginning, at the end, or at a specified position).
• Remove characters from the text by position (from the beginning, from the end, or beginning at a specified position).
• Remove spaces from the text (all extra spaces, leading spaces, trailing spaces, or all spaces).
• Remove characters of a particular type from the text (non-printing characters, alpha characters, non-numeric characters, non-numeric and non-alpha characters, or numeric characters).

Of all the PUP features, I use Text Tools most often.

* * *

What other options for data clean-up?

Rob Adams sent me a link to an on-line tool that some might find helpful: Magic/Replace. You can upload a file (XLS, CSV, or TSV) or simply copy and paste your data. Then, using a row-and-column interface ,adjust one row -- and the other rows change automatically. When your data is cleaned up, save the changes and you'll be notified by email when the file is ready to be downloaded.

I tried it by pasting a 280-row range from and Excel file. One of the columns had city and state names in uppercase (for example, TUCSON, AZ). I wanted to make the city proper case and keep the state abbreviation upper case. I couldn't get it to work. It changed some of the data, but not all of the data. I eventually figured out that the "example row" (which I modified) had a slash character in one of the city names. When I deleted the slash, changing the case seemed to work as it should. I submitted the changes, and I was notified about one minute later that the file was ready for download. The file looked pretty good, except that some of the cells were preceded by a space character. So I fixed it with PUP in about 5 seconds.

Some people might find Magic/Replace useful, but I won't be using it again. One problem is the lack of documentation. You can watch a video demo, but that's it. Also, the changes you can make seem to be fairly simple and it assumes that your data is fairly consistent to start with. For example, if one row has a telephone number in the zip code column, you can't change it. The inability to preview all of your data -- and the lack of undo -- made it kind of a frustrating experience.

Free PUP Source Code

From now through Monday Wednesday.... Purchase a PUP license and get the VBA source code for no additional cost. This applies to:

• PUP v6 - For Excel 2000, 2002, and 2003
• PUP v7 - For Excel 2007

How it works:

Order PUP online, and I will be notified of the transaction. I will follow-up by email and send you information on how to access the VBA code for all of the PUP components.

With access to the PUP VBA code, You'll learn how it works, and you'll pick up dozens of new programming techniques along the way. The PUP source code is an excellent learning tool for VBA programmers of all levels. Even advanced programmers will probably pick up lots of new techniques.

This offer applies to all online orders placed between November 13 through 17 19.

* * *

Update: I'm going to extend this offer through Wednesday, November 19.

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.