J-Walk & Associates, Inc.

Spreadsheet Cartoon

A person who asked to remain anonymous sent me this cartoon.

He suggested that "Jay" is short for J-Walk, and the cartoon is actually about me. It's unlikely because I have a different hairstyle, I don't have a briefcase, and I don't make house calls.

I wasn't familiar with Working Daze. I read some more, and Jay is a regular character in the strip, which has been around since at least 2001.

Annoying Window Behavior

You know what's annoying about Excel?

Assume that you have the VB Editor window open. You click Excel' minimize button to minimize the app to get it out of the way. Then you see the VB Editor window, and realize that there's no need for it to be open. So you close it -- and Excel's window pops back up!

Yeah, as far as annoyances go, it's a minor thing. But it's been going on since Excel 5. With every new version, I keep hoping it will be fixed, but it never is. I can't think of a single reason why closing the VB Editor window should restore Excel's window.

What little Excel quirks do you find annoying?

44 Excel Blogs

According to my count, there are now 44 English language blogs that deal with Excel. You can view the list, and see the latest posts here: Excel Blog Headlines.

I also provide an OPML file that you can download.

What's an OPML file?

It's an XML file that contains feed details, and can be imported by most RSS aggregators. The net effect is that you can subscribe to all of these Excel blogs with just a few mouse clicks.

What's a RSS aggregator?

It's software that makes it very easy to keep up with dozens (or hundreds) of blogs. If you don't use an RSS aggregator, you really should. My favorite is Google Reader, a web-based aggregator (requires an account with Google).

To import these 44 Excel blog feeds (plus 20 additional feeds for the blogs that provide a comments feed) into Google Reader, first download the OPML file. Then navigate to Google Reader choose Manage Your Subscriptions, and then Import/Export. Browse for the OPML file, and the subscriptions will be added. Other RSS aggregators provide the same functionality.

Microsoft Office Is Obsolete?

Somebody named Joe Wilcox declares: Microsoft Office is obsolete, or soon will be.

This month's Office 2010 retail pricing announcement and ongoing discounts for Office 2007 Home and Student are Microsoft's tacit acknowledgment that the productivity suite isn't as valuable as it once was. Office is tracking a course of unplanned obsolescence and the inevitable end shared by oh-so many other products: Commoditization.

Or, maybe lower prices can be explained by competition.

I'll ask upfront: Do you really need Microsoft Office on a daily basis? Is Office vital to your work day? Do you use it at home? If you use it at work, how often? If you use it at home or for college, how often? Please respond in comments.

My answers are easy. I don't use Office at all.

Well then, I guess we can conclude that nobody uses it.

And then, he asks another stupid question:

Word processing reached commodity status years ago, as more applications incorporated the basic formatting features most people use more than 90 percent of the time. No external wordprocessing program is required to blog, e-mail, instant message, tweet or post to social networks like Facebook. Be honest, how much of the writing you regularly do requires a dedicated wordprocessor?

That's right, Joe. Everybody's writing is limited to 140-character tweets.

And the he moves on to Excel:

What is Excel or any spreadsheet really necessary for? Sure, lots of business people use spreadsheets for data analysis, but what is the need for consumers or even small business owners? Many financial products or services, like Quicken or Quickbooks, put a friendly face on spreadsheets;

And people actually take this guy seriously?

Excel Version Poll

This is mainly a test to see if PollDaddy polls work with my blog software.

Analyzing Free Money With A Pivot Table

I've been an Amazon Affiliate for many years. When I link to an item at amazon.com, I include my affiliate ID in the URL. Then, if anyone buys something, I get a piece of the action. For example, all my books have links to Amazon. Plus, I usually use my Amazon ID when I link to items from my other blog (that accounts for most of the non-book items).

Today I ran an Amazon report for the year 2009. It shows every sale made with my affiliate ID, with all of the backup data (except information about the buyer). It occurred to me that the report is a perfect candidate for a pivot table. So I imported it into Excel and create a few pivot tables. I made it available as a download, for people who want to learn pivot tables, but don't have access to a good set of data to play around with. See: Pivot Table Demo Workbook.

If you're curious, my total Amazon affiliate income for last year was $2,524.84. That's almost $7 per day! Some people make a living from it, but I just view it as free money that pays my Web hosting bills. Here are the top items sold via my links:

Blogs As Range References

When column references go up to XFD, you can spell things in formulas. Here are a few Excel blogs, spelled out in valid Excel 2007 formulas:

=SUM(DAI:LYD,OS:EOF,EX:CEL)

=SUM(CON:TEX,TU:RES,BL:OG)

=SUM(BA:CON,BI:TS)

=SUM(NEW:TON,EX:CEL,BA:CH)

=SUM(PT:SB,L:OG)

=SUM(S:PR,E:AD,SH:EET,P:AGE,BL:OG)

Challenge: Write a macro that accepts a text string, and creates formulas like these automatically.

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.

Detect And Repair

Office 2003 (I think) introduced a feature on the Help menu called "Detect and Repair." If an Office app is not working correctly, this command will try to fix it. I've recommended it to several people, and in just about every case it worked.

In Office 2007, the command is available in the Resources tab of the Excel Options dialog box.

Apparently, this useful tool has been removed from Office 2010. I could not find a command or button to diagnose problems. But I did find this in the Help system:

Maybe it's just a joke, added by a technical writer.

New Version Of Bible Is Available

About four years ago, I found a text file with the complete King James Version of the Bible. I wrote a few macros and dumped it all into an Excel workbook. Each book is on a separate worksheet, and each verse is in a separate cell. Then I added a bunch of stuff, and posted it. A lot of people downloaded it.

Today I spent some time and updated it for Excel 2007 and 2010. I added a few new features, including a Ribbon Interface.

If you'd like to take a look, download it here: King James Bible.

2009 In Review

Dick Kusleika did a 2009 in Review post, so I'm obligated to imitate him.

Here's an overview of the year's stats:

The average visitor spends 2 minutes and 18 seconds here, and views 2.82 pages.

The top countries are: U.S., United Kingdom, Canada, India, Australia, Germany, Netherlands, France, and Singapore.

In terms of page views, here are the top-10 individual documents at this site for 2009:

1. Spreadsheet Protection FAQ
2. Referring To Ranges In Your VBA Code
3. Cell Counting Techniques
4. Employee Time Sheet
5. A Formulas To Calculate A Ratio
6. Creating A "Megaformula"
7. Alternatives To Nested IF Functions
8. Comparing Two Lists With Conditional Formatting
9. Protecting Cells, Sheets, Workbooks, and Files
10. Excel Easter Eggs

Here's a chart of the number of page views, by day:

The cyclical dips indicate weekends. That big spike was on August 11-13. Page views were about double during that period, and the extra traffic came to view that Megaformula page. Oddly, there was no major referrer for those three days, so it's possible that the link was in a printed article.

That low point was on April 9. Either my site was down most of the day, or Google Analytics was down.

Here's a chart that shows traffic by source:

Surprisingly, Internet Explorer is still the most popular browser (64%). It's followed by Firefox (28%) and Chrome (4%).

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?

Equation Editor

Excel 2010 will feature a new Equation Editor. That means we can can reproduce classic calculus jokes like this: