Saturday, March 27, 2010

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.

Sunday, March 21, 2010

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.

Saturday, March 20, 2010

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.