Daily Dose of Excel

Mike from the Northeast won the 4-year anniversary contest. A copy of Dalgleish’s Excel Pivot Table Recipes is soon to be rushing to his house from wherever Amazon rushes stuff. In the contest, I asked for your favorite non-Excel blogs, and here are some of them:

Office Live Small Business… - Why are Microsoft brand names a whole paragrah? Just say it already!
The Secret Diary of Steve Jobs
Sasha Frere-Jones
101-things-in-1001-days - I was going to do that once - too hard.
Pogue’s Posts
Stuff White People Like - A little racism for variety.
Information Aesthetics
Hot Air
Old New Thing
Sean MacNair - Kiss over Zep? C’mon!
Experiences of an English Soldier
Gizmodo - I quit reading gadget sites for some reason. Maybe it’s time to start again.
Creed Thoughts - Possibly my favorite non-Simpsons show

I haven’t read many of these blogs and don’t endorse them. But I casually glanced at them and will probably give them a second look.

I’m heading to Seattle tomorrow for the MVP Summit. If you read blogs by other Excel MVPs, you know we like to drink at the Rock Bottom. As is my tradition, I got a new pair of shoes right before the summit. I bought Sketchers this time because I’m trying to look younger. It must be a mid-life crisis thing. Now if I could just learn to balance on a skateboard.

The 4-Hour Work Week

From page 115 of The 4-Hour Work Week:

Honey has completed her first project for me: research on the person Esquire has chosen as the Sexiest Woman Alive. I’ve been assigned to write a profile of this woman, and I really don’t want to have to slog through all the heavy-breathing fan websites about her. When I open Honey’s file, I have this reaction: America is f*cked. There are charts. There are section headers. There is a well-organized breakdown of her pets, measurements, and favorite foods (e.g. swordfish). If all Bangalorians are like Honey, I pity Americans about to graduate college. They’re up against a hungry, polite, Excel-proficient Indian army.

Professional Excel Development has a chapter on optimization that discusses the PerfMon utility (available on the companion CD). I used it for the first time on a 40 second process and I thought I would share the results. Thrilling, I know.

One hundred fifty thousand calls to class properties? Yikes! Noting that FillFinals was the biggest culprit, I manually added some perfmon calls inside that procedure to see what I could see.

Inserting the final reports consists of adding sheets to the final report workbooks, among other things. In this case it adds nine sheets to six different workbooks. I decided to break up that block of code even further. Specifically, I wanted to isolate the Sheets.Add line.

I guess adding sheets takes a lot of time. Maybe I should create a report with some ‘final reports’ already in it so I can limit the amount of sheets that I have to add. Of course I’ll have to delete extraneous sheets, so I’ll have to weigh the costs of that. Well, nothing earth shattering here. It was just the first time I used it on a real program and it was fun.

A couple of bugs I noted in the utility:
It puts PerfMonProcEnd statements before any Exit Sub statements, but when it deletes them it doesn’t respect my original tabbing.
My manual lines looked like PerfMonProcEnd “FireAssay.MProcess.FillFinals.HeaderData”. I don’t think I was supposed to put a period after FillFinals (the procedure name) because the output file added another column. That’s OK, but it didn’t adjust the headers. In the screen shots above, I manually adjusted the headers and added a Section header. It’s probably user error rather than a bug.

A while ago, Patrick O’Beirne sent me a copy of his book Spreadsheet Check and Control to review. I read it, but I never reviewed it. It’s a very well written, well organized book and I recommend it. But book reviews are hard and I’m a little lazy. Needless to say, I owe Patrick one. To try to make it up to him, I’m publishing the following press release for the EuSpRIG 2007 Conference.

EuSpRIG 2007 - Spreadsheet experts meet in London

Experts From Europe, United States & Canada Meet to Discuss Enterprise Spreadsheet Management issues at the 8th Annual Spreadsheet Risks Conference.

Bury St Edmunds, UK - 3rd June 2007 - The European Spreadsheet Risks Interest Group (EuSpRIG), in association with Compassoft, is holding its 2007 conference on the theme of “Enterprise Spreadsheet Management: A Necessary Evil?” on the 11th - 13th July 2007 at Greenwich University, London, United Kingdom. Keynote speakers include Professor Ray Panko of the University of Hawaii, who will be speaking on “Spreadsheet Errors - What the Research Says”, Dean Buckner of the Financial Services Authority who will be giving a regulatory update on the use of spreadsheets in the financial markets, and Paul Bach, CEO of Compassoft, who will be outlining the state of technology identifying and managing spreadsheets in enterprise environments.

“Many studies have shown that significant sums of money are lost by organisations of all sizes because they fail to check thoroughly that spreadsheets, critical to their business, are free of material error”, said Patrick O’Beirne, EuSpRIG chairperson. “EuSpRIG was formed in 1999 to address this and related issues. The annual conference is an excellent place for leaders in this field to come together to discuss the issues and agree on best practices” he continued.

“Finance teams and auditors everywhere know that there is a very high likelihood of multiple material errors in key spreadsheets that are used every day in their company. Compassoft is proud to work with EuSpRIG to raise this issue to a top priority among CFOs, Controllers, and Audit Firms”, said Paul Bach, CEO of Compassoft, the main conference sponsor.

EuSpRIG 2007 is a forum for business people, regulators, auditors, academics and other interested parties to share information and ideas about the management of spreadsheet risks, related problems and opportunities.

There are 18 papers and presentations in this year’s conference. Topics include “Enterprise Spreadsheet Management - A Necessary Good”, “Impact of Errors in Operational Spreadsheets” and “Risk Management for Complex Calculations”, Speakers and delegates are expected from all over Europe, North America and Australia including representatives from Lloyds TSB, HMRC UK, Shell, and universities worldwide.

Further information about EuSpRIG, including the conference programme and booking information is available at www.eusprig.org

About EuSpRIG

EuSpRIG is an organisation dedicated to informing organisations about the material commercial and financial risks involved in the uncontrolled use of untested spreadsheets created by end-users who are not experienced in developing information systems. EuSpRIG is a not-for-profit organisation governed by an elected committee operating under the terms of a written constitution. Committee members include senior managers and directors of leading accounting firms and senior academics from European Universities.

EuSpRIG 2007 Main Conference Sponsor - Compassoft, Inc. Compassoft reduces financial risk by discovering, validating, monitoring and controlling critical financial data files residing on departmental servers and desktop computers worldwide. Compassoft Enterprise is the industry’s only solution that automatically creates a comprehensive analysis of all spreadsheets, databases and reports distributed across your entire organization, including those within and outside of document management systems. This easy to use and deploy software creates and documents a comprehensive controls structure for key distributed spreadsheets, data bases and reports found across your company’s divisions and reporting entities.

EuSpRIG 2007 Conference Co-Sponsors
The following organizations are pleased to support the EuSpRIG 2007 Conference: SecureXLS, Baker Tilly, Spreadsheet Engineering Ltd, AuditNet, Systems Modelling, Information Systems Audit and Control Association - Northern Chapter, University of Wales Institute Cardiff, University of Greenwich.

EuSpRIG Contact
Grenville Croll, EuSpRIG Membership Secretary, c/o
Spreadsheet Engineering Ltd, 63a Churchgate Street, Bury St
Edmunds, United Kingdom, +44 (0) 1284 748020,
grenville@spreadsheetrisks.com.

The contest, Free Stuff, was itself an April Fool’s joke. There really is no book.

Actually, the previous sentence is yet another April Fool’s joke, as there actually is a contest. But only one person put “Stupid Contest” in quotes, so he wins.

Just kidding (boy, I’m on a roll). The actual winner was John Maleckar who will be receiving a congratulatory email very soon. No, this one’s for real.

The most common response to how many books you own was zero. Many of the people who reported owning zero or one also included an apology. To be clear, I didn’t write any of those books. I did do a technical review on a couple of them, but that doesn’t make them my books. If you were to buy one of those books by following the link, I would get some money. It’s actually better for me if you don’t own any, because you have greater capacity to buy. The person who owned the most owned four. I had as many clicks in the last 10 days as I had all last month, but half the sales. And none of the sales I’ve had this month are books. I don’t really know how that whole thing works.

Here’s what some of you had to say:

April Fools joke that involves Excel: Keeping millions of Excel VBA programmers hanging for year after year regarding the future of their chosen development platform.

My favorite April Fool’s joke for Excel was to have the “blue screen of death” appear when my victim selected a certain cell.
It was harder than I initially intended, as you cannot take a screenshot of that screen for future use. Using a digital camera was equally horrible, so I had to go into Paint Shop Pro and painstakingly create a picture that was close enough to fool most computer mortals.
All in all, well worth the time.

My favorite April Fool’s joke concerning Excel is this : I have a co-worker who’s life revolves around one particular Spreadsheet. He must be in it at least 6 hours a day. While he was away from his desk (mother nature calls everyone from time to time) he didn’t log out, so I took a screenshot of this open Spreadsheet, then made it his new desktop background image. It provided about 5 minutes of amusement watching him get mad at his “damn locked up Spreadsheet”. Even funnier when he re-booted and still didn’t get it when the computer somehow “booted up with this Spreadsheet mysteriously launched all by itself (and still locked up… damn it)”.

As for my favorite prank, is to hide all rows and columns on a worksheet, switch off scroll bars and sheet tabs, and then send the sheet to a co-worker, telling them that it contains all the info they need. I must have gotten this from someone else, but I can’t remember who it was.

This one must be funny only in Canada.

This e-mail is confidential. If you are not the intended recipient, please notify the sender and delete this e-mail and any attachments without copying, disclosing or retaining it in any form.
Ce courriel est confidentiel. Si vous n’etes pas le destinataire designe, veuillez en informer l’expediteur et supprimer ce courriel ainsi que tous fichiers joints sans les copier, divulguer ou conserver d’aucune facon.

This one reminds me of screwing with the AutoComplete, which is always funny.

Not an Excel trick, and not original, but still damn funny. In The Office (American version), Jim writes “just a simple macro” so that every time Dwight types his name it turns into “diapers.”

In other news: Guess what I’m doing this weekend.

If you guessed “Playing golf while your wife stakes out the perimeter of your yard”, you’re right. To quote one of my co-workers, “You are the laziest bleepity-bleeps that every lived.”

Today I received 10 copies of Excel 2007 Power Programming With VBA.

Six copies are up for grabs. Just tell me what you'll trade for an autographed copy. To keep things simple, post your item-for-trade offer at my blog.