Do you find yourself creating the same set of steps in another macro with only slightly different names? Or is your macro so long that it’s difficult for even you to read?
That’s what functions and subroutines help solve in VBA. They allow you to break your macros down into smaller, bite-sized chunks that you can eat again later … Wait, that’s gross. Sorry, the analogy broke down, but you can write macros faster and probably better using them.
Thanks for your interest
This content is part of a paid plan.
Introduction to functions and subroutines
Longer macros often beg for natural functions—similar sets of steps that perform nearly the same specific task in the macro.
Ideally, the set of steps best suited to creating a new function would be general purpose and usable in other macros. On the other hand, some long macros could still be broken up into unique functions or subroutines to make the work and presentation more manageable.
Difference between functions and subroutines
Functions and subroutines are both a set of steps that can be run or “called” from within another macro. Often we’ll provide some additional data in the form of arguments such as a paragraph of interest in your current macro or maybe a range of relevant document content.
Subroutines perform the given task and basically just end. Functions are used when you need some information back (we say "returned") after the macro finishes running such as the number of words deleted in a paragraph.
Which one do we use?
It really depends on what your preference is.
In general functions are probably more common because we usually want to know something after the function finishes, even if it’s just an error message. Moreover, if you create function, you still have the option to just ignore the information passed back from the function if you don’t need it in a particular macro.
Given the similarities, I’m going to use the slightly more general term of “functions” rather than constantly saying functions and subroutines. That’s annoying, but I’ll make a distinction when it’s necessary or convenient to do so.
Benefits of subroutines and functions
For the programming impaired, why would you torture yourself creating such abominations? There are several reasons actually:
- We don’t have to reinvent the wheel or copy macro steps every time we need to do the same task. We can a reuse the same function on different document elements inside the same or any other macro.
- If you ever correct or improve the process in the function, you only need to make the change in one place.
- They simplify longer macros. Functions break apart macros into smaller units where you can focus your efforts and solve each smaller problem more easily.
You can grab the function later, trusting it is correct, without getting bogged down with half a dozen additional steps in the middle of a more important macro. We don’t have to think about such small-ish steps like insert this character here, etc. for everything. We’ll still have some of that, of course, but we can think at a higher level overall.
Another nice thing about using a function is we can often generalize it to make it even more useful in other macros. It takes a little more work to set a function up, but it’s often worth the trouble.
In fact, using functions and subroutines was one of the first major improvements in the early era of computer programming back just after something big extict-ified the dinosaurs.
Example
Provided you use good function names, you can read the steps more like macro English (and VBA is already pretty good at its English-like commands). For example, what does this subroutine call do?
It … deletes any spaces at the end of the paragraph you give to it (the fancy name for data you provide is an “argument”).
While this makes the macro steps look a tiny bit more like regular programming, which I’ve often avoided on this blog, it’s much easier to read than having a chunk of VBA code in its place even if you use lots of comments.
If you want more details about the steps, you can always go to the function itself [in the VBA editor, you can right-click (or Control-Click on a Mac) the function name and select Definition to jump to the function].
Deciding on a function
How do we decide when to create a function or subroutine?
Usually, they kind of evolve naturally as you’re creating macros.
You’ll probably have a set of steps in a macro that you’re about to copy and reuse somewhere else. In other cases, you might have a stand-alone chunk of steps that you’d like to extract to tame a very long macro. In either situation, you just have to extract the steps and create the function scaffolding around them.
So it’s often obvious what steps you want to make into a function, but it can get a little tricky if the necessary steps are intertwined within the other logic of the current macro.
Is it worth the effort?
That’s a judgement call, of course, but if you’ll likely reuse the steps in other macros, then it’s probably worth it.
You’re not trying to split the atom. You’re just trying to stop from reinventing the wheel every time to want to use a similar set of macro steps.
Also, it might take a little longer if you’re trying to generalize it as you create the function. Sometimes it’s worth the extra initial effort to make it more general while you’re already taking the time to create it.
How do we use functions and subroutines?
The basic setup begins with a starting line Sub or Function keyword with a unique subroutine or function name. It ends with an End Sub or End Function statement.
Subroutine example
For subroutines we write it just like a regular macro. A super simple example is
We used “Sub” to start the subroutine. Then we gave it a unique name SimpleSubroutine along with parentheses (empty here). We included any subroutine steps which is just a comment line in this fake example. We finally ended the subroutine with “End Sub”.
Function example
Functions use almost the same notation except we swap out the keywords “Function” for “Sub” and add an extra type at the end. Also, we add a returned value sometime before the macro finishes, but it doesn’t have to occur exactly at the end.
Notice the returned value is literally the function name. Just assign whatever result you want to the function name, and this information will be sent back to the calling macro. Of course, whatever value you assign must have the proper data type you gave on the first line. In this example, the SomeValue variable should have a fictitious data type of SomeType.
Function types
Possible function data types include all those we use for variables in any macro such as numbers (Integer, Decimal, Long, Currency, etc.), text (String), other Word objects (Paragraph, Range, etc.), even collections of objects (Paragraphs, Sentences, etc.), and more.
The exact data you return from the function is your choice. Obviously, the returned value should be directly related to the purpose of the macro, but even then, there is some play. For example, in a function to delete spaces from a given range, you could return the number of spaces deleted in the function or a range corresponding to the deleted text location.
Parentheses and parameters
The parentheses () after the name also implicitly indicate it’s a function or subroutine. For example, in the DeleteParagraphEndSpaces example subroutine above, we included the target paragraph MyParagraph inside the parentheses.
Defining parameters
We must specify any data (fancy name is parameters) that can be provided when we set up the subroutine. For example, a sample declaration for our earlier pretend subroutine is
Similar to a Dim statement
Notice the “As Paragraph” parameter mimics how we define a regular variable in a macro using the Dim keyword.
The difference is for a function or subroutine, the parameter is stated inside the parentheses without using the Dim keyword. VBA already knows you’re declaring a variable for the subroutine.
Benefits
Reusing the subroutine with different elements
The main benefit is you can reuse the same steps with another document element without changing anything but the data you give it. For example:
This runs the subroutine on MyParagraph and then AnotherParagraph with only two beautiful, easy steps.
Variable references
The VBA editor will understand any reference to SomeParagraph inside the subroutine is an actual Paragraph with all the rights and privileges, and restrictions, thereof. That is, it has access to all methods (commands) and data (properties) of a Paragraph object in Word.
Restrictions
Rules and restrictions … no one likes them. Well, they actually help out more than we usually want to admit. Even if we don’t like red lights, the roads would be more dangerous without them, so let’s talk about restrictions when using function and subroutine parameters.
Cannot “run” functions or subroutines with parameters
If you provide a parameter, you cannot assign it to a keyboard shortcut or run it from within the View Macros ribbon menu dialog. The function or subroutine can only be used within other macros.
That’s a little disappointing, but functions and subroutines with parameters are still powerful tools to add to your macro toolbox.
Parameter variable has a type
Inside the function, references to SomeParagraph can only do what a Paragraph can do in VBA.
There is a way to declare “Optional” parameters. I use them frequently, but we’ll talk about that later.
Must use a parameter if defined with one
When a function is defined with a parameter, no one can use it without giving it data to act on with the proper type.
Data must have the proper type
We’ve been a little sloppy with variable types throughout this blog because most of the time the VBA editor can infer the type from what data you use. It makes creating the macros a little easier, but when calling functions and subroutines, VBA is pickier.
For example, with the earlier subroutine DeleteParagraphEndSpaces, any argument provided must be a Paragraph because all the steps inside the subroutine assume the SomeParagraph parameter exists as a Paragraph in the document.
After all, even if you know you’re giving the function a valid Paragraph (or whatever Word element you’re using) in your new macro, VBA wants to be sure. For that matter, something might go wrong, and you unintentionally give the function something that’s not a Paragraph.
Cannot use implicit variables as arguments
Whatever data you give the subroutine when you call it must be the actual data type of the parameter.
This sounds like the same point as above, but there is a distinction. We obviously cannot give the wrong variable type, but more than that, we cannot provide the function a generic unknown type (called a Variant). The VBA editor will complain with an error message and not run the macro.
In practice this means you cannot use implicit variable declarations. You must declare your variable before using it as a parameter in a function unless you specifically give your parameter type as Variant when creating the function. To me the latter case partially defeats the point of using a parameter, so I almost never do this.
Bad examples
For example, these subroutine calls don’t work. We first define two variables.
Notice SomeVariable wasn’t given a type, so it defaults to a generic Variant type.
Now these subroutine calls don’t work.
AnotherVariable is assumed to be a Variant type because it was not declared earlier.
Some variable is still a Variant type despite the declaration above because no type was given.
This one doesn’t work because SomeRange is a Range data type not a Paragraph as expected when we defined the subroutine.
Proper usage examples
The following subroutine call works because the variable given (the argument) and the subroutine parameter are both Paragraph types.
But we don’t necessarily need the intermediate variable declaration.
This also works because Selection.Paragraphs.First is a Paragraph from the Selection’s Paragraphs collection.
Using functions and subroutines
This is one of those things that’s harder to explain than to do, but here we go.
Call me
If there are no arguments, you can omit the Call statement and the parentheses, and just use the subroutine like this
If you use an argument (give it some data), you must use the Call statement to run the subroutine.
The latter is the more common usage since we’re usually providing some specific data for the subroutine.
Calling a function
If you call a function like this, it will just ignore the returned value.
Store function values
For functions, we probably want to use the returned information somewhere. One common approach is to just store the value in a variable.
Then we can use the variable StoredInformation wherever we need it.
Use function values where needed
Another approach is to just use the function where it’s needed. An actual example is
Of course, the custom function IsValidStyle must give back a valid value based on where and how it’s used. Here, a regular If statement expects a True-False (Boolean) value, so the IsValidStyle function better yield a True or False result if it’s going to be used like this. Otherwise, it will cause an error.
All roses and sunshine?
Is using functions and subroutines all roses and sunshine? Mostly, and there's not even many thorns to worry about.
There’s a little extra work involved setting one up, and using the functions can result in a slightly less efficient macros.
Why?
We often include some validation tests and such just to make sure there are no problems since it can be used anywhere now, but for Word macros, you’ll almost never notice a practical difference in running time unless your processing a whole novel at a time or some other large document.
Your macros will be better organized, and the time savings using the same functions in other macros is well worth the usually imperceptible degradation in running speed.
