J-Walk & Associates, Inc.

Follow On Twitter

I really have no idea how Twitter works (or even why people use it), but I found some software that will tweet whenever I make a post to this blog. I guess it's for people who don't understand how RSS works.

In any case, my Twitter name is jwalkblog. There's probably a better way to tell you about it but, as I mentioned, I have no idea how Twitter works.

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 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.

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.

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!

Chess Game Viewer

This is one of the most creative Excel apps I've ever seen. Created by Daniel Ferry, At Excel Hero: Excel 2007 Chess Game Viewer.

It uses no macros. Unprotect the worksheet and try to figure out how it work. Some hints:

• Dynamic Charting with No VBA
• Combination of XY (Scatter) and Stacked Column chart types
• Marker Fill with picture files.
• Conditional Chart Formatting for chessboard style
• No IF() functions. Not even one. REPT() and boolean logic as alternative to IF() INDEX/MATCH

Your Most Recent Excel Task?

Just out of curiosity... post a comment and tell us about the last time you used Excel. How long ago was it, and what exactly did you do?

In my case, I used Excel about two hours ago to double-check a file that will be included on the CD that comes with my Excel 2010 Formulas book. The tech editor, Niek Otten, noticed a discrepancy between the text and a figure. So I had to check it. Niek's really good at tech editing books. I'm often amazed at the stuff he points out. Too bad it's such a low-paying job.

Before that, I received a PUP v7 upgrade request, and checked my PUP sales workbook to ensure that the person was eligible for an upgrade. She was. Yes, those upgrade requests are checked manually. Low-tech, but that's the best I can do.

So what's the last thing you did with Excel?

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.