J-Walk & Associates, Inc.

DataPig’s Book Is #1 At Amazon

At Amazon, I searched the book category for Excel 2010, and sorted the results by Bestselling. The first one listed is an Office 2010 book, so that doesn't count. Next on the list is Excel Dashboards and Reports, by Mike Alexander and some other guy.

I'm looking forward to getting a copy of that. Maybe someone at DataPig Technologies will send me one.

More Ribbon-Based Apps

Today I installed two apps from Windows Live Essentials (beta): Mail (an email client) and Writer (a blog posting app). The apps in this product all sport a new Ribbon interface. I was familiar with both of these apps, so I was curious to see how the Ribbon improves them.

In my opinion, neither of these products was really in need of a new interface. They worked fine, and there was no hint of the menu/toolbar overload problem that plagued the pre-2007 Office apps. So, this seems to be a case of adding a Ribbon for the sake of adding a Ribbon.

Overall, I'd say that usability was not improved at all. In fact, both of these apps now seem more cluttered, and I'm forced to look at icons that I didn't have to look at before.  And, apart from the Quick Access Toolbar, no customization is allowed. I also found that some of my old keyboard shortcuts no longer work -- but maybe that will be fixed in the final version.

As much as I like the Ribbon UI in Office, the changes I see in these two apps add nothing at all.

Windows Live Essentials is free, so there's no basis for complaining to Microsoft.

Update: It's even worse. The main reason I've been using Windows Live Mail (and previously Outlook Express) is that it's very easy to insert boilerplate text into an email message. But guess what? That feature has been removed from the Mail app in Windows Live Essentials. I can understand why Microsoft would do update with no new features so they can implement the Ribbon UI. But why would they remove features? Fortunately, Windows Restore got rid of this mess, and I'm back to a version that's usable and not so ugly.

A Break From Excel

After finishing up the last round of Excel 2010 books, I took a much-needed break from Excel. That explains the lack of recent blog posts.

For some reason, I got all excited about Windows desktop gadgets. That's kept me out of trouble for the past few weeks. I made two gadgets:

Office Recent Files - This gadget isplays a handy list of recent files for Excel, Word, and PowerPoint. Requires Office 2007 or Office 2010. I couldn't figure out how to read the Windows registry using Javascript, so I ended up using VBScript almost exclusively. I was surprised to see how nicely these two scripting languages work together.

SLAG - Simple Little Audio Gadget - I found a few music-playing gadgets, but I don't like any of them. So I made my own. It uses the Windows Media Player database, and the code is written in Javascript.

If you're running Vista or Windows 7, give them a try and let me know how they work. Gadgets aren't compiled, so you can see exactly how they are written.

Excel 2010 Book List Updated

The list of my Excel 2010 books is now up-to-date, with cover images and links to Amazon.

You'll notice a new one: Excel Dashboards and Reports. It's really Mike Alexander's book but I contributed a few chapters and got listed as a co-author with the Data Pig.

The publisher and I still haven't decided if a new edition of Excel Charts will be added to the list.

Excel Hero

I follow every English language Excel blog on the planet. There are lots of good ones, but the one I like best is Daniel Ferry's Excel Hero. His downloads are simply awesome.

From his "About" section:

I cut my Excel teeth on very early versions in the early 1990s. Before that I used Lotus123. I am completely self taught in the Excel arena and gained most of my knowledge while creating systems to run a large logistics company that I managed for 15 years. I've had hundreds of clients and successfully completed thousands of projects since.

Is he an Excel MVP? I missed the last two MVP Summits, and I hardly keep up with MVP stuff any more, so I don't know. If he's not, he should be.

Problems With Euro Currency Tools Add-In

If you're experiencing some inexplicable errors with your VBA code, check your add-ins list (press Alt+TI). If Microsoft's Euro Currency Tools add-in is installed, uninstall it. That add-in should be banned. Euro Currency Tools is the only add-in I've seen that can cause errors in VBA code that's in a completely different workbook.

Try this in Excel 2007...

First, make sure the Euro Currency Tools add-in is installed. Activate a worksheet that has a few formulas, and then run this macro:

Sub TestEuroTool()
  Dim WorkRange As Range
  On Error Resume Next
  Set WorkRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
  Debug.Print WorkRange.Count 'formula cells
  Debug.Print Err.Number, Err.Source
End Sub 

Err.Number should be 0, since the sheet has formulas. But it's 9 (Subscript Out of Range), and the source of the error is Euro Tool (the name of the VBA project in EUROTOOL.XLAM) . At least a dozen PUP users have notified me of an error -- and uninstalling the Euro Currency Tools add-in fixes the problem.

It seems to be fixed in Excel 2010 beta.

Hiding & Unhiding With Shortcut Keys

According to the Excel Help system, you can use these key combinations to hide and unhide rows and columns within a selection:

• Hide rows: Ctrl+9
• Unhide rows: Ctrl+Shift+9
• Hide columns: Ctrl+0 (that's a zero)
• Unhide columns: Ctrl+Shift+0 (also a zer0)

They all work for me except the last one. It fails for Excel 2003, Excel 2007, and Excel 2010 (running Vista).

A bit of Googling tells me that others have the same problem. One theory is that the OS is intercepting that key combination before it gets to Excel.

Does Ctrl+Shift+0 work for you? If so, which OS and Excel version do you use? Note that to unhide columns, you must select a range of cells that includes columns to the left and to the right of the hidden columns.

Mini Books

Somehow, Mike Foster figured out a way to miniaturize books, so they fit in a shirt pocket. He sent a photo:

Adventures In Book Marketing

Yesterday Chandoo (Pointy Haired Dilbert) made a blog post: 101 Excel Secrets - Recommended E-Book.

It was a link to an ebook by someone named Francis J Hayes. Chandoo was upfront, and stated that he's an affiliate seller for the book.

I'm always interested in Excel secrets, so I took a look at the site.I was appalled. It looks exactly like any of thousands of scam sites on the Web, run by sleazy "experts" trying to make a quick buck from idiots. I normally don't like to such sites, but if you want to take a look, here it is: 101 Secrets of a Microsoft Excel Addict ebook.

Who knows? It might be the best Excel tips books in the world. But anyone with half a brain will click the Back button as soon as they see the site. It just reeks of sleaze.

Francis J Hayes might make a sale or two based on this post, but I posted it because I'm curious. Does that type of marketing actually appeal to Excel users?

By the way, who is Francis J Hayes? He claims to be an expert, but I couldn't find anything on the Web that didn't require payment or a subscription.

35 Years Of Microsoft

Preston Gralla reminisces: Microsoft turns 35: Best, worst, most notable moments.

As Microsoft celebrates its 35th anniversary, I've decided to take an idiosyncratic and opinionated look at the best, worst and most notable moments, technologies, products, decisions and people in the company's history.

Excel gets only two mentions:

Smartest software bundling

Clearly the smartest software bundling move Microsoft ever made was combining Word, Excel and PowerPoint into Microsoft Office, first for the Mac in 1989 and then for Windows in 1990.

Microsoft Word, which Microsoft originally (internally) called Multi-Tool Word, was released in 1983 for MS-DOS, in 1985 for the Mac and in 1989 for Windows. Excel was launched in 1985 for the Mac and in 1987 for Windows. Also in 1987, Microsoft released PowerPoint for the Mac, essentially a version of an application called Presenter that was created by Forethought, a company Microsoft had purchased that year. In 1990, PowerPoint for Windows was released.

Excel Cat

Daniel sent a photo, and a brief review:

My cat enjoyed your book.

I should note that cats really like the later editions.

If you have any photos of my books out in the wild, send 'em my way, please.

Arranging Your Data

Last week, someone sent me a workbook because he was having some problems with it. Here's a small section from the file:

It was arranged in categories, and each category had tasks below it, arrange in a row. Under each task name was date to indicate when the task was performed. Many of the date cells contained a cell comment to clarify (3,810 comments in all). Summary formulas were entered to count the dates. And each formula was hand-crafted because the layout was so haphazard.

I was very surprised to see such a poorly organized worksheet. But, after giving it some thought, this sort of thing is probably not at all unusual. The typical user, I think, probably starts entering data without giving the organization much thought. At first, it's easy makes sense. But after a few years of data entry, you end up with a complete mess.

I suggested that this person spend a day or two and copy/paste the data into a normalized table with four column headers: Category, Task, Date, and Comments. Data entry would be much easier, and the information in this table can be easily sorted, filtered, or summarized with a pivot table.

To a typical user, it probably seems very inefficient to repeat the category name and task name for every entry in the table. But it's actually the most efficient way to store data.

New Shortcut Key Discovered

Pointy Haired Dilbert compiled a comprehensive list of Excel keyboard shortcuts.

And that reminded me of a new shortcut I discovered last week:

• Ctrl+D makes a copy of an embedded chart (Excel 2007 and Excel 2010 only).

To help you remember, think "D for Duplicate." I couldn't find it in the Help system, and a cursory check of the Web leads me to suspect that I'm the first person in the world to discover this.

Before Excel 2007, the easiest way to copy a chart is to press Ctrl while you drag the chart with your mouse. That action was removed in Excel 2007, but Microsoft provided an even easier method -- and didn't even tell anyone about it!

Greg Likes The Book

Yet another unsolicited bit of fan mail:

Just a quick note to tell you how impressed I have been, for a very long time, with your Excel books. I bought one several years ago (maybe Excel Power Programming With VBA?) and, at the time, it put me ahead of all of my colleagues in terms of practical, ready to use knowledge about MS Excel.

Skip forward at least a decade and I had a current need to update my ss skills and went to Barnes & Noble to get some up-to-date resource technical materials. As I was scanning the available materials, my wife said "how can you ever manage to figure out which Excel book to pick?" At that moment in my scanning, I saw just "Walkenbach" and I pulled it out and replied "Right here, this guy is an Excel genius. I recall reading one of his earlier Excel books and it was just incredible." I think she thought I was kidding.

Well, I took Excel 2007 Bible home over the weekend and cracked it open this morning to get up to speed on charts. I wrapped up the 'Getting Started Making Charts' intro on charts and am just part way into 'Learning Advanced Charting' and I am very impressed! Your exceptionally clear writing style, focus on how to do the task at hand, easy transition from introductory to advanced materials are all just top-of-the-line.

So, I thank you, man, for putting out such a truly great product and I wish you the very best. I can't say enough good stuff!

Wishing you the best,
Greg S., NY

I'm glad you like it, Greg.

Excel 2010 VBA Enhancements

At the official blog of the Microsoft Excel product team: Migrating Excel 4 Macros to VBA.

Excel has a macro facility, known as Excel 4 macros (XLM for short) that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA.

The article lists quite a few things that, in the past, required an XLM macro. The most important new feature is the ability to provide argument descriptions for user-defined functions. These are the descriptions that appear in the Function Arguments dialog box.

I posted a new tip that demonstrates how to do this: User-Defined Function Argument Descriptions In Excel 2010.