Geoff Lilley's Blog About Stuff

Geoff's 10 Commandments of Excel

2011-08-14T14:23:00.001-07:00

Excel is NOT a database program; don't try to make it into one
The formula bar NEVER lies
Let Excel do the heavy lifting
There's a function for that
If you have a list, then you should have a table
Scroll down when possible, scroll across only when necessary
Separate data, analysis, and presentation
Macros are the nuclear option – compiled code is more effective than interpreted code
Excel help SUCKS
I may be wrong about all of this…you need to find out for yourself

Office 2011- WOW!

2011-02-13T12:46:00.000-08:00

I was pretty underwhelmed by the release of Office 2008. Then again, to be fair, the minute I heard macros were gone, the rest of it was just "blah, blah, blah."

By the way, I did give it a chance. I wasn't impressed. The Document Elements feature in Word was kind of cool, but I knew how to do all that before. The multiple masters in PowerPoint is a nice idea, but I'd rather just have different color schemes with the same theme, and call it good. The fact that it runs natively on an Intel processor is handy, and having over 1,000,000,00 rows and 16,000 columns in Excel is helpful.

But I would have gladly given up all of those things to have macros back. That's just too critical to my business needs to give up.

With Office 2011, I don't have to. With Office 2011, I get all of the features of Office 2008, plus much, much more.

A few highlights:

PowerPoint has two new main features:
One is something that Keynote has had for years, but is nice to see here - the ability to create sections. If I have a long presentation, having it broken into sections makes it easier to manage.
The second is the ability to visually re-arrange the stacking order of objects in a slide. It looks like Cover Flow in iTunes or Finder. It's handy if you have a slide with a lot of drawing objects.

Word only has a new feature or two:
All of the applications have more templates available, but Word really benefits from it the most - calendars, brochures, booklets, you name it.
Word now has a view called Publishing Layout, which makes your page look like it's on a drafting table. This is useful if you're using Word in a desktop publishing capacity - place and arrange text boxes, pictures, and other graphic elements.

The artist formerly known as Entourage is now Outlook, and that means a few significant changes:
If you're trying to import a PST file from Outlook for windows, you now can. Before, you couldn't. Now, you can.
No longer are you at the mercy of the dreaded Database! Instead of all of your messages/data being stored in a single file, you now have individual messages stored as individual messages. The folder is ~/Documents/Microsoft User Data/Office 2011 Identities/Main Identity/Data Records

What about Excel?! Thought you'd never ask. So much new, so much improved, so truly wondrous to behold. I'll start with some of the improvements:
Macros are back, baby! Not only will all of your old macros from Office 2004 work, you now have Auto-Complete, like our colleagues on Windows do.
Conditional Formatting has infinitely more options; you can have stoplights, color bars, shades of color, and a host of other options. No more red, yellow, green!
The Home tab, which contains some common formatting commands, has gotten smarter; merging cells and aligning text is faster and easier.
Plotting charts, including placement of legends and labels, is more efficient.
You can filter and sort up to 64 levels, including font and background colors. Date filtering is MUCH more efficient - dates are grouped by year, then month, then day.

Now for some of the new features:
More functions - in particular, NETWORKDAYS.INTL, which is NETWORKDAYS but allows you to pick what days constitute weekends, instead of just defaulting to Saturday and Sunday.
Instead of the half-baked List Manager, you now have Tables - alternating row colors, auto-populate formulas, and, best of all, you can base a PivotTable on the table, which is a dynamically growing/shrinking range, instead of a range of cells you have to manage yourself.
Sparkline charts - instead of your traditional line chart, the Sparkline is a chart that "lives" inside the cell, to provide a mini-summary of your data.
The Data tab has your usual suspects - Sort, Filter, and PivotTable. And then there was "Remove Duplicates." This is one of those buttons that makes you think there really is a Santa Claus.

Bottom line? Don't walk - RUN to the store, and pick this up. It's AMAZING. Until next time, be well.

The Password Function

2010-09-11T17:36:00.000-07:00

I've worked with a lot of customers who have to generate random passwords for their users to log on to one kind of system or another.

I've seen algorithms and programs to do it; I thought recently, "What if I could write a function to do that in Excel?" I've done some ham-handed functions to make it happen (using RAND() and MID() and repeating the function eight times), but I was thinking there has to me a more elegant way.

I think I finally have it:

Public Function PasswordGen(Optional PasswordLength As Byte)
If PasswordLength = 0 Then
PasswordLength = 8
End If
Dim g As Byte
For g = 1 To PasswordLength
PasswordGen = PasswordGen & Chr(Rnd * (122 - 36 + 1) + 36)
Next g
End Function

In short, the optional PasswordLength argument allows the user to specify how many characters they want the password to be; eight is the default, but if you need sixteen, I have you covered.

Once that's been established, it's just a loop from one to the argument's length; just keep picking a random number between 122 and 36, and then get the ASCII character value that corresponds to that numeric position. 36 is the starting position on the ASCII character list of usable characters; 122 is the outer limit.

Simple function, I grant you, but useful. If you can use it, please do. I'll have more functions and useful stuff later, but until then, be well.

Net Work Times

2010-08-23T07:11:00.001-07:00

I couldn't have pulled this off without Chip Pearson's formula on work days and times. In a nutshell, when I was still at Pearson, I was asked to figure out how much time had elapsed between the creation of a ticket, and the time a support agent actually started work on that ticket.

This proved to be one of the ugliest formulas I ever wrote. It was two parts:

Figure out the earliest time that someone could have touched the ticket
Figure out when someone actually did, and how many "work hours" had elapsed between the opening of the ticket and that time

I started by creating named ranges:

The first was the days of the week, the day the ticket could have been opened, days elapsed between those two, and the respective start and end times, called "dayStart."

Day Email Came In Day Service Starts # of Days Start Hour End Hour
Sunday Monday 1 4:00 AM 5:00 PM
Monday Monday 0 5:00 PM
Tuesday Tuesday 0 5:00 PM
Wednesday Wednesday 0 5:00 PM
Thursday Thursday 0 5:00 PM
Friday Friday 0 5:00 PM
Saturday Monday 2 4:00 AM 5:00 PM

The second named range was the list of holidays for the year in question (2006-2007), called "holidayList."

The third and fourth named ranges were just the standard start and end times for the business ("startTime" and "endTime", respectively.)

A typical row of data in the spreadsheet began with the case number, and the date/time opened (columns A and B):

Day Email Came In
Sunday 00:00 00:00
Monday 04:00 17:00
Tuesday 04:00 17:00
Wednesday 04:00 17:00
Thursday 04:00 17:00
Friday 04:00 17:00
Saturday 00:00 00:00

I had the week # and year in columns C and D; in E, the first of two formulas:
=IF(AND(DAY(B2)>=2,DAY(B2)<=5,HOUR(B2)>4),text(date(year(b2),month(b2),day(b2)),"mm/dd/yyyy")&>=17,TEXT(B2,"dddd")="Friday"),TEXT(DATE(YEAR(B2),MONTH(B2),DAY(B2)+3),"mm/dd/yy"

In column F, I had the ticket touched time; in G, the Actual Duration:
=(IF(B2=F2,0,IF(AND(INT(E2)=INT(F2),NOT(ISNA(MATCH(INT(E2),holidayList,0)))),0,ABS(IF(INT(E2)=INT(F2),24*(F2-E2),(24*(endTime-startTime)*(MAX(NETWORKDAYS(E2+1,F2-1,holidayList),0)+INT(24*(((F2-INT(F2))-(E2-INT(E2)))+(endTime-startTime))/(24*(endTime-startTime))))+MOD(ROUND(((24*(F2-INT(F2)))-24*startTime)+(24*endTime-(24*(E2-INT(E2)))),2),ROUND((24*(endTime-startTime)),2)))))))/24)

YIKES.

Yeah, well, it worked.

If this code can be of any use to anyone, please do? A former colleague of mine (Jeff Z) mentioned on my Facebook page that he gets questions about this in class - here you go, my friend. (For those of you who live in the Sacramento area, Jeff Z is a former colleague of mine from New Horizons Computer Learning Center. While I was Training Supervisor there, I once had the honor of awarding him Instructor of the Year, where I described him as "more the All-Madden Team than the Heisman, but an MVP on any team.")

Thank you to Chip Pearson, for making this formula possible, and to the support management team at Pearson (no relation) for giving me the chance to make this happen. Be well.

Excel Wish List - HOT DAMN!

2010-08-22T22:22:00.001-07:00

There's an old saying in the news business: "If you don't like the news out there, go make some of your own."

I decided to apply that same line of reasoning to Excel functions. Back in a previous blog post, I had asked out loud if certain functions could be included (see Microsoft Office Wish List, the section under Excel):

Count instances of a string (how many times does the letter "e" show up in "Geoff"
Find the nth instance of a string (find where the 3rd "g" shows up in a cell's text)
An ORDINAL function
A work hours elapsed function
A VLOOKUP function with an additional argument for what instance you're looking for

Well, I went 4 for 5 today. I've been able to pull off all of them but #4.

The code follows:

For #1, my challenge was to count how many times a letter or letters show up in a cell's text.

The code:

Public Function CountText(strTexttoCount As String, strTextToFind As String)
Dim strTextoFindLen As String
Dim intTextToCountLen As Integer
Dim intTextToFindLen As Integer
Dim g As Integer
intTextToCountLen = Len(strTexttoCount)
intTextToFindLen = Len(strTextToFind)
For g = 1 To intTextToCountLen
If Mid(strTexttoCount, g, intTextToFindLen) = strTextToFind Then
CountText = CountText + 1
End If
Next
End Function

This one proved to be pretty straightforward; pass the cell's text and the text to find as string variables to a function, then loop through the cell's text letter by letter. If the text to find is there, then increment a counter by 1. When you're all done looping, what is the counter at?

#2 was the Nth instance of a string - find what position in a string does the 3rd g, the fourth f, the second T show up?

The code:
Public Function ModeFind(strTexttoCount As String, strTextToFind As String, intInstancetoCount As Integer)
Dim strTextoFindLen As String
Dim intTextToCountLen As Integer
Dim intTextToFindLen As Integer
Dim intTextOccurrence As Integer
Dim g As Integer
intTextToCountLen = Len(strTexttoCount)
intTextToFindLen = Len(strTextToFind)
For g = 1 To intTextToCountLen
If Mid(strTexttoCount, g, intTextToFindLen) = strTextToFind Then
intTextOccurrence = intTextOccurrence + 1
If intTextOccurrence = intInstancetoCount Then
ModeFind = Len(Left(strTexttoCount, g))
End If

End If
Next
If intTextOccurrence <>
ModeFind = 0
End If
End Function

I recycled some code from the previous function to make this one happen, but same idea - loop through the cell's text, and keep looking for the "find" text. Count how many times you find it - when that counter is equal to the instance number, then get the position number, and return it.

#3 was the ORDINAL function idea. This one was actually the easiest of the four that I pulled off; code follows:

Public Function Ordinal(lngNumberToOrdinal As Long)
Select Case Right(lngNumberToOrdinal, 1)
Case 1
Ordinal = lngNumberToOrdinal & "st"
Case 2
Ordinal = lngNumberToOrdinal & "nd"
Case 3
Ordinal = lngNumberToOrdinal & "rd"
Case Else
Ordinal = lngNumberToOrdinal & "th"
End Select
End Function

All I had to do was get the leftmost digit of the number passed to the function, and then figure out what it was. Based on what it was, I added the appropriate suffix using a Select...Case statement.

My #4 challenge, I haven't worked on yet. I found some code from a guy called Jimmy Peña that looks promising...if I get his code to work, I'll let you know.

#5 was a bit of a bugger. My method is inelegant, clunky, and effective:

Public Function vlookupRow(rngRangeToSearch As String, varTexttoFind, lngRow As Long, lngColumn As Long)
Call FindBillyBrown35(rngRangeToSearch, varTexttoFind, lngRow, lngColumn)
vlookupRow = vOurResult
End Function

'******************************************************************************************
'This is a heavily modified version of some code from Dave Hawley of OzGrid.com that
'I've modified and re-purposed for my own function
'give credit where credit is due
'original can be found at http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm
'THANK YOU MR. HAWLEY!
'******************************************************************************************
Sub FindBillyBrown35(rngRangeToSearch As String, varTexttoFind, lngRow As Long, lngColumn As Long)

Dim rFoundIt As Range
Dim iLoop As Integer
Dim intCount As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
With Sheet1.Range(rngRangeToSearch)
'Set variable to start search from
Set rFoundIt = .Cells(1, 1)
'Loop no more times that the name occurs
For iLoop = 0 To WorksheetFunction.CountIf(Range(rngRangeToSearch), varTexttoFind)

'ReSet variable to found occurence of name. Next loop search _
will start AFTER this Set cell.
Set rFoundIt = .Find(What:=varTexttoFind, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
intCount = intCount + 1
'Check if it's the right one.
If intCount = lngRow Then
vOurResult = rFoundIt.Offset(0, lngColumn).Value
Exit For 'Leave loop
End If

Next iLoop
End With 'Sheet1.Range("Data")

If IsEmpty(vOurResult) Then
vOurResult = "#N/A"
End If
End Sub

I did the call to the Sub so that I could debug this stupid thing; I was having a devil of a time getting the range to evaluate properly. What I found, after all was said and done, was that if I quoted the range in the function itself (e.g., =VLOOKUPRow("A1:C6","apple",2,2) then I could get it to work perfectly. I'm not thrilled with that solution, but it works, so maybe it's time not to look a gift horse in the mouth.

Well, it's a start...just goes to show that if you want to throw down the gauntlet, be prepared to pick it back up, as well. Let that be a lesson to you. Until the next lesson, be well.

Office 2010 - Thanks Microsoft!

2010-08-10T14:02:00.000-07:00

PowerPoint
The Animation Painter button is WONDERFUL, and the Insert Screenshot button is equally superb. On balance, a lot of good new features.
1. Save as WMV: Check.
2. Find and Replace: No go.
3. Consistency and Professionalism: No go.
4. OK, I asked for more templates. The new templates are a HELL of a lot better than previous versions, so there is some improvement on this point. Between that and the Color Schemes introduced in 2007, we're getting there.
5. Navigation Menu Wizard and navigation buttons – Nothing. That's a real black eye, considering how many good shapes there are.
6. A better slide sorter – Still nothing. The ability to run the slideshow in one monitor and then edit in the other window is nice, but not a real improvement.
7. More stock photos, less clip art in the Gallery, both online and installed. Clip art plain SUCKS.
8. Still no color picker, but I suppose I could save custom color schemes as part of a theme. OK, I could live with that.
9. VBA – Doth mine eyes deceive me?! The "Record Macro" button is gone?!?

So how'd we do? I'll say we went 2 for 10, or 20%. PowerPoint had a pretty poor showing. D+ on my wishes, but the overall "how much better is it" is a solid B.

Microsoft Access
Access has many ways that it now integrates with SharePoint. Can't really fault Microsoft for wanting to do that, but I'll still wish for a more platform agnostic ASP.

1. Data Access Pages: Yeah, sort of. You can publish a database to a SharePoint server running Access Services, so that's moving in the right direction.
2. Parameter Query Wizard? No go.
3. Macro Wizard: HOME RUN! They really nailed it on this one! The ability to create steps and procedures in macros in Access 2010 is really fantastic.
4. RegEx, they get a pass on this one. If it's not in Office, it's not in Access.
5. Better and more updated form/report templates, and a greater flexibility to customize those templates: It's getting there.

I'd say on the Microsoft Access front, they went 3 for 5 at worst, and 4 for 5 at best. I'll give it a B.

Microsoft Word
I'm alone in the world on my belief Word needs slimming, apparently. No sense condemning Microsoft for catering to the majority.
1. Regular expressions, like Access, gets a pass.
2. Improved macro recording: I still can't use the mouse to select text, or click anywhere. Yippee.
3. Version control is going backward, not forward. Still no true "versions" feature, outside of SharePoint.
4. The Track Changes feature is better when the document is downloaded from SharePoint, and then version controlled using its native functionality. That's par for the course, I suppose.
5. Mail Merge: It HAS gotten better; now, my merge fields are on a pull-down menu, so I don't need to have that window open. Nice.

Hardly the finest hour for Word, but it's going in the right direction. I'll give it a B.

Outlook
Nice to see the Ribbon. The Actions buttons are really slick.
1. Hotmail? Same problem.
2. Haven't seen a Premium Live option.
3. Nothing on forms.
4. This is coming in October 2011, so we'll call it happy.
5. Duplicate Contacts – still nothing.
1 for 5 on the Outlook list, so that's an F. Upgrade it to a B- for the other features they did add.

Excel
The overall look and feel of Excel 2010 is just right, and a few of the new features THRILL me.
1. Still no Custom List Wizard.
2. PivotTables – WOO HOO! The Slicer has addressed this problem in SPADES! Nice work!
3. Show Report Filter Pages still does what it used to.
4. I may have to write my requested functions, see how I do. Bummer. But, again, as I mentioned, this is because I'm in the minority.
5. Still no love for Conditional Formatting in PivotTables. Strike two.

I'll give them an B+ on this one, just because the Slicer is SO COOL, and some of the other stuff I'm asking for is understandably esoteric. The ability to truly copy Report Filter pages is the next big step I see needing to happen, though.

Overall
On balance, it looks like Office 2010 is moving in the right direction. I don't know if the more esoteric features I'm asking for (like Regular Expressions) are worth pursuing. If I can write my own custom functions, I'm good there. But some of the other functionality, I feel like Microsoft needs to acknowledge that we live in a multimedia world, and we should respond accordingly.

Three Kinds of Programmers

2010-04-05T22:14:00.001-07:00

This was inspired by a post by Coding Horror blogger Jeff Atwood. He had many valuable things to say, including a post called Never Design What You Can Steal. I liked his idea about how we should "steal" code freely from each other.

I've long argued there are three kinds of programmers in this world (I'm the second one, by way of a pre-emptive strike):

I call the first class "script kiddies" just like the junior hackers are often known. I put in this group people who simply blindly steal code without any thought as to what it does, or why it does. They simply steal without a thought or a desire to attribute it.
I'm the second category, the "shade tree coders." We can't make a living writing code alone, even though we wish we could. We steal the code of the great ones, but we try to modify it to suit our own purposes. I'd like to think we gain an appreciation for what it does in that process. We attribute the source, and take credit for our modifications as appropriate.
The third category are the Jedi Masters. They simply smile at the rest of us, and say, "You're Welcome."

Thank you, Jedi Masters everywhere. I hope one day to join your ranks; until I do, I'll keep trying to perpetuate your legacy and be grateful for your gifts.

Until we meet again, be well. And be sure to thank a Jedi Master.

Try This First

2010-04-03T11:05:00.000-07:00

My wife is one of about three of my readers. That's pretty good, as far as I'm concerned.

She read my last post about the Computer User's Bill of Rights and Responsibilities, and recommended this topic, which I'll term Try This First - Some Things You Can and Should Check Before Calling Tech Support. In her case, calling Tech Support is hollering at me from the other room in the house, so it looks a little different than some of you who have to call a 1-800 number...but I digress.

Anyway, some things to try that fix a lot of problems, and, when available, the reasons why:

This is her favorite one, so we'll start right in with, "Is it plugged in?" Cords get loose, kicked, or knocked out of place all the time. It sounds like such an obvious thing, but it's worth checking to see if all the lights are on, especially power.
Where printers and other supply-based devices are concerned, does the device have adequate supplies? Ink is top on that list, by the way. :)
Everyone hates this answer, so I'm going to try and clarify why it works - "Have you tried restarting?" There are many reasons why restarting your computer or other device can make a big difference. Without boring you with the technical minutiae about memory leaks, let's just say that restarting gives your system a chance to make a fresh start.
Have you run BOTH an anti-virus and an anti-malware scan on your computer? Nowadays, it's more common for the Nortons and Kaperskys and McAfees of the world to have an all-in-0ne solution, but I still find that even with my pretty good (AVG) anti-virus and anti-malware solution, an occasional rogue slips through. When that happens, I go to Malwarebytes Anti-Malware. It's gotten me out of a nasty jam or two in the last month.
If you're experiencing slowness or weird behavior, is it just one program, or all programs? If it's just one program, is it just one document? If it's just that one document/spreadsheet, try copy/paste to a new one. That can make a difference.
If it's Internet access that you're having trouble with, is it just one site? That's the site's problem, not yours. Is it just one computer in the house? (easy for me to ask, I have four) If it's all of them, then it's time to call your ISP.
If you're having problems with one particular piece of software, don't hesitate to Google the error message you're getting; chances are good that you're not alone.
While I grant you not all of those error messages are worth a damn, or tell you anything interesting, do take a moment to see if the message has any useful information; do you need to insert a blank disc to burn that file? Are you out of hard drive space? Is your USB drive still inserted? It's worth trying.
If it's just one problematic piece of software, try checking for updates. If you get a message saying an update is available, install it, and then see if things get better. If not, try uninstalling and reinstalling it.
This is a pretty radical "Try backing up all your data, and then wiping your drive clean, nad starting over." Hopefully, you read my last post, and you're already in the habit of doing regular, religious backups, and re-installing Windows/OSX/Linux is the easy part...

This is more of a "things to have ready to tell the technician if you have to call," but I'm throwing it in here: What's the last thing that worked right? When's the first time you noticed trouble, and what caused you concern first? Was it a sound your hard drive made? Was it an error message? Be as detailed as possible in your description.

Whether you fix it yourself or call in for help, the two most powerful tools in every troubleshooter's toolbox are a notepad and a pen/pencil...writing down error messages/observed behavior is the key to your success.

And, if you're going to take on a hardware fix (replace RAM, processor, hard drive), it's a good idea to have a digital camera handy. Why? Take a picture of what your system looked like when you started, so you can reconstruct it more easily. Make careful note of the positions of screws, brackets, and cables; that can make a big difference.

Hopefully, your computer will keep running trouble-free, so you'll never need to worry about any of this stuff. But if and when it does, try this first. Until next time, be well.

The Computer Consumer's Bill of Rights and Responsibilities

2010-03-27T22:53:00.001-07:00

I do a small amount of tech support for family and friends. I offer my services freely to those in need. I accept payment from those who can, and do it pro bono for those who need it. That doesn't exactly make me unique among geeks in any way.

I've been thinking about the idea of a Computer Consumer's Bill of Rights for some time. This is a work in progress, to be sure, but it's a starting point. As I told my boss this week on an unrelated matter, "It's not a complete work; it's something to bounce off of, to start from."

So here goes.

The Rights of the Computer Consumer

You have the right to ask for an explanation of what I'm doing, and why I'm doing it, at any time. It's your computer, and you deserve to understand it.
You have the right to ask me to stop the explanation if it gets too technical, boring, or irrelevant. I know not everyone finds this stuff as interesting as I do.
You have the right to the confidentiality of your information. Any personal, sensitive, or private information that you have on your computer should be kept that way. I will only access said information if it is absolutely necessary.
You have the right to ask me how or why something the way it is. In some cases, please understand that the why and wherefore of technology is far outside my control, but I'll try to put it in context as best as I can.
If I am charging you for my services, you have the right to an itemized invoice, with an agreed-upon hourly/flat rate fee stated in advance. While I cannot always control how long something will take, I will make every effort to check in with you as we approach a new billing hour.

Your Responsibilities as a Computer User:

You are responsible for backing up your data. If you do not have a data backup system in place, please be advised that I will probably tell you to take care of that problem before we try to address any other problems.
You have the responsibility to give me as much information, as honestly as you can, about what you were doing right before the problem in question happened. I have a responsibility to listen without making judgment.
You have a responsibility to figure out what you want to use your computer for. While I can advise you on what software is good for what purposes, the ultimate decision as to what you want your computer to do lies with you.
You have a responsibility to keep copies of your software. If you downloaded the software from download.com or some other venue, I will make every effort to help you get a new copy, but no guarantees.
You have a responsibility to treat me civilly and respectfully, including respecting the veracity of my professional opinion. While I welcome your questions and your input, please remember that you hired me for my professional expertise, and I ask you to please respect my professional conclusions based on my expertise and experience.

More than anything, I'm trying to make sure that consumers/civilians and geeks learn to understand each other. To my fellow geeks out there, remember that civilians don't spend as much time thinking about/caring about technology as we do. To all our "civilian" customers out there, remember, we're human, too. Act accordingly, and until we meet again, be well.

Microsoft Certified Office Developer?

2010-03-21T01:01:00.000-07:00

I've kicked this idea around for years, and I've seen other MSO geeks talk about it in the abstract, so I'm hoping to re-kindle the debate. The Microsoft Office Specialist/Certified Application Specialist is the single-celled amoeba of the MCP food chain. I can live with that. I'm used to it.

But some MSO geeks, present company included, do a fair amount of VBA development for Microsoft Office, and I think we deserve some recognition for it. We're a different breed of dog from the other MSO users; not better or worse, just different.

So my thought is, have a new MCP certification called the MCOD - Microsoft Certified Office Developer. It would consist of two tests: one on core VBA and programming fundamentals, one on your application of choice. I'd probably start the testing/certification with Access and Excel for starters.

The Basics Test/Class

The VBA basics class would be called something like "Fundamentals of Programming Using Visual Basic for Applications."

The topics covered by this test and class would be some basics. Very little programming knowledge is assumed; instead, it's assumed you have an advanced understanding of the application in which you wish to do your development. Topics might include:

Control structures: For-Next, For-Each, Do-While, Do-Until
Conditional logic: If-Then, If-Then-Else, Select Case
Basics of objects, properties, methods, events
The VBA IDE
Variable types: numeric and string, and in between
Error messages: some of the more common ones, and what do they mean?
Error types: Syntax errors, logic errors, and runtime errors
Error trapping: How can you validate user input to ensure that your assumptions and dependencies have been satisfied?
How do you deploy/install your solution on another user's computer?
How do you support your newly built application?

The Fundamentals Test: Part 1
To test something like this would require two parts, for my money. Part 1 is a multiple-choice exam where you answer questions like this:

You have the following VBA code. When you run it, you get an error 1004. Why?
Your program keeps getting stuck in an infinite loop. Identify the problematic line of code.
What is a "Type mismatch" error, and how can you prevent it from happening in the future?
How can you make sure that your end user only enters in a 10-digit number into your form field called Ticket Number?
Write pseudo-code to iron every shirt in your closet that's a dress shirt and isn't ironed already.

The Fundamentals Test: Part 2
Using your knowledge of VBA, write pseudo-code to perform an everyday household task, choosing from the following list (doing laundry, dishes, taking out garbage, mopping floors, whatever)

The Program-Specific Tests
Who writes the tests? How about some of the MVPs in the respective disciplines? Have guys like Walkenbach, Pearson, and Umlas write the Excel course and test, and Dev Ashish write the Access test. The test would be a mix of:

What's wrong with this code?
What does this code do?
Write some code that solves the following problem
Record some code, then make it run faster
Record some code that's specific, and make it general

I'd call the MOC class for this something like "Developing VBA Solutions for Microsoft Access" and "Developing VBA Solutions for Microsoft Excel," respectively.

The Access Test
How about three parts on this one:

Subroutines that are executed, usually by the OnClick event of a form control's button
Custom functions
Userforms, which would have subparts design and functionality

Create a form to check for all the orders between date x and y.
Create a custom function to update the Geographic, based on the Country.
Create a custom function to calculate sales tax.
Create a subroutine to delete all records associated with an individual user.

The Excel Test
For this one, I'd probably follow the same pattern as the Access test, but place less emphasis on user forms, and more emphasis on custom functions.

Ideas might be:

Write a custom function to convert centimeters to inches, Farenheit to Celsius, miles to kilometers
Write a custom function to calculate the state, based on its capital
Write a subroutine to update all of the sales records for Nancy Davolio in Brazil to be the existing price + 10%

I'm just trying to get the ball rolling on this one. Anyone else have any ideas? Let me know. Until we meet again, be well.

Microsoft Office Wish List

2010-03-20T23:05:00.001-07:00

This is something I sketched together at my dining room table earlier this week, and hoped other people would enjoy - the Microsoft Office Wish List. Every Office geek has one, so here's mine.

PowerPoint
This is one that I went after hard, because I was talking to my buddy Sorb, a fellow Office trainer, about how PPT hasn't really come that far in the last 2-3 versions, and needs some updates/upgrades.

The fact that we live in the 21st century and we can't save a PowerPoint as any kind of movie file (on the PC, that is, Mac users can smirk at this one)..I know the format in question would be WMV, and that's fine. (By the way, to those of you who want to respond, "There's third-party add-ins that do this," I agree. But add-ins should be designed to address the needs of a narrow set of users instead of patching a gaping hole in a fairly commonly used application.)
A more robust Find and Replace, straight out of Word. I want to find Arial 16 point bold, and replace it with Verdana 16 point Italic. Word can do it, why can't PowerPoint?
I want a Consistency and Professionalism Wizard, or some name like it. This would be along the lines of Word's Word Count/Readability Statistics; show me some of the following:

Color Schemes
Fonts
Backgrounds
Animation

More installed templates out of the box. I don't mind going online to get them, but I shouldn't have to. I don't always have internet access, believe it or not.
A Navigation Menu Wizard, kind of like a DVD menu. I want to have a Wizard where I can add 3-5 action buttons that point to a particular point in my show, with some built-in button styles.
Speaking of action buttons, the ones we have look very 1980s...can we get some more modern looking ones, please??! Again, I know good and damn well how to draw my own, but why should I have to?
A better slide sorter. This is one microcosm of the utter lack of support for multiple monitors in MS Office. I want to go to the Slide Sorter, and be able to right-click on a slide, and choose "Preview In New Window," or in the other monitor, in this case. So my slides are sorted on my left screen, and I'm previewing the one slide on my right monitor. Whole-parts. Not rocket science.
More stock photos, less clip art in the Gallery, both online and installed. Clip art plain SUCKS.
The Mac has a color picker tool, why doesn't the PC? I don't think it's too much to ask to be able to grab the RGB value of a color from an external source, so I can make the fill color of my shapes that same RGB. But, of course, that would mean that we'd have to have a more robust color management tool in MS Office. Wow, bummer.
Last, but certainly least, would anyone else like to see a better, more robust, fully developed VBA object model? The amount of actions that I can record in PowerPoint is shamefully lacking, and it's frustrating.

Microsoft Access
In the interest of fairness, I have to say that I was pretty pleased with the small changes introduced in Access 2007; some of the import/export functionality is FANTASTIC. But there's still a few missing pieces:

I'd love to see an improved Data Access page come back. Maybe it's time for the Office Developer Edition to come back, so it's easier for me to create ASP pages right from Access; my recordset is already declared, and I can maybe create a quick-and-dirty update form, and a simple report? Anyone?
Parameter queries are one of my favorite features in Access. I'm thinking some kind of a Parameter Query Wizard that helps me build an unbound form, which lets me plug in the necessary parameters to run the query? And for extra credit, have that query drive a report? Yes please.
More wizards - Macro wizards this time. There are some basic functions that I shouldn't have to think about anymore:

The No Records macro for the report; just let me plug in a message and the Wizard does the rest.
A Duplicate Record Check Wizard - not on existent recordsets, but a Wizard to help me check for duplicate records before the Update event of the record.
The Audit Trail Wizard - when I create a form with a Delete Record button, have that fire an event that writes information about the deleted record to an audit table, so I know the where and when of the deletion.

Searching

Regular expressions in my searches would be SUPERB, especially in queries
More wildcards, including SOUNDEX

Better and more updated form/report templates, and a greater flexibility to customize those templates

Microsoft Word Wish List
I mentioned this in my last post briefly, but Word needs to be slimmed down in many ways; move more of the functionality over to Publisher, out of Word.

How about a Regular Expression find and replace? The Find and Replace in Word is the best in the business, but this would just take it up to the next level.
Improved macro recording. Let me CLICK buttons, for the love of God! That's a grievous shortcoming in the Word macro procedure.
More robust version control. If I say at the get-go, "establish versions for this document," then prompt me for the document's version data EVERY TIME I hit Save.
Tracking changes in Word is pretty damn good. But I have 2-3 computers I use on a regular basis, and if I do some changes on my laptop, and some on my desktop, Word treats me as two different people, How about instead, when I open a document that has Track Changes turned on, I "sign in" to the document using my Passport or NT authentication, so I'm the same Geoff Lilley no matter what computer I use?
There's one feature of Mail Merge that I think is just ATROCIOUS. If I click Insert Merge Field, why is this window modal? Why can't I insert a field, then move my cursor somewhere else in the document, while keeping that Insert Merge field window open? That's just STUPID.

Outlook

The biggest one of all? Hotmail is a Microsoft product. Outlook is a Microsoft product. It's just inexcusable that I have to download any kind of plug-in to integrate the two. I should be able to integrate my Contacts, Calendar, and Mail without any extra work whatsoever.
I'd love to see Microsoft offer a Premium Live service of some kind that allows Rules and Folder Sharing.
Outlook forms shouldn't be as esoteric or difficult as they are. Having pre-fabricated forms available for download online would be superb.
I'm an old Mac guy, so I have to give a shout-out to my Apple using friends out there - Entourage should be more like Outlook, especially playing nicer with Exchange.
How about a Merge Duplicate Contacts Wizard? Sound like a plan?

Excel
The difficulty I had coming up with this list probably tells you that I'm a HUGE Excel fanboy. OK, fine, you caught me.

I wrote an add-in years ago that did this, and I found it very useful: I want to be able to click a Custom List Wizard, which has a drop-down list and three buttons. The drop-down list is my set of custom lists. One button creates a worksheet for each item on the custom list I choose. The second button populates a set of cells (either down or across) with the items in the custom list I choose. The third button creates an in-cell drop-down list which uses the values from the custom list I choose.
PivotTables are almost at a pinnacle of perfection. But there's one more thing I think they could use. When I have two or more Report Filter (AKA Page) fields, I want to be able to establish a dependency between them. So if my topmost page field is State, and I choose New Jersey, then have the City field show only cities in NJ.
Along the same lines of the Page/Report Filter field: I LOVE the Show Pages functionality dearly. But in many cases, I have five and six PivotTables on the same sheet, and/or charts to go with it. When I click Show Pages, what I really want to do is copy the whole worksheet, not just that one table.
There's a few text functions I'd like to see added or improved:

A function for counting instances of a string - how many times does "eo" appear in cell A1 (call it COUNTTEXT?) It might go =COUNTTEXT(A1,"eo")
A function to find the nth instance of a string - find the third "g," or the second "a." Call it FINDMODE? MODEFIND? =MODEFIND(A1,"g",3) to find the third "g" in A1.
The ORDINAL function might write 1st, 2nd, 3rd, that kind of thing? =ORDINAL(A1) would return "1st" if A1 contains 1.
The long-running winner of Worst Formula I Ever Wrote was to calculate business hours. This one would be akin to NETWORKDAYS; how about NETWORKHOURS? Same idea as NETWORKDAYS; I tell you the start DATE and TIME, and the start DATE and TIME. The third argument is the start time of the business day; the fourth argument is the end of the business day, and the fifth is a range of cells that contains holidays.
Worst Formula Ever would make a killer lookup function - VLOOKUP with an instance number argument; VLOOKUP the second or third instance of a value. Call it VLOOKUPS? VLOOKUPROW? It would look like VLOOKUP, with the last argument being the instance number you're looking for: =VLOOKUP(A1,List!$A1$B25,2,FALSE,2) would find the second instance of A1, in range A1:B25 of the sheet List, exact match.

Conditional Formatting in PivotTables just shouldn't be this damn hard! I should be able to refresh my PivotTable and maintain my Conditional Formatting. My inability to do this without a macro is just INEXCUSABLE.

OK, enough ranting already. Holler back with your ideas, and I'll see you next time. Until then, be well.

The Swiss Army Toolkit

2010-03-20T22:45:00.001-07:00

You've probably heard a lot of different pieces of software refer to themselves as "the Swiss Army Knife" of this or that (video editing, photo editing, data management, whatever.)

I was talking to a colleague of mine the other day about the concept of the Swiss Army Knife (hereafter referred to as the SAK) software. The problem that SAK software faces is that it usually does several things, but none of them well. In the attempt to bring together too many different functions, you end up with bloated software that has menus and palettes too complex to navigate.

So Justin (my colleague) and I talked about how maybe the answer is, instead of having one piece of software that does everything, how about a suite of applications that integrate really well together, and each one performs a specific purpose?

Ladies and gentlemen, I give you the Adobe Creative Suite. While there's some crossover between the applications, as a whole, each has its discrete functionality that it does well, but by the same token, it's easy to transfer your work from one place to another. I can produce a movie in Adobe Premiere, but edit the sound in Audition. I can create vector graphics in Illustrator, and then place one or more layers in Photoshop.

The best part about Adobe products is that the interface is reasonably consistent across the applications; I can count on finding the Direct Select tool or the Eyedropper tool in the same place every time.

I'm no Adobe acolyte, don't get me wrong. I just think this is one area where Microsoft has laid a good foundation, and still has some work to do. I'd love to see some small, subtle changes in the interface that takes it to that clean, usable interface that Adobe has mastered. We're almost there.

The one program in the core MS Office suite I see needing the biggest facelift, by the way? Word. Word needs to decide if it's a word processor or a lightweight desktop publishing application. I'd love to see some of the publishing-oriented features taken out of Word, and become the purvey of Publisher. Make it easy to just import text from Word into Publisher, and go from there. Make Tables of Contents, indexes, and other publishing-oriented features the realm of Publisher; slim Word down.

Just a thought. I know it's rare you hear someone talk about removing and slimming down features instead of adding them, but I think it's time. Write and tell me what you think. In a near-future blog, I'll post my MS Office Wish List; love to hear your thoughts.

Until then, be well.

Playing Professional

2009-10-12T21:51:00.000-07:00

I'm a recent convert to the show "MadMen." So far, it has not surpassed "The Wire" and "The Sopranos" as the greatest show ever, but hey, it's got all the makings of a top 10 so far.

For those of you who watch the show, I'll just say that the incident I'm about to reference to make a point is from late in Season 1. For those of you who have not seen the show, this may come too close to a spoiler for your tastes, so you might want to skip this one.

For those of you who don't plan to see the show, or know nothing about it, the key facts are these: Don Draper is a successful advertising executive at a company called Sterling Cooper. The head of a larger, more powerful agency tries to recruit him to work for another firm. The man's name is Jim Hobarth, just for reference's sake.

Hobarth refers to his own agency as "Yankee Stadium" and "playing in the pros."

Among the tactics Hobarth uses to recruit Draper include a new set of golf clubs, a membership at an exclusive country club, and recruiting Draper's wife Betty to do some modeling for his agency. Hobarth then sends Draper pictures of Mrs. Draper, in her modeling glory.

That's when Draper calls to turn Hobarth down. When Hobarth again brings up the "big leagues" metaphor, Draper quickly responds with words to the effect of, "That last move wasn't pro at all."

Yes, there was a point. Playing professional, whether you're the Draper or the Hobarth, is extremely important when you're in a recruiting relationship.

I know most of us won't be giving or getting golf clubs and exclusive gym memberships in our search to make meaningful employment relationships, but it's all offering the job and taking the job for the right reasons. Play professional, and you'll attract other professionals to play on your team.

LinkedIn - MySpace for Working Folks

2008-10-10T11:38:00.000-07:00

My last post briefly touched on Facebook as a tool for networking. Yes, I am endorsing it over MySpace, Friendster, and anything else. If that feels kind of like endorsing the Yankees, I can live with it. When I'm looking to get in touch with my old classmates, colleagues, and friends, economy of scale is a beautiful thing.

On a separate, related note - how to find and network with former and current colleagues. For this purpose (purely professional) I love LinkedIn.

Why? Granted, it's not as extensive or personal as FB, but what I like about it is the ability to recommend and get recommendations from people you worked with, or people you currently work with.

What if you were an employer and you were trying to get some background on a potential employee? Which would you rather know - someone's religious beliefs, their favorite books, and their friends names; or would you prefer to hear recommendations from people in a professional context?

That sounds like a no-brainer to me...

The answer is that the two sites can peacefully co-exist, and I hope they stay separate and distinct. While I certainly have a fair amount of colleagues as friends on Facebook, it's because I'm friendly with them outside of the normal work context. LinkedIn I reserve for people I've actually done work-related projects with. Just a personal guideline.

Then again, I'm one of those people who thinks that while you can be friendly, cordial, and respecftul to your boss or your direct reports, it's a really bad idea to be friends outside of work with your boss, or your direct reports. Again, just a personal preference. Do with it what you will.

Facebook - MySpace for Grownups

2008-10-10T11:16:00.000-07:00

I've been on the hunt for a way to get back in touch with some of my classmates from Santa Clara University (class of 92), and from De La Salle High School (class of 88). Not to mention, in this economy, and with my eclectic skill set, I've had to move jobs a few times in the last 20 years.

That's just the tip of the iceberg. But you can see where the rest of the iceberg is going - it's hard to keep in touch with people when you move around and change lifepath.

Years ago, I heard on the radio about a social networking site called "Friendster." I decided to give it a try; I figured, you know, what the hell.

Quickly I found that my inbox was stuffed with messages from women who only gave their first name, and the subject line was always the same - just the word "hey." I thought that was kinda weird.

When I went to the Friendster site to further investigate, I noticed that most of the messages either had no picture, or the picture was the same person two or three times, with different names.

You guessed it - Friendster got pornitrated. Look it up on Urban Dictionary, but it basically is a word I invented to describe when a site gets overrun by porn spammers - the "come see my webcam" kind of garbage.

What about MySpace, you ask? Oh, that was even more fun...I was told by a family friend that I could find people who went to my high school - good way to get in touch. Sounds good, right?

Remember when you get advice to always consider the source. Kristina is a smart young lady with a good head on her shoulders. But let me clarify - she's 21 years old!

So she was right. I did in fact find people who went to my high school. But most of the people who listed De La Salle as their school were still going there.

OK, that's just creepy. 38 year old guys need a different site than 18 year old ones. Bye-bye, MySpace.

And then, my best friend from high school wrote me to tell me of Facebook - there were actually people from our class on there.

And people from my office. And people from my college - who graduated with me.

And I can play late-night games of Scrabble against my boss, share recommended reading with co-workers, and get notified when my favorite musicians are playing in my area.

Don't get me wrong, it's not perfect. Still some get-rich-quick spammer scammers on there, still some people whose energy exceeds their intelligence. But on the whole, I'm pretty pleased. I've managed to find some old SCU classmates I haven't seen since I left, and some found me.

You know what's nice? When you get a friend request from someone you respect and really like. That's happened several times. After we went to my wife's high school reunion, a few of her friends sent me requests. Felt like a validation of sorts.

Sure, some people just try to artificially inflate their friend count. But I just use it to keep in touch, and hopefully network with some people.

Try it. You might like it.

Why VBA?

2008-07-08T10:39:00.000-07:00

I have the great fortune, if you want to call it that, of being the Microsoft Office expert in my office. This is yet another one of those accidental tours of duty that I never saw coming, but I sort of landed here without knowing how.

(The most likely answer, by the way, is that I'm an Office geek instead of an Office user. What's the difference? Office users use the application out of job necessity, learn new application features out of that necessity, and stop when the necessity does. Office geeks use and learn the application out of an obsessive curiosity, and never know when the hell to stop. But I digress.)

Based on having been anointed said expert, I saw it my sacred duty to beta test Office 2008, so that I could warn my peers about what was ahead for them. (I got the little key fob and the copy of Office 2008 to prove it.) Before I go any further, let me say that this entry will do nothing to slam the Beta team; Derek and the rest of the guys and gals were wonderfully receptive and supportive the whole time.

As anyone who knows Office 2008 knows, Microsoft made the business decision to remove VBA from it. And as anyone who knows me knows, I make a large part of my living using VBA in Excel.

And I'm not going to add my voice to the sound and fury in response to Eric Schweib's blog about this subject, which you can find here.

Instead, I'm going to ask a different question:
What did we need VBA in Excel for in the first place?

Speaking for myself, it's because my various and sundry applications that I have to use don't do what I want them to, so I have to use Excel to scrub and unify that data. Granted, I can do most of the aforementioned scrubbing with some rather lengthy formulas, but macros automate even the process of inserting said formula and AutoFilling it down, thereby resulting in one less step.

Ugh.

I start to think sometimes that keeping VBA in Excel up until Excel 2004 enabled people like me to create unsustainable, house-of-cards processes that were doomed to fail eventually anyway, and taking away VBA might be the best thing for us.

On the other hand, I don't have anything cross-platform to replace it with, because I work in a mixed platform shop.

But I guess what I'm really wondering is, "Where's the VSTO for the Mac?" What I really need is the ability to create custom applications that can "hook" into Microsoft Excel, and SalesForce, and other applications.

Anyone?

Leftovers, Microwaves, and Other Unlikely Inspirations

2006-11-23T12:14:00.000-08:00

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.

Getting it Right, Part 4.1: Microsoft Outlook (Email)

2006-08-20T01:15:00.000-07:00

Keeping with the previously established tradition, let's get it out on the table what Microsoft Outlook is good for:

Sending, receiving, storing, and managing email. That sounds pretty obvious. But I'm including it here anyway, just to gain agreement with my readers on the boundaries here.
Scheduling meetings and events. More on that later, but if you work in an office that runs Microsoft Exchange Server, Outlook is about all you need to make sure that meeting announcements get to the right people.
To-do lists.

Again, to be consistent with my ongoing tradition, what is Outlook not good for? A few things jump right out at me:

Outlook sucks as a project management tool. I can remember one very specific job I had a few years ago as a programmer. My Project Manager would use Outlook to assign tasks to people. Sounds good on the surface, but the reality was, Dave had nowhere he could look to see the big picture; how what I was doing was related to Brad, or Steve, or Frank. That was the hugest problem. Not to mention, if we had to bill a client for services, there really wasn't any way to create meaningful invoices. In answer to your next question, Microsoft Project.
Outlook is mediocre as a mail merge data source. I've found, after multiple trials and errors, that when I have my contacts stored in Outlook, and I try to use that contact database as a data source in Word, then I don't have access to all the contact fields. Port it into Excel, and all is well. (For my money, though, I'd probably choose Access. That'll be my final Getting it Right topic, which will probably be a while.)
Outlook is weak for making organization-wide forms, for such functions as time off requests, expense reports, and the like. The scripting language behind Outlook is kind of weird, and not very intuitive. For my money, making forms that people can use could be either done via an Access client-server application, or via a company Intranet, with web forms.

Now, for the best Outlook practices:

Let's start with e-mail. E-mail is all about folders. I would recommend that folders should have a logical, intuitive structure. I have a client who works for an engineering firm; I recommend to people like her to put emails in folders by client name; in the case of my boss, he has one folder for each of the techs he supervises. Those are both logical, intuitive strategies.
E-mail part 2 - when you get an e-mail whose sole purpose is to say, "Hi Geoff, here's the attachment you asked for," you have a ready-made way to save space on your account. Right-click on the attachment, and save that attachment to the proper folder on your hard drive. That's part one. Part two, delete the email. Otherwise, you're hogging up server space needlessly.
Emails should be brief and to the point. If you have a long, involved process to describe, you're probably better off sending the process as an attachment (like a Word document) rather than let the email do the work.
On emails - grammar and spelling still matter, I don't care what anyone says. And yeah, spell check is all well and good, but it won't prevent malapropisms and grammatical errors. One of the most grievous malapropisms I ever saw was when a manager a few years ago happily reported that "Linda excepted a position..." Yeah, spell check let that one go through. As well it should have. But the person sending it should not have.
On email options in Tools->Options that make sense:

Tools->Options->Spelling and Grammar - check "Always check spelling before sending." As mentioned earlier, this won't solve everything, but a good place to start.
Tools->Options->Mail Format. Make at least two signatures - one internal and one external. My internal signature includes my cube number, my AIM address, and my extension. My external signature is pretty much prescribed to me by company policy. But that's OK.

On email attachments - don't just double-click on them to open them. Bad idea. Instead, right-click on the attachment, and then save the attachment somewhere useful, like My Documents. Problem is, if you just open the document, it gets saved in your TEMP directory, and is almost impossible to find later.
On email templates - some of you may know that you can create a template in Word, or Excel, or PowerPoint. But what about Outlook? Can do. Create a message with a subject line, intended recipient(s), and body. Do not include attachments. Trust me, those don't work.
Once you have the skeletal framework in place, in the message, go to File->Save As. Choose the file type "*.oft."
Put the file where prompted - probably C:\Documents and Settings\[user]\Application Data\Microsoft\Templates. That's OK.
To use that template, click on the "File" menu from Outlook, and click on "Choose Form." Click on "Personal Forms Library." You'll see your template.

More on Outlook next time; we'll talk Calendar. Until then, be well.

Bernanke vs. Bush

2006-07-01T14:47:00.000-07:00

This is not a supreme court case - fear not.

This is a catch phrase that I've developed to describe the situation that folks like me, who are known in our office as the person most knowledgeable in Excel, or Office, find ourselves in often.

Back up - Ben Bernanke is the chief of the Federal Reserve. By Bush, I am referring to our President.

Put politics aside for a second, and ponder this: For those of us who are expert at the art of numbers crunching, data analysis, and reporting (Access/Excel/both), we will often be called upon to answer a question like this: "Does it make sense that home sales mean more than gas prices in this report?"

There's a fine line we walk there - is it really ours to answer that question?

Usually, the answer is no.

Most of the time, when we get asked that question, the best way we can answer is the way I answered a colleague of mine who asked a similar question. (The details are confidential, and the questioner shall remain anonymous.) But when this person asked me, in essence, "Does it make sense that when looking at our economic stability, to put more emphasis on home sales than gas prices?" the way I answered was this: "This is where Bush meets Bernanke."

The analysis of what the numbers say is our job. The conclusion of what the numbers mean is a job for the policy makers.

To be a truly effective analyst of data, I feel it my personal duty to maintain neutrality on the issue of what the numbers mean. I concentrate on what the numbers are.

By saying that, I am not shedding the responsibility of reporting the numbers fairly and accurately. But our responsibility as analysts is to show what is there; the policy makers' job is to show what is possible.

We can get in on the policy game if we wish, but we necessarily leave behind the objectivity and remove that made us good at data analysis. In other words, Alan Greenspan would make a lousy president.

Getting It Right, Part Three: PowerPoint

2006-06-11T16:23:00.000-07:00

This is probably going to be a shorter blog entry than some of the others.

In keeping with the tradition I've started, let's get it out on the table what PowerPoint is good for:

Presentations. Big surprise there. That's what people Have been using Powerpoint for since its inception.
Signs. No kidding. If I want to make a sign that has some pizzazz, via borders, themes, and font markup, PowerPoint is actually pretty good at that.

Now, what is PowerPoint not so good at? Interesting question. I guess the best answer to that is "everything else." But seriously, folks, where I would advise against using PowerPoint:

Making charts. Hold that thought, but for now, let's just say that Excel is better.
Large, complex tables of information. I'd probably go Excel for calculations, Word for pure display of data.

Where would a Getting it Right blog entry be without the best practices? Well, glad you asked. Here are my best practices for PowerPoint. These will concentrate both on the technical aspects of putting together a presentation, and the tactical aspects of giving the presentation:

Don't go too crazy on the animation, including slide transitions. My advice would be to use one transition style, and use that transition only in the beginning and end of your presentation.
In a long presentation, use the Title style of slide to signify the beginning of a chapter. That's an appopriate place to use transitions, too.
Using music in presentations is dangerous at best. Remember - if the music is more interesting than you are, you've got trouble.
Three bullet points per slide is best. If you have to break up five bullet points, then put three on the first slide, two on the second. If you have only four bullets, you can get away with it.
If you're going to have one indented bullet point

Like this
Then make sure to have at least two. One looks silly.

Capitalize at least the first word of the bullet point.
Speaking of capitalization, I want to transition into case. Title Case is Best for the Title of a Slide, while Sentence case is best for bullet points and body text. Notice the difference?
Two fonts for a whole presentation is about right. One font for the title text, one font for the body text.
Which two fonts? I like a serif font, like Times New Roman or Georgia for my titles, with a Sans Serif font like Arial or Verdana for my body text. Notice that all the choices listed are simple, fairly straightforward fonts. That's the way I like them.
Using graphics is a good idea, as long as they're high-quality. Low-quality graphics make you look low-quality, too.
Slide masters are your best friend. Deviate from them only when absolutely necessary. Slide masters create consistency and cleanliness.
If you have to put a chart into your presentation, create the chart first in Excel, and then paste the chart into PowerPoint. That way, you can get all the data that underlies the chart nailed down. Make sure to get the colors right in the chart first, before copying it.
Speaking of colors, here's a little tip for you - if you have a company logo, or specific set of colors that you have to match against, go to http://www.download.com, and download a color picker application. I would just say search for a free one appropriate for your OS, and go from there. That way, you can get the hexadecimal values for any color on that picture, whatever it may be.
What good is that, you might ask? Well, as we all know, PowerPoint allows us to change the color of any element on the slide, from the AutoShapes to the background. When you first see the list of colors, it'll be the usual 56 suspects. But click "More Colors," and you get two choices. Choice number one is to use one of 256 Web-safe colors, which are located on the "Standard" tab. Good news is that the colors are easy to pick out. Bad news is, if you're trying to match to a specific color, it's painfully tedious. That's when you go for choice number two - "Custom." When you click on this tab, you can choose a color by its hexadecimal value instead. Make sure that the color picker application of your choosing can pick a color in terms that PowerPoint understands. Bonus feature would be if you can copy the hexadecimal values to the Clipboard.
Spell check your presentation before you save it. PERIOD!
Go to Tools, Options, Edit. Change the maximum number of undos to 99. You're welcome.

Now, as promised, a few words on mechanics:

Always arrive at your presentation location one hour before presenting.
Know where restrooms, electrical outlets, eateries, and water are located.
Know who your contact is if you have technical problems. This is especially important if it's not your equipment.
If you're bringing your own projector, bring a spare bulb as well. The only thing more expensive than a spare bulb is a lost presentation.
Check your teeth, your clothes, and your shoes before you present. And after lunch.
I don't condemn people who smoke. But I'd recommend against it the day of the presentation. With all due respect, smoke on your clothes keeps you from being approachable.
Print out an outline. Keep it on the lectern with you. Refer to it, but don't read it.
Most importantly of all - if you forget absolutely, positively, everything else I tell you, remember this. DO NOT READ DIRECTLY FROM YOUR BULLET POINTS!!!!!

Bullet points are for reference, for you and the audience. For you, they cue you what you're going to say next. For the audience, they give you a sense of what's coming next.

When closing your presentation, end with a title slide. That title slide should have one of several things on it:

A humorous quote. I like Mark Twain better than anyone else for this. Who else could say "Outside of a dog, a book is a man's best friend. Inside a dog, it's too damn hard to read."
An inspirational quote. My personal choice is Dr. Martin Luther King, Jr.; too many to name, but start with "Everyone can be great, because everyone can serve."
A quote from a person respected in your profession, or that your common group can agree on. If you're presenting before a religious group, then a quote or verse from your belief system is a good way to go; if you all work for the same company, then a quote from the founder or the CEO would be appropriate.
Sidenote on that - if you are a religious person, I would advise against a religious quote, unless your crowd is entirely of your belief system. Otherwise, you risk offending someone who is not. Same goes for political quotes. If I'm going to quote a politician, I usually go for someone respected on both sides of the aisle, like JFK, Franklin D. Roosevelt, or Churchill.
A call to action is effective, if your presentation is action-oriented. Buy my product, join my club, take on my point of view. Again, as appropriate.
If you're absolutely out of ideas, the best way to end a presentation is with a title slide that just has your contact information, and something to the effect of, "Please feel free to contact me with any questions." That works.

The bottom line, my friends, is that PowerPoint is probably the easiest application in the Microsoft Office suite to learn. It also comes with the greatest pitfalls. It's easy to get caught up in the animation, transitions, and fanciness. Don't. Get it out of your system before you present, or you'll learn to regret it. PowerPoint gives you the best chance to put yourself in public, but also, the best chance to make a fool out of yourself in public. Don't let it. You're better than that.

The next blog will be on Outlook, which will be a little bit of many pieces. Myths will be dispelled, and truths will be revealed. Until then, be well.

Getting it Right, Part Two: Microsoft Excel

2005-10-03T22:53:00.000-07:00

It's been a while. I've been trying to wrestle this complex beast by the horns.

I have no illusions that I'll be successful. But here goes nothing.

First, from the Institute of Duh. Microsoft Excel is a spreadsheet program. So here's what Excel is:

A powerful tool for aggregating numeric data
An effective way to make low to mid-level financial projections
A useful tool for scrubbing and analyzing data dumps

Here's what Excel is not:

A database application. Say it with me - Excel is not a database application. Repeat as often as necessary.
A project management tool. Don't ask me why anyone would try to make it into a project management tool, a task system, a scheduler, or a calendar. It's beyond me.

Now, about how to succeed with Excel. Just as I previously wrote about with Word, there are some general best practices that I've acquired from some of the greats, and am here now to present in a condensed format:

Instead of KISS (Keep It Simple, Stupid), it's KIDD. (Keep It Dynamic, Dude.) Instead of putting 2 in A1, 2 in a2, and then "=2+2" in A3, make A3 instead say "=SUM(A1:A2)". That way, you can always change the value of A1 or A2, knowing full well that the value of your total will change instantaneously.
Give up trying to learn every last function in Excel. Repeat - FORGET IT! The best thing to do is figure out instead what it is you want to do, and then work backwards from the desired result. Believe it or not, with some practice and some creative Googling, you'll get there eventually.
So what if you don't find a function that does something you're planning on using all the time? OK, write your own. Easier than it sounds. Stay tuned for a future blog on some quick and dirty functions I've used.
For you macro junkies out there (guilty, Your Honor), try storing your macros in an XLA file rather than in "PERSONAL.XLS." Too much stuff in PERSONAL.XLS can often result in it getting needlessly bogged down.
In that future blog about macros, I promise also to talk about how to make an Excel add-in. Good way to go if you want to have individual chunks of code for individual applications.
Fo the record, PERSONAL.XLS is in C:\Documents and Settings\[User]\Microsoft Office\Excel\XLSTART\. That's also a handy directory to have if there are any files that you want to have load at startup in Excel.
Make an absolute religion out of templates. No different than Word in this respect; make sure that your templates have the following properties:

The cells containing critical formulas are protected
The cells where you want an end user to input data are not protected
Any workbooks that will be going to an external client are free of comments
Don't just protect the cells. Protect the worksheets. If you don't protect the sheets, people can delete those worksheets. That might be bad, what do you think?

Speaking of templates, the ultimate template secret is in XLSTART.

Set up a blank workbook with all the parameters you want, from margins to number of worksheets.
Save the file as "Book.xlt"
Save the file in C:\Documents and Settings\[User]\Microsoft Office\Excel\XLSTART\
There'll be a "Book.xlt" file there already; when prompted, you can say "Yes" to the replacement
When you quit and re-open Excel, your workbook becomes the default.

Good news - the AutoCorrect settings and the Spell Check information that I discussed in the previous blog carries over to Excel. (As well as all the other Office applications. But Excel is the current area of focus here.)
Useful, quick-and-dirty auditing tool: Hold down the CTRL key and hit the Tilde (`) key. This will allow you to toggle back and forth between formulas and values. (Yes, you can print that information.)
If you have an enormous sheet, that bogs down Excel every time you open it, try this: Go to Tools->Options->Calculation. Temporarily change it to "Manual." Make sure you remember that you made this change. If not, you may forget later.
In fact, spend some time and get comfortable with Tools->Options. You can change your default file save location, the default number of worksheets in a book, and default chart colors.
This is unusual for me to recommend registry hacks, but I have to make an exception in this case. By the way, if you're not comfortable making potentially dangerous modifications to your computer, please skip to the next bullet.

Go to Start->Run->Regedit.
Click on the folder that says "HKEY_CURRENT_USER"
Click on the sub-folder for "Software"
Click on the sub-folder for "Microsoft"
Click on the sub-folder for "Office"
Click on the sub-folder for the highest number. I have Office XP, so that number is 10 for me.
Click on the sub-folder for "Excel."
Click on the sub-folder for "Options."
On the right side, right-click, and choose "New->DWORD Value."
The name of the value should be "UndoHistory."
The base should be "Decimal," rather than the default, "Hexadecimal."
Set the number to anything between 1 and 100.

A couple of final notes:

Excel documents tend to get corrupted when you put them on a network drive, and people are trying to open and re-open them. Watch out for that. If a large number of people are trying to open and change the same book over and over again, you're probably trying to treat Excel like a database. Stop it right now.

Excel is an OK application for forms. Access is way better. Don't forget - Access can do calculations, too. Access is a lot better at storing large amounts of data.

The bottom line, my friends, is that Excel is not really such a great way to store large amounts of data, but it provides some very elegant and graphically appealing ways to display a slice of data. Excel is well-suited to grab a chunk of data from an external source and analyze it, but the external source is probably better suited to store the underlying data. Division of labor, that's what it is.

The next blog will be a little bit of a rest stop for both of us - PowerPoint. Until then, be well.

Getting it Right, Part One: Microsoft Word

2005-08-20T15:20:00.000-07:00

This blog is going to be in two parts. Whether or not I successfully join the two parts together at their junction, well, that remains to be seen. Like hanging cabinets (long story), that can prove trickier than it seems at first.

I read a lot of books about Microsoft Office. I can say with absolute, unwavering confidence that the number is in double digits. It's probably pretty close to my age. While I spent three and a half years in the Office training trenches, I obsessed with the mission of learning it all, of slaking the unquenchable thirst for Office knowledge and understanding.

I just finished reading a book by a guy called A.J. Jacobs titled The Know-it-All: One Man's Humble Quest to Become the Smartest Person in the World. This guy read the entire Encyclopedia Britannica. As in, all 32 volumes.

I know exactly how he feels.

But I fear Jacobs and I both suffer from the same problem in our quest - we may be gaining knowledge, but really lacking in understanding. In both of our bookwormish quests to fill our brains with really great tidbits of trivia, I fear there was there missing a larger understanding of the why and wherefore.

I quote David Weinberger: "Knowledge without understanding is like, well, information."

So what I want to do in the second part of this blog is shed some light on where each of the five core Office applications fit into the organizational and business scheme of things, and how to best succeed with each one. I'll do them in my own chronological order, as in, how I learned them. To save both my fingers and my readers' eyes, I'll address one application per blog. Today's discourse is going to be my least favorite of the five: Microsoft Word.

Microsoft Word is, above all other things, a word processor.

Duh.

Yeah, I know. But I have to make this overly obvious finding from the Institute of Duh first so I can talk meaningfully about what Word is and what Word isn't.
Word is:

An effective tool for letters, memos, and reports
A useful way to manage large, complex documents
One of the more effective ways to send out a mass mailing (marketing letter, holiday card, etc.)

Word is not:

A page layout or publishing application. For anything more complex than a company newsletter, I personally think Word is the pits.
A calculation application. I kid you not, I used to get all kinds of questions about how to do calculations in tables, use of Equation Editor, and how to do charts. In one word: Excel.
A web design studio. I know, Word has this oft ballyhooed feature called "Save as Web Page." I implore you not to use it for that. The code it produces is horrifically unwieldy.

Now, about how to succeed with Word. There are a couple of best practices that we should all keep in mind. I certainly didn't invent any of these. I just try to pass on what I've learned from the trenches:

Anything worth doing is worth doing well. Anything worth doing well is worth making a template out of.
As long as you're going to have a template, make sure to embed styles into that template. If nothing else, change the Normal style to something more interesting than TIMES NEW ROMAN 12, FOR THE LOVE OF GOD!
Use styles instead of local formatting. You'll thank me later.
Eschew the use of Format Painter. It's a wolf in sheep's clothing.
If you have templates for the whole group, make good and sure to go to Tools->Options->File Locations, and point your "group templates" to a shared, network directory.
If you want to kick it up a notch, export the following registry key:
Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\General\SharedTemplates
(in my case, it's 10.0, because I use Office XP; 2000 would be 9, 2003 would be 11.)
Kick it up another notch and have your administrator "push" that key to everyone in your office, so that everyone has their Group Templates set to that specified directory.
Use AutoCorrect to your advantage. I would very highly recommend getting rid of "Automatically capitalize first letter of sentences," ditto table cells. I would recommend adding entries of your own for your initials, your company name, and any industry lingo you use often.
Again, if you want to share that information, ask your administrator's assistance in "pushing" out MSO1033.acl, which is usually located in C:\Documents and Settings\[user]\Application Data\Microsoft\Office\ ; that's your AutoCorrect entries.
Don't trust Normal.DOT further than you can throw it. If your Word starts acting weird, delete NORMAL.DOT. You can hunt it down at C:\Documents and Settings\[user]\Application Data\Microsoft\Templates\.
Add industry terms, lingo, and proper names to your custom dictionary. When you get the dreaded red squiggly, right-click on the word and choose "Add to Dictionary."
In answer to your next question, the file is called "CUSTOM.DIC," and it's C:\Documents and Settings\[user]\Application Data\Microsoft\Proof\.
If you're thinking of putting one person in charge of adding all the words to the custom dictionary, take heart - this is actually a file you can open and modify. So if you work in a field that has a fair amount of industry-specific terms (science, medicine, law), you might get one of your fast-fingered colleagues to create a base custom dictionary for the office.
Remember, though, that Spell Check doesn't check for word misuse - if you say, "Linda excepted the job offer," when you should have said "Linda accepted the job offer," Word won't nail you.
Now the good news - words you add to Spell Check are not the exclusive domain of Word. What you add in Word's spell check carries over to the other four applications, plus Project.
Turn off Grammar Check. I, for one, think that it does litle more than give a false sense of security as to the readability of your document. This is a time to have someone just read the document, and look for consistency of voice, tone, and style.

In short, my dear readers, I would ask you to remember that Word operates on the principle of "Type first, format later." Your best bet is to get all your text down on the page, then go back and apply styles appropriately. Realize that if you can't get Word to listen to your formatting requests, you can always highlight the text, and then hit SHIFT+CTRL+N. That will set the text back to "normal."

In my next blog, I'll take on the biggest of all the beasts: Excel. Until then, be well.

Professional Looking Word Documents

2005-08-06T20:20:00.000-07:00

I've seen a lot of Word documents in the course of my professional existence.

Problem is, most of them are poorly done. Some of the major mistakes I see:

Misspellings
Misused words, malapropisms, and inappropriate tenses ("the bird flapped it's wings" is incorrect; the correct phrasing is "the bird flapped its wings"; "disinterested" means impartial, not that you don't care)
Incorrect page numbers
Missing pages
Inconsistent use of fonts, sizes, and markup

The thing I find really puzzling is that most of these are so easily avoided.

So I'd like to offer a couple of best practices to make long documents really work in Microsoft Word. Some of them will sound like findings straight from the University of Duh, while, hopefully, others will be from the University of Aha!

Use styles consistently for your headers. Use at least two levels - the first level is the chapter name, the second level is the sub-topic under the chapter. Longer documents may require a third or fourth level of heading. You make the call - does the document justify it? I've done training for federal government and private sector organizations dealing with some fairly complex regulatory information, and four levels of header is not unheard of. Look at your subject matter.
Make an absolute religion out of using sections. Make your title page one section, your TOC another section, and each chapter in your document its own section. If you have an index (which you really should), make that its own section.
As mentioned in #2, you should have a TOC, an index, and a title page.
In the header for Chapter 1, go to View->Header and Footer. Then, go to Format->Borders and Shading. Put a border on the bottom, and apply it to the paragraph. Make the header contain the chapter title. In Chapter 2, and each successive chapter, go to View->Header and Footer. Click on the button that says "Same as Previous" to break the link between Chapter 2 and the previous section. The good news is that the border stays, but the chapter name can be different for each chapter.
In the footer, just keep the page number. Let that be consistent throughout the document, with the exception of the TOC and the title page. If you want, you can have the TOC and any other supplementary information (acknowledgements, copyright information, stuff like that) be all one section, and have the page numbering be Roman (i, ii, iii) instead of Arabic (1,2,3).
I would recommend using Next Page section breaks instead of Odd Page or Even Page section breaks. Odd and Even Page section breaks really only work if you're willing to have the page be COMPLETELY blank, devoid even of a page number. If you want a blank page at the end with the header and footer information, I'd use page breaks, rather than odd or even page section breaks; that way, I have more control over the document.
Indexes are a good idea if the reader is going to use your document primarily to look for specific terms, rather than read through a concept. If that's the case, a well-developed index will save your reader hours of time. Granted, it'll take us writers some time, but so what? We're here to serve the needs of our readers. End of discussion.
Small thing, but one that annoys the living daylights out of me: If you're going to use images (figures, illustrations, photos, etc) make sure they're high quality. Nothing says "I don't care what you think, Thou Reader" (thank you Walt Whitman) like a crappy image you downloaded off of someone's website and then (gasp!) enlarged. Just don't do it. Don't strain your readers eyes that way, and don't destroy your professional credibility in the first place. Get a good source image you can downsample, and go from there.
As far as images go, one other point - ClipArt and WordArt rarely if ever look professional and clean. You're better off not risking it.
Keep your markup to a minimum. Use bold and italics sparingly. Don't use sparkle text at all. Don't ever use underline, unless it's to reference a website.

Remember, above all, that your document is for the reader. Make it work for them, not for you. That's priority #1.

Grace Under Exit Sign

2005-07-23T15:21:00.000-07:00

I never thought this day would come. I really could have seen myself working at New Horizons Computer Learning Center in Sacramento for a good long time. 3 1/2 years is a pretty good tenure at a company for people my age.

But the call I never thought would come did.

Apple.

So now, I am going to do my most personal post to date, while at the same time be faithful to my mission to keep this blog on the Office target.

If you find yourself in my situation, where you're going to be leaving your current position and/or company, I would like to offer some best practices:

NEVER delete projects, files, or documentation that another person will need. Files that you create on a company computer on company time are company property. Same for email.
Also on email - I would suggest to you that it's unethical at best to take the names and phone numbers of your clients from your current company and try to solicit business from them in your new capacity.
Put the power of Word to work to make extensive documentation of any programs, processes, or procedures where you are the go-to guru. If you're like me, and you have to make a lot of documentation where you have to take screen shots, check out a program called ScreenHunter Free, which is from Wisdom-Soft (http://www.wisdom-soft.com); it's a great little tool to grab some screen, and paste it into Word. Check for a future blog on best documentation practices in Word.
If you've password protected any Word or Excel forms, make sure to give an administrator those passwords. Remember, they can get around your passwords if they have to, but make it easy, and don't make them do it. Same goes if you administered or created an Access database - be sure to give someone else administrative rights to the database so they can pick up where you left off.
If you created any custom VBA code for any of your in-house documents, make sure to painstakingly comment it, so that, again, another person can pick up where you left off.
If you have any clients to whom you have made promises, be sure to create an agreement with that client on how that promise can be fulfilled in your absence.
Make sure that if you're close to any of your colleagues, to tell them how much you've appreciated working with them, and give them a reason to want to stay in touch with you.
Never badmouth the company you're leaving; you never know when you may have to go back and ask for a job.
In you resignation letter, be brief and to the point. Make sure to end on a positive, encouraging note; that will contribute significantly to how people will remember you.
Most of all, remember that you are unique, valuable, and important. But you're neither indispensable nor replaceable. Act accordingly.

Outlook tasks vs. Project

2005-07-07T20:44:00.000-07:00

Which is better? For those of you who read my post on Excel versus Access, then you probably know what the next part is - "it depends." You should have seen that one coming.

So, more to the point: I use Outlook when I need only manage my own tasks, and I'm not billing someone else for my time. I can use Outlook to assign tasks to my co-workers, as long as I'm not concerned about how Pete's finish date affects Mel's start date, and that finish date doesn't adversely affect the project's ability to finish on time. Outlook is effective for managing independent tasks.

Project is a little different ball game. (to put it mildly!) I use Project when I have Bill and Russell hanging drywall in my kitchen, and Willie the electrician has to re-wire the outlets before the drywall is complete, and then Dave the plumber can't do the piping under the sink until Russell tears out the cabinet under the sink. Project is very effective for managing interdependent tasks.

Outlook tasks are a good way to manage deadlines - I train some law firms who use Outlook tasks for filing dates, and then the Calendar for their hearings. Remember, the filing of a document is a date by which it has to be done, whereas a Calendar appointment is a finite start and end period on the clock.

Project tasks don't really get so much into the question of, "From 8:00 AM to 1:30 PM, I have to be in court." Project is more concerned with, "Willie has to install the ceiling fan in the kitchen after Bill and Russell tear out the cabinets above the sink." Further, Project is very effective at calculating the number of days ahead. In other words, if I say that a task is going to take 5 days, and I can start it on the 21st, then Project helps calculate what the end date will be.

Outlook is good about filling in the holidays on my Calendar, like Thanksgiving and Christmas. Project says, "Tell me what days Pete can't work, what days Mel can work overtime, and what days the office is closed altogether."

Last point - Outlook is a better quick-and-dirty solution; Project is a better large-scale solution that requires more of a time investment at the outset. Project is extremely effective at managing resources, both work and material. Work is people like Russell and Bill; materials are stuff like drywall, nails, studs, and paint. Project can itemize those costs and summarize them on a report for a client.

Outlook can't do all that, but if you don't care, Outlook is a much simpler program, ounce for ounce.

So consider both options; like Excel and Access, they are far from mutually exclusive. Act accordingly.

Effective VBA

2005-06-19T23:24:00.000-07:00

I love VBA. VBA, if you've never heard the term before, stands for Visual Basic for Applications.

What that means, simply is that it's a programming language - a way to automate processes in Microsoft Office, and make your existing processes go faster.

Before I go any further, though, in talking about when to use VBA, I want to talk about when not to use VBA:

To make a program act like another. For example, if I'm using Excel VBA to automatically add a number to a row, or to require data in a field, or to aggregate data from a form, I might as well use Access.
To replicate existing functionality of a program. For example, if Excel already has a button/command to do Paste Special->Values, then there's no need for me to make a macro to do that. (Oops, too late, already did.) But there's a case where if you go to Tools->Customize in your program (like Excel), then you might find that the functionality you want already exists. In the case of Paste Special, Values, the button did exist; I just had to go to Tools->Customize, and look under "Edit," and there it was.

Now, having said both of those things, let me offer a disclaimer: As far as #1 goes, I've given people advice on the Excel Community forum at Microsoft's web site on how to automatically generate a new invoice number every time a person opens a workbook template. In many cases, Access can do it better, but there's no one at the office who can support Access, and/or there's a license issue. OK, that makes VBA the best choice. Fair enough.

I digress. When to use VBA is also two part:

To make one step out of six or seven steps. For instance if I want to do three or four steps over and over again, I would record those steps in a macro. As anyone who has ever taken Excel Level 3 with me will tell you, I usually have people record the steps of giving a cell a blue background, white text, center aligned, Arial font, size 9. (I sometimes mix it up how many of those steps I have people do.) In this case, all you do is just have the program (in this case, Excel) follow you through the steps, so that next time, all you'll have to do to replicate them is to run the macro.
To build a better mousetrap. This is where you not only record the steps, but then add some conditional logic and/or control constructs to it. The conditional logic part is to say, for example, "if the workbook is blank, don't do anything to it," while a control construct might be, "execute this code once for every worksheet in the book" kind of thing.

Frankly, I do most of my VBA in Excel, because it's what I use the most. I've done VBA in Word, but I'm still having a hell of a time learning the object model. The object model means how the entities are related; in Excel, for example, the Workbook is an object, which contains Worksheets, and those contain cells, which can be selected. Word isn't that consistent.

One can do an extensive amount of VBA in Access. I have barely scratched the surface of its power. Remember, macros in Access are different than macros in Excel. A macro in Access means assembling steps using the Macro editor. To do VBA in Access means creating what's called a module. Either way, you can't have Access record the steps. In Excel, Word, or PowerPoint, you can have the program record your steps and play them back later.

Back to the example I brought up earlier about automating the process of changing the background color, font, and stuff like that. I had my class the other day go into a certain file called "OfficeSupplies" (which can be downloaded from the New Horizons website, by clicking this link, which is to a Zip file. What I have the class do is start with the "Australian" worksheet, and then record the steps of various formatting, like currency formatting the dollars, bolding the title, and stuff like that. The macro that I have the folks create initally looks like this:

Sub formatWorksheetLongWay()
' formatWorksheetLongWay Macro
' Macro recorded 6/20/2005 by Geoff Lilley
Range("A1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A3:A7,B3:E3").Select
Range("B3").Activate
Selection.Font.Bold = True
Range("B4:E9").Select
Selection.Style = "Currency"
Range("B9:E9").Select
Selection.Font.Bold = True
End Sub

This code suffers from two problems. Number one, I have to manually apply the formatting to each individual worksheet, in turn. Two, it's ridiculously bloated, which is going to slow down my processing time long-term.

I re-wrote the code to be more efficient; this code loops through all the books, and then does all the same formatting to each one, except "Chart1."

Sub ColorRegions()
'variable to figure out what sheet number we're on
Dim SheetNum As Byte
'variable to figure out how many sheets are in the book
Dim SheetsCount As Byte
'move the chart worksheet to the end
Sheets("Chart1").Select
Sheets("Chart1").Move After:=Sheets(ActiveWorkbook.Sheets.Count)
'start with the first sheet
SheetNum = 1
'do this stuff to every sheet but "Chart1" by looping through the sheets
SheetsCount = ActiveWorkbook.Sheets.Count
Do Until SheetNum = SheetsCount
'select each sheet in turn, starting with the first
ActiveWorkbook.Sheets(SheetNum).Select
'take the title, make it bold and centered, white text, blue background
Range("A1:E1").Select
With Selection
.Interior.ColorIndex = 5
.HorizontalAlignment = xlCenter
.MergeCells = True
.Font.Name = "Arial"
.Font.Size = 14
.Font.ColorIndex = 2
.Font.Bold = True
End With
'bold the titles on the left and top
Range("A3:A7", "B3:E3").Select
Selection.Font.Bold = True
Range("B4:E7", "B9:E9").Select
'currency format the numeric cells, including the totals
Selection.Style = "Currency"
'go to the next sheet
SheetNum = SheetNum + 1
Loop
End Sub

In essence, I just cut down the code to the necessary pieces, and then wrapped the whole thing in a "Do" loop. The loop just says, "do this stuff once to each sheet in the book, until you get to the end."

This is an example of VBA for quick and dirty fixes. I highly recommend it as a solution. If you live in the Sacramento area, and you want to learn more about Excel and macros, call the New Horizons main line at (916) 641-8500. As it stands right now, we have to generate enough interest from people to get a full VBA class together. If we get the interest, either me or one of my colleagues can teach it. But we need the demand first. So let people know it can be done!

Best Way to Make Forms in Office

2005-06-16T23:01:00.000-07:00

I get this question a lot. What's the best way to make a form to fill out?

I have several different answers to this question, each with their own relative merits and drawbacks. The guiding question is always, "What do you want to do with the form when the person is done filling it out?"

Create the form in Word. If you want to go this route, there are two tools that will serve you well - the Forms toolbar, and tables. Put the label for the data (like the word "name" or the word "address" in the left column of the row, and the form field where you want the person to put the name, or address, or phone number, in the right column of the row.

Use this method if you want to create a quick and dirty form that will be printed out and mailed to you, or emailed to you.

Don't use this method if you're concerned about maximum number of characters in a field, and/or you're worried about populating this information into a central data location later.
Create the form in Excel. If you want to go this route, the two tools you'll find most useful are validation and protection. Use Data->Validation to restrict what kind of values a person can put into a cell, like minimum and maximum numeric values, and in-cell drop-down lists, using Data->Validation, with the "allow" property being set to "list."

Use this method if your primary concern is getting valid data within a given threshold. Compared to Word, still pretty quick and dirty, and still perfectly serviceable for printout and/or email.

Don't use this method if you have to populate the data into a central data location later. You can do it, but it's clunky and inelegant. But better than Word in this regard.
Create an Access database. This will require first creating the table(s) to hold the data, then the forms to enter the data. Probably, you'll want to implement some kind of security, so that your form users don't see your back-end functionality.

Use this method if you're only dealing with internal clients (people in your office, or people who work for your company in other locations who can access the same server(s) you can.) This method is the best of the Office programs for validating data to ensure its compliance with your standards, and has the best built-in method for making sure that required data gets filled in.

Don't use this method if you have to make your form available to people outside your company.

Other solutions to the problem that I've heard batted around, going outside the traditional confines of Office, also come with ad and disadvantages:

InfoPath. This is a new product for Office 2003. It is a very elegant, easy-to-use forms designer.

Use this method if you have a SharePoint website for your company where people can go and fill out your form on the team and/or company SharePoint site. You can also use this method if you already have an Access database created, and you want to make the InfoPath form talk to the Access database.

Don't use this method if you have to publish your form to external parties; their likelihood of having the tools needed is pretty low.
Acrobat forms. Acrobat 6 and 7 both have pretty robust, usable tools to create radio buttons, check boxes, and drop-down menus. If you know your way around Acrobat JavaScript, you can extend the functionality of the forms quite a bit. With 7, there's a separate Form Designer product (comes with the Professional version of Acrobat, I believe) that makes form design a pretty painless process.

Use this method if you have to publish your form for use by the broader general public, and you want to give that general public the chance to fill in the data in a form. You can then either have them postal mail it to you, or submit it to a database.

Don't use this method unless you have a respectable knowledge of Acrobat and of basic web design; the way to make certain functionality work in Acrobat forms requires a little of each.
Web forms. You can put a form on a website that a person can fill out, right there on your website, and have that form data go to a database, get emailed to you, or any combination thereof.

Use this method to make your form most widely available to the general public. This method requires you either are or know someone who can do both web programming and database design. This method is very effective at allowing you to aggregate data into a database. To make it work, it requires that you have a web site that can support a web language, like ASP, PHP, JSP, CGI, or ColdFusion. (There are many others; those are just the first few I can think of.)

Don't use this method if you don't need to aggregate your data into a database; it's kind of overkill, and comes with a certain human-hour cost. Don't use this method if you don't have access to competent web design, programming, and database design resources, or don't have the time to learn them.

Personally, given my choice, I'll usually use a web form that uses ColdFusion to talk to a database, and fill that database with the form values. Then, again, using ColdFusion, I can write code to email me a notification that a new form submission has occurred, and the person who submitted it can get a "thank you" email. I say ColdFusion because that's what I was trained in, not necessarily because it's better than ASP, PHP, JSP, or CGI.

More than anything, look to see what others have done, including cases of what you do and don't like. Hopefully, that'll help guide your process better.

Why doesn't everyone use templates?

2005-06-15T22:10:00.000-07:00

This is something I've never understood. Why don't more people use templates in their office?

Templates are a way to standardize what comes in and what goes out. Templates are a way to make sure we're all on the same sheet of music, when creating such standard documents as purchase orders, cover letters, confirmation letters, presentations about our company, and expense reports.

I've heard people say that standardization can suppress creativity. OK, fair enough. But the way I see it, this is not the kind of stuff that we want to spend our creative resources on; this is the kind of stuff where the compelling interest to project a unified front is far more important than the compelling interest to be creative.

Save creativity for new ideas about how to manage resources, how to promote and position products, how to sell and market services.

Here's a couple of ways to think about how to use templates in the various Office applications:

In Word, templates are a really effective way to store styles and macros. So if you create styles that reflect the company look and feel, you can check the box that says "add to template," and then the template becomes a container for those styles - the two go together. As far as macros go, if the automation provided by the aforementioned macros is specific to the template, why not?
In Excel, create templates, like an expense report, where the expense types are fully spelled out and known quantities. Protect the cells that have the formulas (total miles travelled; reimbursable rate times miles travelled). Use Data->Validation to establish upper and lower thresholds for values put into those cells. Use Data->Validation->List to ensure that a person has to choose their name from a drop-down that's in another set of cells.
In PowerPoint, create a template with your company logo and colors. Put standard company fonts into the Slide Master and/or Title Master, so that everyone begins with the same look and feel.
In Outlook, you can use templates to create a standard message, like a confirmation of purchase, or a request for payment.

In all four cases, create the look you want in the file. That means everything - margins, headers, footers, backgrounds, fonts, colors, the whole bit.

Also, in all four cases, File->Save As. Give the file a meaningful name ("Expense Report" or "Confirmation Message," for example.)

Make sure to go to the bottom, where it says, "Save As Type." Change the type to "template." In Word, it's "Document Template;" in Excel and Outlook, it's just "Template;" in PowerPoint, it's "Design Template."

Once you click on the option for the program in question, you will find yourself taken to a whole new folder, called just "templates." That folder, by default, is in "C:\Documents and Settings\user\Application Data\Microsoft\Templates." This is normal; don't be alarmed. Go ahead and let the program save the template there. Hit "Save," and then close the template file.

Pause. How can you modify your templates directory so that other people can share your templates? Easy. First, open Word. Go to Tools, Options, File Locations. What I generally recommend to people is to change the setting called "Workgroup Templates." In my case, I changed my "Workgroup Templates" on my own computer to a folder called "NHCLC Templates," with NHCLC standing for New Horizons Computer Learning Center, where I work.

For the first three programs mentioned, the way you access the template to use it is to go to your "File" menu, and choose "New." I'll describe Outlook's method shortly. If you use Office 2000 or previous, you'll get a window that says "Templates." Yours will be listed under the tab called "General."

If I had modified my "Workgroup Templates" setting in Word, then any templates I save in the "NHCLC Templates" folder will also show up in the tab called "General."

To get the templates that I created to show up in a separate tab, what I would do is put sub-folders under "NHCLC Templates," like "Reports," and "Personnel." If I do that, then the names of the folders show up as tabs in the "Templates" window. That would be a sound idea if you have a fairly extensive array of templates.

If you want to ensure that everyone in your office uses your templates, the best thing to do is get buy-in at the top levels. If you do, then you can get your administrator to copy the registry key that tells people where their workgroup templates are located. The key, by the way, is called "SharedTemplates," and it's under "My Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\General."

Remember, the number "10" in my case refers to the fact that I run Office XP. For 2003, it's "11," and for 2000, it's going to be "9," and so forth. But, if in doubt, you can do a registry key search for "SharedTemplates," and you're good to go.

Once you've found the one you want, just double-click on it. Remember, the best part of all is that you're not overwriting the template when you use this method. You're starting a new document based on it. It's a very elegant, scalable solution.

Back to Outlook. The way you create and use an Outlook template is slightly different. Before you start composing your template message, go to Tools->Options->Mail Format, and make sure that "Use Microsoft Word to edit e-mail messages" is turned off. Personally, I think this so-called "feature" sucks, but that's another story.

Once you've hit "OK" on that change, start a new mail message. Put in that message any information that will be constant for every time you send it. I might create a message addressed to my training group, subject "Timesheets are Due," with the message text being, "Timesheets are due this Friday." That's keeping it nice and generic; it's not date-specific.

Once that's in place, in the message, go to "File," and choose "Save As." Again, as above, save the file as a template.

Close the message. When it asks "Do you want to save changes?" you can say no.

To use the template, you go to your "File" menu, and click on "Choose Form" (towards the bottom.) At the top, where it says "Look In," choose "User Templates in File System." That's where you'll find yours. Click "Open," and you're good to go.

Templates, my friends, will save you infinite amounts of doing and re-doing, and making sure everyone has the most current version of this or that. Make templates not just a policy at your office. Make them a religion. Amen!

Word Tables

2005-06-14T21:17:00.000-07:00

I hate Word tables.

I have never had much luck getting Word tables to cooperate with me. Either they fall completely off the page, or the column width gets all jacked up.

I have, however, found a few useful workarounds for this problem:

1) Convert the table to text. Get anywhere in the table, and click on the Table menu. Choose Convert, and then "Table to Text." That will often take care of the problem; you can use tabs to get the text looking how you want. Once you've done that, reverse the steps: Table->Convert->Text to Table. Make sure you use "Tabs" as your delimeter. That might work.
2) Instead of two tables on the same page with different column widths, experiment with splitting cells. On a row where you need more cells, right-click in one of the cells, and choose "Split Cells." You get prompted whether you want to create multiple columns, rows, or both.
3) Use #2 in conjunction with merged cells. Same deal; highlight the two or more cells, right-click, and choose "merge."
4) When all else fails, highlight the table, cut it, and paste the damn thing into Excel. Get it just right there. Then paste back into Word.

None of those solutions are perfect by any means. But the splitting and merging cells is the best I've found so far. Sure beats setting tab stops for stuff like phone lists, resumes, and any other document where structured data is at a premium.

Excel versus Access

2005-06-13T20:33:00.000-07:00

OK. Let me begin by saying that I am like all the other 3,999,999 Office users out there, in thinking that Excel is about as cool as cool gets. Excel is friendly, intuitive, "come on in, the water's fine." Excel is familiar.

Access, by contrast, seems cold, dark, distant, and mysterious, "don't go in there, you might get hurt..." kind of a sense. Access doesn't understand Excel's Undo. Access feels foreboding.

Access talks in strange terms, like "referential integrity" and "bound column," while Excel uses friendly, familiar terms like "column" and "row."

But here's the secret, friends: they're both useful. Both have their place and their good.

Excel is useful for two reasons: number one, because it's relatively easy to get in there and start working. Reason number two, because it's great for financial modeling.

Access is also useful for two reasons: number one, because it handles immensely complex, interrelated data with relative ease. Number two, because it make your finished product look like a stand-alone application, with much of the dark underside of functionality hidden from the user.

If I had to keep a list of customers, with names, phone numbers, and addresses, I'd just use Excel.

If I had to interrelate those customers, their orders, the products ordered, the shippers who sent the orders, and the employees who took the orders, I'd go with Access.

I just wanted to give a shout out of warning to those who try to avoid having to use Access by doing complex, processor-intensive VLOOOKUP, INDEX, MATCH, and OFFSET functions to get a result. The biggest problem you'll run into is that you're needlessly replicating the functionality that Access gives you right out of the box.

Let it.