How to Know When to Turn Lines of Code Into a Standalone Routine in VBA

Mike Wolfe -

Don't Repeat Yourself.  It's part of the programmer's mantra.  But it's not an absolute.  

New developers often wonder when a block of code should be extracted into its own routine.  

  • Is it ever OK to copy and paste a block of code from one routine to another?
  • Should you preemptively create a routine any time you think you might use that block of code in the future?
  • If you do convert code into a routine, how do you know which values to hard-code and which ones to make arguments?

I like to use the "thrice-is-a-pattern" heuristic to guide these decisions:

Once is chance; twice is coincidence; three times is a pattern.

In practice, this means I don't create a standalone function or sub for a block of code until I've copied and pasted it twice.

Copying and Pasting Code Can Be Good

There are a few things I like about this approach:

  • It helps avoid overengineering
  • It reduces the number of decisions you have to make (more on this in a minute)
  • You're more likely to create a useful routine when you finally do generalize

A Practical Guide

Once is Chance

The next time you write a block of code that you think might be useful as a standalone routine, don't immediately create the routine.  Instead, make a mental note of the code so that you can reference it in the future.

Twice is Coincidence

At some point, you may be working in another part of your program (or another project entirely) and realize that some block of code you wrote earlier would solve the problem you are facing.  When this happens, simply copy and paste the code you wrote earlier into wherever it is you need it.  Make a mental note of any hard-coded values you need to change after the copy and paste.

Three Times is a Pattern

If you find yourself needing that same code that you copied and pasted earlier, now it's time to generalize it into a standalone routine.  Copy the code from the second location and paste it into a new Function or Sub.  Make a mental note of any hard-coded values you need to change.  Chances are they will be the same ones as the last time you copied and pasted.  

The hard-coded values you had to change each time you copied and pasted your code will be arguments in your generalized Function or Sub.  You don't have to think about what pieces of information might change.  You don't need to guess at what variables would make the most appropriate arguments.  Your real-world usage will dictate those arguments.  (That's what I meant above when I said you would have fewer decisions to make if you follow this approach.)

Use Common Sense

Oftentimes, it will be obvious that a block of code will be called many times and should be extracted into its own function.  And, there are other reasons you may want to extract code into its own function even if it's only being called a single time:

  • To provide separation of concerns
  • To improve readability
  • To simplify testing
  • To reduce the total number of lines in a long procedure
  • Etc.

Where this thrice-is-a-pattern heuristic helps the most is when it's not obvious whether a block of code will be called often enough to justify extracting it into its own routine.  In those situations, I find this approach works very well indeed.

Cover image created with Microsoft Designer