A Cook’s Tour of Excel 2010 VB Macro Development

I have my preferred technologies to develop in, it’s true. But I happily go where the requirements lead, even if it’s into technologies I wouldn’t use by choice.

Lately, I’ve been working on building out the most complex thing that’s ever been asked of me with Visual Basic macros in Excel 2010. It’s currently about 6.5 thousand lines of code, and I’m pretty certain now that there’s not much of the Excel macro space I haven’t had to look at.

Because it’s not a common choice, but it is an uncommonly technically challenging build, I want notes to remind me of what Excel can (and can’t) do for me with macro support.

So, here’s a (very dense) cook’s tour on programming with Excel macros.

  • There are all sorts of limitations around program size, method nesting, max number of variables etc.
  • Worksheet.UsedRange  is a trap.  Don’t trust it from the moment you start removing rows/columns in a worksheet.  Consider other substitute approaches.
  • There’s no native support for ‘skip the rest of this iteration in a loop’. The best you can do is place a label at the end of a loop and get over your carefully developed intuition to avoid the perils of goto statements.
  • Though you can’t skip an iteration of a for-loop, you can exit a block (method, for, do, whatever) entirely. Limit your reliance on goto statements to just loop iterations, Linds.
  • Macros don’t generate undoable/redoable actions by default. There’s limited capacity for it, but it doesn’t allow nested undo/redo actions which, frankly, makes it close to useless for me.
  • Floating-point arithmetic has pitfall traps for the unwary. Special note must be made for situations where we are relying on chart trend line formulas.
  • There’s no “direct” way to get a Range object for the  Series object of a chart. If you’re trying to get back to the range that the chart is drawn from, it’s involved.  Study this class module to see how you can interrogate the series text to build the underlying Range object from it.
  • Understand events and their lifecycle. Event handlers are probably best views as wrappers on base Excel objects, and when the variable holding a reference to a wrapper goes out of scope, bye-bye event handler.  You’ve gotta store those references somewhere global enough that the event handlers stick around for the entire life-time of the macro itself.
  • Event lifecycles can orbit class modules pretty tightly.  It’s useful to be aware of class modules vs the more pedestrian code modules in enough detail to avoid any surprises. Note also, that this is where you’re going to get closest to the ideal of “object oriented”, in an otherwise procedural approach to scripting.
  • Chart events are particularly snarley… take this. Also, a study of this class module of chart events is worthwhile.
  • A workflow based on a pattern of
    • automation,
    • manual intervention,
    • rinse and repeat as needed

    gets interesting if you want to remember state between automation steps.  Consider a hidden worksheet to store state instead of the very tempting, but ultimately, self-defeating low-road of global variables.  Said globals don’t survive a close/open sequence of user activities mid-way through the workflow.

  • There’s no simple support for catching and reacting to a paste event.  You’ve got to build event handlers at a global level to capture paste events via every possible key stroke/mouse event, then react to just those that matter to you.
  • Yes, it’s actually possible to do reflection/introspection type tricks (like say, injecting event-handler code code directly into a work-sheet). Be warned though that you have to explicitly tell Excel to trust macros wishing to access the underlying VBA project object module.  Not exactly safe, so just… carefully consider if you really need to go here.
  • Delightfully, methods support optional parameters.
  • Colour Index! Let’s remember a colour index!
  • Here are the line styles supported for drawing boxes around cell ranges (for no other reason that they were hard to find).

And, what has all this been in aid of?

This slideshow requires JavaScript.

An analysis tool for people wielding mass spectrometry devices, allowing them to process results with a minimum of manual effort. It’s an “hours down to minutes” time-saver for the target-users, which seems thematic for me lately.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s