A short exercise in modular programming

August 5, 2008 – 8:30 pm by JP

---

    As time goes on I am becoming more of a fan of modular programming. Lately I have been breaking out a lot of small code snippets into their own subs or UDFs. It's a great way to practice creating useful subs that can do different operations by passing arguments to them. The best part is that the whole sub can be cut and pasted (or if placed in a separate module, exported and imported) into different projects, to be used immediately.

    You might be wondering what modular programming is, or how to implement it in your own code. Here's a brief intro with an example.

    PC Magazine has a short but excellent definition:

    Breaking down the design of a program into individual components (modules) that can be programmed and tested independently. It is a requirement for effective development and maintenance of large programs and projects.

    Here's another decent article that explains it well: Modular Programming (see top of page)

    Related ideas include encapsulation and structured programming. I encourage everyone to check out these concepts; an especially useful book for this is Professional Excel Development, which has been my bible lately. I've read it cover to cover three times. This is the single most important book you need to own if you are (or plan on) doing any kind of serious application or add-in development with Excel.

    I especially like the image on the PC Magazine site, which clearly shows how a program branches out into separate smaller modules that perform some (presumably) singular function and then return control to the main program.

    The reason why this is so important is that your code is infinitely more portable, usable and readable if it is broken down into steps, which are separated into different procedures. These procedures can accept whatever arguments are passed to them without question (other than checking that the variable type being passed to them is correct, of course) and don't need to know anything about the calling program. Writing generic, reusable routines, is an easy habit to develop and pays off in the long run.

    Of course, you could simply load all of the code for a single project into a single procedure, and it will usually work as intended, but what happens when you need to use the same code multiple times in the same procedure? Many would cut and paste it further down, resulting in lots of duplicate code in the same sub. Further, if you needed to perform the same function in a different procedure, you would have to copy and paste the code and then try to adapt it for your procedure.

    Here's a short example that should make it clear. I want to add autofilter arrows to a header range. I could do it like this:

    But what about if I need to use this somewhere else in the same procedure? Do I create a new Range Object variable and Range.AutoFilter it? This uses up more memory and might slow down further code execution. Or do I reuse the rng variable and risk causing errors if I reference it again later, mistakenly assuming I am referring to the original rng variable?

    Instead, I modularize the AutoFilter function by removing it from the main sub and putting it in its own procedure so it can be used on any range. The slight discomfort of removing the simplicity of our original code is replaced with the newfound flexibility and portability of our updated code. We'll use a very short sub which takes one argument: the range to be filtered.

    The sub FilterHeaderRow() is called from the FilterMe() sub, passing the rng variable as an argument. The second sub checks to make sure AutoFilter isn't already applied, then goes ahead and adds autofilter arrows to whatever range is passed to it. The 'On Error Resume Next' is used in case the calling sub sends a bad range to the called sub. We don't want error messages, we just want to ignore the request if the range is invalid.

    The AutoFilterMode check is done inside the FilterHeaderRow() sub, instead of from the calling sub, to make it self-contained; I can now cut and paste this code anywhere I need it and it will check itself to make sure AutoFilter isn't already applied to the worksheet. Otherwise I'd have to remember to add that code in, which I probably wouldn't (which is the point of writing subs like this in the first place, so I don't have to).

    It's a simplified example but hopefully you get the idea. Here's another example. In this code we have an imaginary spreadsheet whose header row we want to filter and highlight. We could just throw all the code into a single long sub, but we want to write code that is: more portable, usable and readable. Therefore the filtering is done by one sub, and the highlighting by another.

    The ColorCol() function accepts three arguments:

• the range to be highlighted (A Range Object),
• the text color to be applied (Range.Font.ColorIndex), and
• the cell highlighting color (Range.Interior.ColorIndex).

    You may also notice that the ColorCol() sub will work on any range, not just a header row.

    One important thing that needs to be said is that the called subroutine should declare what arguments it accepts, and (in the case of Functions) what variable type it returns. This acts as a built-in check in case we try to pass arguments that would cause errors. We could have written Sub ColorCol(rng, FontColor, InteriorColor), but as soon as someone tries to use an invalid range, or a bad number for FontColor, the sub won't complain until it is used (i.e. too late).

    For the color index numbers check out Dave McRitchie's color page.

Enjoy,
JP

 Print This Post  |   Email This Post  |  Permalink  |   Subscribe now!

Filed Under: Excel, VBA
Tags: encapsulation, modular, structured

This post has 175 views since August 5, 2008 – 8:30 pm.