J-Walk & Associates, Inc.

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.

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.

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.

Equation Editor

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

Colorful UserForm

I ran across a site that features an add-in that provides an alternate MsgBox function: ktMsgBox. Here's the UserForm (click to see it in actual size):

I'd say that qualifies as a poorly designed UserForm. Besides being ugly, it's just way too confusing.

When designing a UserForm, my advice is to emulate the look of the dialog boxes in Excel as much as possible. Just because you can change the colors and cram 50 controls into one dialog box doesn't mean you should do so.

I downloaded the add-in just to see how it worked. I deleted it as soon as I saw the pop-up greeting that stayed on screen for 60-seconds and couldn't be closed.

Microsoft Patenting Sparklines?

This kind of boggles the mind. One of the new features in Excel 2010 is sparklines -- small in-cell charts that are often presented in groups.

The concept was invented and named by Edward Tufte. However, Microsoft is seeking a patent: Sparklines in the Grid.

A sparkline is associated with a location in a document to provide a visual representation of one or more data values included in the document. The sparkline is associated with a data source within the document including the one or more data values. The sparkline is generated by generating the visual representation based on the one or more data values with a matrix of points to be presented at the associated location in the document. The sparkline is presented at the associated location in the document. The sparkline is configured to be regenerated when one or more of the data values in the data source change.

In July, one the Microsoft patent authors (San Radakovitz) wrote this on a Microsoft blog: Sparklines in Excel:

For Excel 2010 we've implemented sparklines, "intense, simple, word-sized graphics", as their inventor Edward Tufte describes them in his book Beautiful Evidence.

At his Web site, Edward Tufte asks:

What should I do?

Office 2010 Beta Is Now Available

If you'd like to take a look at Office 2010, go here and download the Beta.

After registering, you will be able to select the version (we recommend 32-bit which runs great on both 32-bit and 64-bit versions of Windows) and your software language.

Downloading the Office Professional Plus 2010 Beta could take an hour or more. The exact time will depend on your provider, bandwidth, and traffic. The good news is that once you start the download, you won't have to answer any more questions - you can walk away while it finishes. If your download gets interrupted, it will restart where it left off.

It seems very stable, but it still has a few bugs. For example, if you want to try out the new Equation Editor in Excel, you need to make sure your worksheet has at least one Shape. Otherwise, the Equation command (which is found on the Insert tab) is disabled.

Oh, and my PUP v7 add-in works just fine.

VBA Speed Results

A few days ago I posted a VBA procedure that looped 100 million times. I asked readers to run the code and report on how long it took. See How Fast Is Your System?

About 60 people responded, and here's how the times were distributed:

It's certainly not a normal distribution. The times are skewed towards the faster end of the scale.

• Mean: 13.25 seconds
• Median: 11.29 seconds
• Fastest: 5.08 seconds
• Slowest: 28.52 seconds

So, the fastest machine ran the code about 5.6 times faster than the slowest machine.

But the real question is, how long would it take to do this task manually?

I wrote 0 on a piece of paper and I flipped a coin. If it came up heads, I added one to my tally. If it came up tails, I subtracted 1. I did this ten times and it took me 42 seconds. So, one time through my "loop" took 4.2 seconds. Using this information, I calculated that it would take me 799 years to perform this task 100 million times -- but only if I work non-stop. The conclusion: My computer is about 52.3 million times faster than I am.

How Fast Is Your System?

Just for fun, take a few minutes and post your result. Copy and paste this procedure into a VBA module. Then run it, and post the time it takes.

Sub TimeTest()
  '100 million random numbers, tests, and math operations
  Dim x As Long
  Dim StartTime As Single
  Dim i As Long
  x = 0
  StartTime = Timer
  For i = 1 To 100000000
    If Rnd <= 0.5 Then x = x + 1 Else x = x - 1
  Next i
  MsgBox Timer - StartTime & " seconds"
End Sub

Here's the time on my system (no significant difference between Excel 2003, 2007, and 2010 Tech Preview):

My system is about three years old. I'm just curious about the range of processor speed of systems in use. Is it possible to run this in less than a second?

Making A Living From Excel

Every once in a while, I get an email from someone who want to know how they can earn a living by using Excel. An excerpt from his email:

I find I'm happiest when working in Excel and creating custom solutions. I even use it at home for my budget and finances and assorted other personal needs. Heck, I even made a Sudoku puzzle solver/creator a few years back "for fun". But, in the work environment, I've seen WAY too many spreadsheets created that barely scratch the surface of Excel's capabilities. Most people have no clue as to fully use this tool Microsoft created. You seem to have made a successful living at teaching others how to use Excel. I'd be interested in knowing how to do that.

Unfortunately, I'm probably the worst person to ask. I've manage to earn a living from Excel for quite a white, but my career was an accident and completely unplanned. I've become pretty much detached from the corporate world, and know next to nothing about job markets.

Lots of people do earn a living from Excel, so feel free to post your suggestions on how to do it.

A Very Polite Add-In

In Excel 2007, it's no longer possible to directly change a point a chart. In previous versions, you could drag a chart point to change the underlying value. Microsoft has come to the rescue: Excel Add-In for Manipulating Points on Charts.

I tried it, and it works. The VB Project is even unprotected, so you can view the code and maybe learn a thing or two.

One thing that struck me is the use of of the word please. I don't think I've ever seen an Excel dialog box that uses the word please. Nothing wrong with it, but it just seems kind of odd.

Free Stuff From Deb

At Contextures Blog, Debra Dalgleish is giving stuff away: Very Scary Fall Giveaway For Excel Nerds.

It's a contest. Either (a) describe an Excel-related costume, or (b) tell about your scariest Excel-related experience. Winners are chosen randomly, so you don't even need to be creative.