Leftovers, Microwaves, and Other Unlikely Inspirations

I brown bag my lunches. My lunches usually consist of leftovers from the night before. I reheat those leftovers in the microwave at my office.

None of those facts are unusual, I grant you. I'm sure that previous paragraph makes me like thousands of others of office workers throughout the United States.

What I wanted to talk to you about is unusual inspirations in unexpected places.

Instead of buying lunch meat, or bread, or other traditional lunch items. I re-use what I already have. That's lesson number one: re-use existing resources as much as possible. Lesson 1.25 is to be willing to change your traditional notions to get results. Lesson 1.5 is to find resources that are inherently reusable - like plastic containers that you can wash and reuse.

When I put my food in the microwave the other day, I was struck by an unusual characteristic of that microwave's interface - instead of having to punch in "2" then "00," then "start," to get the microwave to cook my food for two minutes, all I had to do was push the number "2," and my food started cooking. That's lesson number two: look for ways to make your interface easier to use.

Let's put all of those lessons together now in the context of Excel, which has become my bread and butter of late.

Lesson 1
The first lesson, as you may recall, was "re-use existing resources." The implicit lesson there is that we have to find ways to create re-usable resources in the first place.

Now, a lot of people out there will say a sentence like this: "I have a template that I can email you to use to fill out your expenses."

Tell me how many of those templates end in "xls." If they do, they are not a template. They're a workbook in template's clothing.

A true template should be saved in "xlt" format, for starters. In addition, make sure that your templates have the following:

• If there's a formula in a cell, make sure it's protected.
• If you require a certain type of input in a cell, consider validation, or a userform. (I primarily develop and use a Macintosh right now, and as most of my colleagues and community members who use Mac Excel will tell you, most ActiveX controls choke on a Mac. So know your audience's platform.)
• Consider a hidden worksheet to contain all of your lookups.

The most critical thing is the "XLT" part. The rest is gravy. Furthermore, if you use Windows, consider this idea: Put your re-usable template on the company's Intranet, particularly in a location where it's readily available to your colleagues. Furthermore, you can put the file in a WinZip executable, and set the file to be extracted to C:\Documents and Settings\[user]\Application Data\Microsoft\Templates\.

Lesson 2
Lesson 2 was ease of use. Remember that with my microwave example, I needed push only one button to get my desired result. Ways you can make that happen in your Excel environment include, but are not limited to:

• Put some code to fire on the Auto_Open() event of the workbook. For example, I've written code to load a toolbar button when the workbook opens. In other cases, I have the code just automatically run on the Auto_Open() event. In still other cases, I check to see if a cell which is out of the print area and out of sight has a value.
• Seek to eliminate the amount of manual input, jumping through hoops, and tweaking that your end user has to do. If your VBA code can figure out the answer for them, don't make them figure it out. If you need the current date inserted in a particular cell, do something like this:
  Private Sub Auto_Open()
  Range("A5").activate
  If len(A5)>0 Then
  Activecell.Value=format(Now(),"mm/dd/yyyy")
  End If
  Simple stuff, but it helps.
• Don't restrict your thinking to just VBA and Excel; see if there are other resources available in your company to get the data to your end user without any manual intervention on their part - the "other resources" in my case are AppleScript to open a page, download the Excel file on that page, and then a Unix shell script to copy that file to a specific folder.

Insert this idea into your thought process, too: What happens if your user accidentally deletes a file that your code depends on? What happens if they enter in a value you can't use? In other words, what happens if they kick the legs out from under one of your assumptions? How are you going to handle it? In some cases, you can prevent it from happening in the first place; in others, you're going to have to write error handling code.

Remember, our main charge as the Microsoft Office experts in our office is to make our customers' lives easier, not to make our jobs more secure. Act accordingly.

Next time we meet, I'll surprise us both. Until then, be well.