How to Create a VBA Function and Re-use it in Many Subroutines

My first VBA function was like

Let’s get it out there straight away — creating VBA Functions is fucking hard. It’s tough enough to get your macro running in the first place!

(That’s why I hammer the VBA Toolbelt so hard — those functions cover tons of use cases and are well-tested.)

That said, a good Function is incredibly useful:

• It condenses many lines of code and logic into a convenient single line
• It has a name that describes exactly what it does
• It lets you make a code change in a single place instead of multiple places, a concept commonly referred to as D.R.Y. (Don’t Repeat Yourself) in the development world

Heaven help you if the code is scattered in many workbooks

But there is a bulletproof strategy for writing Functions in VBA. Even if you use this technique to create just a few re-usable Functions (or Subroutines), the nature of the method guarantees that you will get a lot of mileage out of the results.

But that’s enough hype — let’s get to it.

Every time you copy / paste code in the VBA editor, ask yourself:

Am I doing the same thing here (paste) that I was doing there (copy)?

If the answer is “yes”, it’s time to write a Function or Subroutine!

Beautiful. Now that you know you’re doing the same thing in at least two places, these two questions will send you down the right path:

1. Does the copied / pasted code return something (like a String, Range, Worksheet, Collection, etc.)? If yes, you’re going to write a Function. If no, you’re going to write a Subroutine.
2. Does the copied / pasted code operate on something (like a Worksheet, Range, Scripting.Dictionary etc.)? If yes, that thing is what you’ll pass in to your Function (or Subroutine) as an Argument.

That thing… Pass in that thing as an argument!

That’s it — seriously! 3 questions. Here they are again:

1. Am I doing the same thing here (paste) as I was there (copy)? <~ If yes, you’re going to extract the copied code into a Function or Subroutine. Continue to #2:
2. Does the copied code return something, like a Long or a Range? <~ If yes, you’re going to write a Function. If no, you’re going to write a Subroutine. Continue to #3:
3. Does the copied code operate on something, like a Worksheet or Collection? <~ If yes, you’re going to pass that thing into the Function or Subroutine as an Argument. Whammy!

Let’s work through a real example, using the AddToDestinationWorksheet Subroutine we wrote in the copy data based on dates remix part 2:

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Lines 38-41 and Lines 78-81 are exactly the same! That means we can move on to question #2.

Does the copied code return something, like a Long or a Range?

Nope — there is no variable assignment here, we’re clearing all filters. That means we’re going to write a Subroutine. On to question #3.

Does the copied code operate on something, like a Worksheet or Collection?

Yep — this code works on the wksData Worksheet. This means that we’re going to pass that Worksheet into the Subroutine as an Argument.

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Let’s work through this short-but-sweet Subroutine line-by-line:

• Line 5: we are not returning anything, so we use Public Sub (instead of Public Function) as described in question #2
• Line 5: we are expecting a Worksheet to be passed-in as an argument, as described in question #3, so we call that passed-in Worksheet variable Sheet
• Line 6: we flip the passed-in Sheet‘s AutoFilterMode to False
• Line 7: check to see if the passed-in Sheet‘s FilterMode is True
• Line 8: if so, we call Sheet.ShowAllData to clear any remaining filters
• Line 9: ends the If statement
• Line 10: ends the Subroutine

Nice! Now that we have the ClearAllFilters Subroutine, you can easily integrate it back into the larger AddToDestinationWorksheet Subroutine by replacing the 4 line code block (lines 38-41 and lines 78-81) with Call ClearAllFilters(wksData), like so:

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Wahoo! Here’s a 7-minute screencast walking through this process — highly recommended, since this topic is so abstract!

Are you creating VBA Functions and Subroutines easily by answering those 3 questions? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.