Where does your macro want to go today?

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