Spreadsheet Page Blog
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?
Excel 2010 will feature a new Equation Editor. That means we can can reproduce classic calculus jokes like this:
Fan Mail From Texas
Another one for the "Fan Mail" category. An Excel user from Texas writes:
I think your work is awesome. You have single-handedly taught me how to write a very comprehensive user application that has lead to a nice fat salary increase. I knew nothing of VBA before I bought your book your book in October. Seriously, nothing. I am very proficient in the Excel application, used it for years for patient tracking and such (I am a nurse by trade, but somehow morphed into a Clinical Trial Research Budget and Contract analyst - whatever that means) but have never used VBA and certainly didn't know where to start.
I created some excel spreadsheets that my entire institution uses (I work for the largest cancer center in the country. I telecommute.)
Your guidance, examples, fabulously well written book, and website have allowed me over the last 2 months to create a user application utilized by all of doctors, research administrators, research nurses, billing & finance and VPs alike related to over 4,000 clinical trials! I have now written over 50 macros in this short amount of time. I swear it amazes me .
I feel forever in your debt . You're like my Excel Hero.
I like a name with a face so here is me. You can see who you have helped!
That's the first fan mail I've received that included a photo. To protect her privacy, I will not post it. But she's an attractive, young brunette who knows a good book when she sees one.
Adjusted For Inflation
In 1993, in the Preface for Excel 5 Power Programming With VBA, I asked for reader feedback, and wrote:
Of course I would prefer to receive comments like, "This is the best book I've ever read" or "Thanks to this book, I was promoted and now make $150,000 per year."
In the second edition of that book, I raised the salary to $175,000. In the Excel 2000 edition, the salary expectation (inexplicably) went down to $85,000. In the Excel 2002 edition, it went up to $90,000. It increased again in the Excel 2003 edition, up to $105,000. In the Excel 2007 edition, the salary expectation rose to $112,000. In the Excel 2010 edition, it will be $124,000.
Here's how it looks graphically:
I wish I could remember why I reduced it for the Excel 2000 edition. Maybe someone told me that the salary was unrealistic.
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.
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?
The Book Winners
Today's 2-hour PUP sale was a major success. I was really surprised by the turnout. There are now 135 new PUP users. That works out to more than one per minute of the sale. Frankly, I was expecting about 50, tops.
The "but wait, there's more!" part of the sale was a free book for three randomly chosen customers. I asked the winners to provide a prioritized list of the books they want, because I only have one copy of some of them. They were all able to get their first choice:
Posted: Old-Style Menus In Excel 2007
I just a posted a simple VBA macro that creates a toolbar containing the old Excel 2003 menu system. Click here to view the code: Old-Style Menus In Excel 2007.
It looks like this:
It might be useful in a pinch. Some of the commands don't work, but most of them do.
I got an email asking if it's possible to format a cell with a sub subscript (also known as a double subscript). It's not.
But you can do it in a Text Box (Excel 2007 or later).
And you can do a few other text tricks too. See Text Effects in Text Boxes.
Excel 4.0 First Look
Google Books has an old issue of InfoWorld, where you can read my "first look" at Excel 4.0: Excel 4.0's ease of use is sure to win converts. It's from March, 1992.
The active cell and selected ranges now include a small "auto-fill" handle in the lower right corner. To copy a cell or range down or across, you simply click and drag this handle. This technique is even intelligent. Excel examines the selection for words it recognizes -- such as January, Qtr-1, Monday, etc. -- and will extend the series for you.
I also raved about the right-click shortcut menus.
I've been working on the Excel 2010 Bible, and I read this passage in a section about data entry:
If a number contains a colon (:) or is followed by a space and the letter A or P, it may be converted to a time format.
Obviously, I knew this at one time, but I forgot it. If someone would have asked me yesterday, "Hey John, when I enter a time, can I just type a P, or do I need to type PM?" I would have replied that it's necessary to type PM.
An aging brain has room for only a limited about of Excel knowledge, so some of it goes away to make room for more. I probably used to know all of this, too:
- Type an integer from 0 through 9999, followed immediately by a colon, and it's interpreted as a time value. 0: is interpreted as 12:00:00 AM, and 9999: is interpreted as 2/19/1901 3:00:00 PM.
- Type an integer from 0 though 12, followed by a space and the letter P, and it's interpreted as a p.m. time. So 9 p is converted to 9:00 PM.
- Type an integer from 0 through 12, followed by a space and the letter A, and it's interpreted as an a.m. time. So 9 a is converted to 9:00 AM.
- Type an integer, followed by a colon and a value from 0 through 59, and it's interpreted as a time. A leading zero for the minute part is not required. For example, 9:2 is converted to 9:02 AM.
- Type an integer, followed by a colon and a value greater than 59, and it's interpreted as a time -- but Excel doesn't apply time formatting. For example, 6:200 is converted to 0.388888888888889. If you apply time formatting, it appears as 9:20 AM. (That's 6 hours plus 200 minutes.)
Feel free to list more Excel time-entry quirks -- even though I won't remember them.
Calculating In The Pre-Excel Days
This is an interesting site: Retro Calculators.
Here's a device called the Add-A-Mite Pocket Calculator ("Handsome as jewelry, accurate as a watch").
This simple mechanical pocket adder is made of aluminum with bright blue paint and dates perhaps from the 1950s. The cursor on the right is depressed and dragged to the number you wish to add, 0 to 99. When released, the cursor engages a gear tooth on the back of the disc and when you then drag the cursor clockwise back to the 0 position, your number is added to the result. Crossing from 99 to 00 causes an automatic carry into the hundreds window, allowing sums up to 2,499. Subtraction works similarly by starting from zero and dragging counter clockwise to the desired number.
Back then, people rarely added numbers that exceeded 2,499.
Sadly, version 2 failed miserably in the marketplace. The world just wasn't ready for Add-A-Mite-With-Pivot-Tables.
Everyone else is celebrating this momentous occasion, so I will too.
In Excel, today's date (July 6, 2009) has a serial number of 40,000. According to Excel, the world began on January 1, 1900, and that date is designated Day 1.
Personally, I'm really looking forward to September 5, 2021 (also known as Day 44,444).
Still More Fan Mail
And the fan mail keeps pouring in. I think this is the third one this year:
I just wanted to say thanks to you for your Excel book. I purchased your "Excel 2007 Bible" after purchasing two previous Excel books from other authors. Your book is far superior in allowing me to understand Excel.
I currently work for a major accounting firm and knowing Excel in the accounting industry is crucial. My Excel skill before purchasing your book was probably a 1 (on a 1-10 scale), but after going through the first five chapters and chapters 11 and 12, I can comfortably tell you that my knowledge in Excel has increase dramatically. Hopefully, by the time I'm done with the book, I will be able to stay in pace with some of my co-workers.
I wrote you this e-mail because I feel that you have contributed in some way to my career, and I want to thank you for it. I know some people don't give you credit for the hard work you do in creating such a great book. Thanks once again.
Thank you for the kind words, Jose.