VBA Functions

A while ago I read an interesting post here:


A guy questioning the point of breaking code up into functions. He seemingly uses as few as possible. I’m the exact opposite – I almost use as many as possible, and I make them as short as possible (1 screen ~ 40 lines). No more than 3 or 4 parameters.

His argument is that the extra code of the build up, pass control and tear down of all those functions makes things harder not easier. Its an interesting approach. Personally I don’t think it would scale to over a couple of hundred lines of code max – what about you?

I’m a bit of a fan of Millers theorum (7, + or -2), but a few people have pointed out there is no evidence that it applies to programming. Suits me though.

Whats your view on it? do you even care about function length?



[My session at the Excel user conf will be looking at this and other VBA design issues in more detail]

4 Responses to “VBA Functions”

  1. Jon Peltier Says:

    I don’t care specifically about function length. Splitting code arbitrarily into ever smaller pieces until they all fit in one screen can be counterproductive. However, using as few functions as possible is also counterproductive, as it reduces your opportunities to modularize and reuse your code.

  2. Harlan Grove Says:

    Some modularity makes sense, but I’ve seen examples of programs that look like

    (Pascal syntax)
    program …

    (C syntax)
    main(…) {
    while ( ! feof(STDIN) ) {

    That’s foolish. It’s one thing to encapsulate specific functionality in a function or procedure, quite another to make the main program skeleton reusable.

    I can see the JOS guy’s point: functions and procedures are only needed when specific bits of code would be run multiple times within the same program or from multiple programs. It’s pointless to compartmentalize anything that’s truly once-off.

  3. Marcus Says:

    I do care about function length but I’m not obsessed by it.

    Although is not always possible (or even pragmatic) is to make functions as generic as possible. This allows them to be tested independently and encourages reuse adding to my code library.

    Often (but not always), simply by defining a simple, single task that a function needs to achieve is enough to help from letting the size get out of control.

    Cheers – Marcus

  4. Nick Hebb Says:

    Where possible, I try to boil it down to a single responsibility per function/sub. Sometimes that responsibility is to manage flow between other functions/subs.

    I’m not obsessive about it, but it makes the logic easier to follow when you look at your code a few years, months, days, or (fatigued) minutes later.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: