The Password Function

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

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:

1. Figure out the earliest time that someone could have touched the ticket
2. 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!

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):

1. Count instances of a string (how many times does the letter "e" show up in "Geoff"
2. Find the nth instance of a string (find where the 3rd "g" shows up in a cell's text)
3. An ORDINAL function
4. A work hours elapsed function
5. 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!

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

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):

1. 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.
2. 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.
3. 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

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:

1. 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.
2. Where printers and other supply-based devices are concerned, does the device have adequate supplies? Ink is top on that list, by the way. :)
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
   
10. 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

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?

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:

1. Control structures: For-Next, For-Each, Do-While, Do-Until
2. Conditional logic: If-Then, If-Then-Else, Select Case
3. Basics of objects, properties, methods, events
4. The VBA IDE
5. Variable types: numeric and string, and in between
   
6. Error messages: some of the more common ones, and what do they mean?
7. Error types: Syntax errors, logic errors, and runtime errors
8. Error trapping: How can you validate user input to ensure that your assumptions and dependencies have been satisfied?
9. How do you deploy/install your solution on another user's computer?
10. 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:

1. You have the following VBA code. When you run it, you get an error 1004. Why?
2. Your program keeps getting stuck in an infinite loop. Identify the problematic line of code.
3. What is a "Type mismatch" error, and how can you prevent it from happening in the future?
4. How can you make sure that your end user only enters in a 10-digit number into your form field called Ticket Number?
5. 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:

1. What's wrong with this code?
2. What does this code do?
3. Write some code that solves the following problem
4. Record some code, then make it run faster
5. 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:

1. Subroutines that are executed, usually by the OnClick event of a form control's button
2. Custom functions
3. 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

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.

1. 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.)
2. 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?
3. 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:
1. Color Schemes
2. Fonts
3. Backgrounds
4. Animation
4. 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.
5. 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.
6. 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?
7. 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.
8. More stock photos, less clip art in the Gallery, both online and installed. Clip art plain SUCKS.
9. 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.
10. 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:

1. 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?
2. 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.
3. More wizards - Macro wizards this time. There are some basic functions that I shouldn't have to think about anymore:
1. The No Records macro for the report; just let me plug in a message and the Wizard does the rest.
2. 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.
3. 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.
4. Searching
1. Regular expressions in my searches would be SUPERB, especially in queries
   
2. More wildcards, including SOUNDEX
5. 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.

1. 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.
2. Improved macro recording. Let me CLICK buttons, for the love of God! That's a grievous shortcoming in the Word macro procedure.
3. 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.
4. 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?
5. 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

1. 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.
2. I'd love to see Microsoft offer a Premium Live service of some kind that allows Rules and Folder Sharing.
3. Outlook forms shouldn't be as esoteric or difficult as they are. Having pre-fabricated forms available for download online would be superb.
4. 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.
5. 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.

1. 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.
2. 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.
3. 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.
4. There's a few text functions I'd like to see added or improved:
1. 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")
2. 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.
3. The ORDINAL function might write 1st, 2nd, 3rd, that kind of thing? =ORDINAL(A1) would return "1st" if A1 contains 1.
4. 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.
5. 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.
   
5. 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

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.