One of the things that currently torments my soul has to do with macros in Office. Now "macro" can mean several different things, but for this discussion let's assume that a macro is something an end-user records with the macro recorder for later playback. Let's further assume that the user knows nothing about code and is blissfully unaware that macros actually generate VBA code that is compiled and executed for them in the background. (I'm jealous of this user already; thinking about code is too hard ;-) )

One problem is trying to figure out the user's mental model for where the code should "live." For example, consider these simple scenarios:

  • Betty often needs to print documents in triplicate, so she records a macro to print the current document 3 times
  • Frank receives budget spreadsheets every week and has to perform the same operations on them, so he records a macro to help automate some of the work
  • Sam is working on an ad-hoc document and records a macro to fix up some paragraph formatting, and he e-mails the document to himself so he can work on it at home

In the first case, the macro is clearly designed to be used as an extension to the host application (eg, Word). Betty has essentially added a new feature to Word that will print any document 3 times. Therefore, the code should be stored on her machine and associated with Word itself, rather than being stored or associated with a particular document or class of documents.

In the second case, Frank has a macro that is very specific to his weekly budget spreadsheets. It probably won't work very well when used against arbitrary documents, so he has not really added a feature to the host app in the same way Betty has. It doesn't make sense to expose the macro at a global level because more than likely it will just fail if used against any non-budget spreadsheet. If the host application has a notion of a "Template", then Frank's macro could be associated with the template and therefore made available whenever a budget is open, but if Frank does not own the template (eg, his reports are mailed to him as part of a larger business process) then he could be out of luck.

In the final case, Sam has customised the document itself and expects his customisations to travel with the document as he sends it to his home account. He also would not expect his macros to show up when any other documents are open, as they are very specific to his current task. All security issues aside, this requires the macro code to live with the document and not with the application or the document's template.

We have these three styles of working with macros (and possibly more!) and it would be very nice to support all three styles without having to prompt the user with a question that they likely won't understand ("Where would you like to store this macro?"), but code can't simultaneously be available to all documents and only one document, etc.

Then you start worrying about security. Or re-using macros from one document inside another document. Or two developers collaborating on a macro. Or other more complicated scenarios. And it gets worse!

So next time you curse VBA for getting it wrong, take a moment to think about how you'd make it better, then take a couple of aspirin ;-)