Article Index

MS Office development

For many organisations the MS Office suite remains an effective solution for tactical applications. Unfortunately, Bob used to work here and created a myriad of macro-driven spreadsheets and databases that are now essential to your business.

Except something doesn't work and no-one knows how to fix it.

Although the macro programming environment in the MS suite is more user-friendly, it is still handing out machine-guns to babies. Simple automation tasks can be safely recorded by end-users without too much trouble, but once you start to edit the VBA code directly you should have a long hard look at yourself and ask 'Am I the right person to be doing this?' - the answer is probably 'No'.

Why develop MS Office macros at all?

The functionality delivered off-the-shelf by both MS Office and OpenOffice already includes many functions the most users will never require, so why would anyone want to add yet more features? The answer is that adding some fairly simple, but necessarily customised functions, can make many daily tasks much more efficient.

For instance:

  • Word processing documents You can automate the 'fill in the blanks' part of creating letters, faxes, memos etc and (a) make the process much quicker and (b) be sure that all your correspondence carries the same organisational branding.
  • Spreadsheets Reporting and charting applications, perhaps extracting data from a central database for more detailed manipulation.
  • Database applications Could be anything from a custom order entry procedure to an analysis and reporting application supporting an existing third-part application.

There are just some of the processed that can benefit from macro automation. In general, any process where someone spends a large proportion of their time producing many similar results might benefit from automation.

But there's this really good wizard thingy! Can't I use that and do it myself?

Sure, you can give it a shot, and if what you want is close to what the wizards think you want then you might even get a usable result. Sadly, once the wizards prove insufficient you need to know what you're doing.

An analogy might be illuminating: I know enough about car engines to check fluid levels and cables and can nod at the right times when speaking to mechanics, but I'm not about to try replacing a cylinder head myself.