Functions and subroutines allow us to group common, often detailed, steps for use in any macro. Functions take it a step further by returning a result based on what happened. They allow us to break our macros down into smaller subtasks, so we can create better macros faster.
Thanks for your interest
This content is part of a paid plan.
Introduction to functions and subroutines
Do you ever find yourself creating or perhaps copying the same set of steps into another macro only with different variable names? Or is a macro so long that it’s difficult for even you to read?
That’s what functions and subroutines help us solve in VBA. They allow us to break our macros down into smaller, bite-sized chunks that we use again later … wait, that’s gross. Sorry, the analogy broke down, but no one eats a hamburger in one bite.
Ideally, the task is general purpose and useful in other macros, but some long macros can still be divided into unique procedures just to make the presentation more manageable.
Difference between functions and subroutines
Functions and subroutines make VBA feel more like regular programming, but once finished, they make other macros easier to create and understand. Both are sets of steps that can be run or “called” from another macro. Often we’ll provide some additional data for it to use such as a paragraph to trim or maybe a range of document content to modify.
Subroutines perform the given task and just end. Functions also perform a task, but they further "return" some information about what happened. Examples include the number of words deleted in a paragraph or a modified range after inserting some text. The result can be stored in a variable or used directly in other statements making them work much like a variable that just happened to do or calculate something before it returned its result.
Which one do we use?
Do we write a function or a subroutine?
It depends on the needs of the subtask. Functions are more common because we often want some information about what happed in the function, even if it’s something as simple as how many characters were removed. If we create function, we can always just ignore the information returned if we don’t need it in a particular macro.
Call them procedures
Given the similarities, we'll use the more general term “procedure” going forward rather than constantly saying functions and subroutines. The practical differences are in how we declare them and how we use the returned value, but we'll make a distinction when necessary. Using procedures in VBA is a little different than some other programming languages, but VBA tries to make it as simple as possible.
Why use procedures?
Why go through the trouble of creating a procedure? For the programming impaired, why would we torture ourselves by creating such creatures?
In fact, functions and subroutines were one of the first major improvements in the early era of computer programming back just after something big extinct-ified the dinosaurs (so about 80 years ago, but that is definitely ancient in computer years).
- We don’t have to reinvent the wheel or copy steps every time we need to do the same task. We can a reuse a procedure in many macros or even on different document elements in the same macro.
- Functions allow us to focus on the main macro task. Dividing a problem into logical subtasks makes the bigger problem easier to solve.
- Even a simple subtask is often more complicated than it appears at first glance, so it's nice to have any unsightly complexity tucked out of sight in a function. We can take extra steps in the function to ensure all the details are correct without bloating the main macro.
- If we ever want to correct or improve the procedure, we only need to make the change in one place.
- Once the function works, we don't need to remember the details every time we use it.
- With a good function name, the macro using it often reads almost like English.
We can grab the function later, trusting it's correct, without getting bogged down with half a dozen extra steps in the middle of a bigger task. Check for that space, move past that punctuation mark, … blah, blah, blah. We don’t have to think about such small-ish steps, but if we need any details, we can always review the procedure.
Quick examples of using a subroutine
Using a subroutine is super simple (say that three times real fast). For example, what does this subroutine do?
It … deletes the current paragraph. Yep, that was real hard to understand.
If the subroutine needs some data, it's still easy. We just add the relevant data (called an "argument") after the subroutine. What does this one do?
This one deletes the given paragraph if it is empty. Easy peasy. We often provide the argument as a variable, which is MyParagraph in this example, but a value would work also if the subroutine needs it.
Quick examples of using a function
The point of a function is to use the result, but it's still mostly intuitive. Just treat it like a calculated value.
Yep, it looks like it runs a function to get the word count in a range defined in a bigger macro. Then it stores that result in a variable for later use. The assignment works just like storing a fixed number in a variable. If we're storing an object like a Range, it needs an extra Set keyword, but the idea is the same. The parentheses are required around the argument when a function is used in this way.
How do we create procedures?
Here's where it starts getting a little programmy, but we gotta graduate from kindergarten someday.
Create an empty subroutine
We create a simple subroutine like a regular macro (actually, macros are subroutines). Open the VBA editor using Alt+F11 in Word for Windows (or Option+F11 on a Mac). An example subroutine looks something like the following:
The elements of a typical subroutine are:
- The keyword Sub starts the subroutine.
- It needs a unique name such as SimpleSubroutine here.
- Any parameters are placed inside parentheses (empty here).
- Any subroutine steps go in the middle.
- Any comments begin with a single quote.
- End the subroutine with End Sub.
The VBA editor will recognize the keyword Sub and add the End Sub line and the parentheses automatically, but basically we just type it as if we're working in a text editor … because it is a text editor. Parameters are not required, but the empty parentheses are required even if no parameters are included.
Create an empty function
Functions use a similar notation to subroutines except we swap out the keyword “Sub” for “Function.” We also add an extra type at the end of the header line much like declaring a variable. We must assign a result with that data type to the function name sometime before the macro finishes.
The structure of a function is:
- The keyword “Function” starts the function.
- It needs a unique name like SimpleFunction.
- Any parameters are placed inside parentheses (empty here).
- Add a data type such as "As Long" or "As Range" after the parentheses to indicate what type of data the function will return when it is used.
- Any function steps go in between.
- Comments begin with a single quote.
- The returned result is literally assigned to the function name sometime before the function ends.
- Finish with End Function.
Parameters are not required, but they're commonly used in functions because we're often taking an action on a specific document element. They provide a way to "pass" that information to the function.
What is a returned result?
A function generally has a result in mind related to the action it takes. Examples include:
- Where is the next empty paragraph located in the document?
- Is a range all whitespace?
- How many story words are in the novel?
- And many other possibilities …
The function result answers that question, and this information is sent back to the calling macro. Anytime before the function ends, we need to assign the result to the function name like a typical variable or object assignment. Whatever value is assigned must have the same data type we gave it on the header line. In the above example, the SomeValue variable should have the fictitious data type of SomeDataType.
Function data types
VBA functions will support any valid standard data type as a return value.
- Common numerical types include Long (counting numbers and their negatives), Double (decimal numbers which are called "floating point" numbers in the computer world), Currency, and a few others.
- Plain text is stored as a String.
- True or False (Boolean) values allow yes or no answers.
- Some document elements have their own data type such as Paragraph, Document, Bookmark, Shape, or Section.
- Other document elements like characters, words, or sentences are tracked and manipulated as a more generic Range.
- If we need a generic type, we can use Variant, Collection, or even Object (not recommended).
Common data types for editing macros include a Range (probably the most common in editing macros), Paragraph, Boolean, String, or Long.
The data type returned by the function is assigned based on the purpose of the macro. For example, if a function deletes spaces from a given range, it could return the number of spaces deleted, or it could return the modified document range without the spaces. Both would make sense depending on the intent of the function.
A few issues exist that would affect the returned result. For example, a String does not store any text formatting, and VBA will not allow our own custom objects as return types without a workaround. The latter is uncommon in editing macros, but we could still use a generic Variant type if needed.
Comments
As with any macro, VBA will ignore any text on a line after a single quote (unless "it's" inside double quotes). Comments are not required, but you'll probably regret it later if you don't use them. We usually add comments to describe what the function does, what the parameters are, any important or unusual steps, etc. While VBA often reads quite Englishy, comments add extra explanation necessary for a human to better understand the macro.
Using procedures
This is one of those things that’s easier to do than to explain.
Run a subroutine
If a subroutine has no parameters, we can run it just using the subroutine name.
Usually the subroutine needs some data to work on it. Several options for providing an argument exist. Using Call is a more formal and older variation.
A more modern variation just includes the parameter. It's clean and clear.
If we want extra clarity, we can assign the argument to the parameter name.
The colon equals := symbol is required for the assignment. This mimics how we typically use VBA object methods, and it also allows us to change the argument order.
Reuse the subroutine
We can reuse the same subroutine with other document elements without changing anything except the data we give it.
This runs the subroutine on MyParagraph and then on AnotherParagraph with two easy steps.
Run a function like a subroutine
We can also call a function like the same way, and it will just ignore the returned value.
Much like a subroutine, the SimpleParagraphFunction does something with the MyParagraph variable we passed to it. The function returns a result, but in these examples, we just ignore it.
Store function result for values
For functions, we often want to use the returned information later in the macro, so we store the value in a variable.
We can also include the parameter name inside the parentheses as well.
Either way, we can use the result StoredInformation at any step after this line.
Store function result for objects
If the returned result is an object such as a Range or a Paragraph, we need to use the Set keyword just like any other object assignment.
Use a function result where needed
Another approach is to just use the function where it’s needed. This is easier to understand with several semi-real examples. The extra step of returning a result seems simple, but it provides a lot more flexibility in how we can use them compared to subroutines.
Use a function in a conditional statement
A previous article checks whether a style is valid. The function is used before we assign a style to a paragraph to avoid any errors. Give the function a plain text style name in double quotes.
It returns a True or False (Boolean) value based on the answer. As a Boolean value, we can use it directly in a conditional statement (see our brief introduction to conditional statements). Without a lot of explanation, a sample conditional statement is:
Of course, the function must return a valid value based on where and how it’s used. In this example, an If statement expects a Boolean value, so the IsValidStyle function should return a True or False result or a value that can be interpreted as such. If VBA cannot interpret the result as a Boolean value, it will cause an error.
A second example using a different function, GetNextCharacterAfterRange, in a conditional statement is:
GetNextCharacterAfterRange returns the next text character after a working range simply named r. We immediately compare that character to a paragraph mark vbCr. Using the function directly saves us from needing to declare a separate variable for the comparison, and the descriptive function name still keeps the statement easy to read.
Use a function with a subroutine
Another example extends one subroutine InsertNovelNote with a function GetDateTimeText.
This example runs the InsertNovelNote subroutine using the GetDateTimeText function. The time text function gets the current time as plain text every time the novel note subroutine is used. The parentheses are omitted with the GetDateTimeText function since no arguments were required. Of course, the GetDateTimeText function should return a string if it's used in this context.
Refer to methods or properties of a function result
Suppose we have a function GetNextEmptyPararagraphRange (similar to our GotoNextEmptyParagraph macro). It returns a range corresponding to the next empty paragraph in the document after a starting range provided to the function. If we pass the function an initial range called MyRange (assuming it's already assigned in the main macro), we can use the Delete method to directly delete the identified empty paragraph range.
Since the returned object is a VBA Range, we can immediately refer to any methods that a Range has using a dot (just a period). The parentheses are required when we access any methods or properties of a returned object. To keep the example simple, we're neglecting any issues with the range not existing if no empty paragraph was found.
Use a function result in another function
We can also use a function result as the argument for another function.
In this example, the function GetNextCharacterAfterRange returns the next character after a working range named r, but we are unsure whether it is upper or lowercase. Perhaps some decision logic follows the next character assignment, and we don't want to worry about handling either case, so we immediately convert it to lowercase every time.
LCase is a standard VBA function that converts any uppercase alphabetic text to lowercase without changing any other characters in the given string. This would be a quick way to ensure we're always working with the lowercase version of the next character.
This can result in some concise steps, but it can also be tedious to interpret at a later time. The latter issue can be helped by using descriptive function names.
Including procedure parameters
Often we want to do something in a procedure with specific document content such as the current paragraph or maybe a sentence range. We need a way to provide that information to the procedure.
What is a parameter?
Parameters are basically special variables that allow us to provide the procedure with some outside data. They also specify what types of data are accepted by a procedure. Parameter types are important because a procedure will take various actions under the assumption it knows the type of document content it is manipulating.
We include each parameter name and its expected data types inside the parentheses () after the procedure name. When we use a procedure, the data we provide in place of a parameter is called an “argument.”
How do parameters work? (Quick summary)
We provide the procedure some data through a parameter, and the function uses it to do something. It works with any standard VBA data type where the basic rules are:
- Argument and parameter data types should match exactly, but some exceptions and workarounds exist.
- An argument is required for each parameter included in the procedure definition unless a parameter is declared as Optional.
- If a subroutine has any parameters, it cannot be assigned to a shortcut. A function cannot be assigned to a shortcut even if it has no parameters.
- Multiple parameters can be used as needed. Separate them with commas and assign each one a type.
- Argument order should match the parameter order unless we include the parameter names.
- By default, any changes made to a parameter inside the procedure will affect the argument outside the procedure.
The explanations stretch out a ways, so jump to the next topic if you prefer to skip the details for now.
How do we declare parameters?
Parameters are not required, but if used, we define them when we set up the procedure. A declaration for our earlier example subroutine is:
We include a parameter name and give it a data type. In this subroutine, the parameter name is SomeParagraph, and it has a data type of Paragraph. Using “As Paragraph” to specify the data type mimics how we define a regular variable using the Dim keyword with a typical procedure variable.
For a procedure, the parameter and its type are stated inside the parentheses but without the Dim keyword. VBA already knows we’re declaring a parameter for use inside the procedure. Parameter and variable names need to be unique within the procedure, but a parameter name can be the same as another variable outside the procedure without causing any conflicts (for the most part).
VBA will understand any reference to SomeParagraph inside the subroutine is a Paragraph type with all the rights, privileges, and restrictions, thereof. More specifically, SomeParagraph has access to all methods (actions) and properties (data) of a Paragraph object in Word VBA which we can use to manipulate whichever document paragraph is passed to the procedure.
Adding more parameters
We can include as many parameters as we need (technically, up to 60 but yuck, don't ever). We just separate them with commas and give each one a specific data type.
If the header line (fancy name is a "procedural header") gets too long, we can use an underscore _ character to extend the line. VBA will interpret it all as one line while we humans get the benefit of multiple lines for improved readability.
Default reference parameters
Changes to the parameter inside the procedure will affect the argument outside the procedure.
Yep, you read that right.
In VBA, by default all arguments are passed to the procedure "by reference." Technically, a procedure receives the location of the data in memory, not the actual data. Since the procedure is referencing memory for a variable defined outside its own scope, any changes it makes to that data remain when the procedure ends.
Of course, the procedure should change some parameters. The earlier DeleteParagraphEndSpaces subroutine would be useless if it didn't actually delete any extra spaces at the end of the given paragraph, but that is the core purpose of the subroutine, and the subroutine name makes that change clear.
For other parameters that are necessary but not part of the core purpose of the macro, we usually want to avoid unintentionally modifying the given arguments.
Avoid leaky procedures
Sometimes we need to manipulate a parameter to determine various details before carrying out the intended task. How do we avoid any icky side effects where changes to the parameters affect our outside arguments?
- For objects like a Range or a Paragraph, create a duplicate of it inside the function if any changes are made to it other than fulfilling the procedure's intended subtask.
- For intrinsic data types like numbers, strings, and Boolean (True or False) values, we can pass the data "by value" using the ByVal keyword when declaring the parameter. An extra benefit is VBA will do some automatic conversions between certain types for us.
Parameter name does not need to be unique (mostly)
A parameter name can be the same as another variable outside the procedure without causing a conflict since they have different "scopes." VBA sees them as separate variables which are stored at different locations in memory. More specifically, this means:
- Two different procedures can have a parameter with the same name.
- Our main macro could have a variable with the same name as a parameter in a procedure it uses.
In either case, the procedure will use its local version, and the main macro will use its own version. No error will occur.
A small issue exists if a parameter name conflicts with the name of a variable defined for the whole module or project (latter is called a "global variable"). What can we do if we want to avoid any confusion?
Avoid using the same name as a module-level or project-level variable since the procedure will prefer its local version. This will not cause a VBA error since the procedure will happily use its own version, but it's better to avoid any logical errors that could arise from the confusion.
The above refers to procedure parameters based on the current context, but the features also apply to any procedure variable.
Restrictions on using parameters
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 traffic lights, the roads would be more dangerous without them, so let’s talk about restrictions when using parameters in a procedure.
Shortcut restrictions
A function cannot be assigned to shortcuts or run from within the View → Macros menu dialog (accessible from the Word Ribbon). Similarly, if a subroutine includes a parameter, it cannot be assigned to a shortcut or run manually via the View menu. They can only be used within other macros. That’s disappointing, but they're are still powerful tools in our macro toolbox.
If we want to assign a shortcut, it must be a subroutine without any parameters. We've just been calling them macros most of the time.
Procedure and argument data types must match (kind of)
VBA often plays loose with its variable data types since it uses a form of "dynamic typing." If we don't specify a type, a new variable will default to a generic type called a Variant which can store any type of data. With some caveats below, VBA expects matching data types between a procedure parameter and the argument we give it.
Examples of correct argument types
The earlier DeleteParagraphEndSpaces subroutine included a parameter for the target paragraph. The parameter name was SomeParagraph which we declared as a Paragraph type. The following subroutine call works because the argument given and the subroutine parameter are both Paragraph types.
In this example, we don’t necessarily need the intermediate variable declaration with MyParagraph.
This also works because Selection.Paragraphs.First refers to a Paragraph from the Paragraphs collection of the Selection. We can omit the parameter name if we respect the parameter order of the procedure. Either version works the same.
Any actions the procedure takes using SomeParagraph are necessarily constrained to what a Paragraph can do in VBA, so it needs a valid Paragraph variable.
Examples of incorrect argument types with values
The following examples with fixed values do not work because they are the wrong data type.
Neither the value 5 nor "ABC" are valid Paragraph data types. Respectively, they are a Long (or Integer) and a String type, so these attempts to use the subroutine cause errors.
Example of an incorrect argument with an object type
Suppose we instead declare a Range variable, MyRange, which we assign to be the current Range of the Selection. This still doesn’t work for the DeleteParagraphEndSpaces subroutine because MyRange is a Range data type, but the subroutine expects a Paragraph type.
Examples of incorrect arguments with generic types
Whatever data we give the subroutine when we call it must be have 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 as explained above, but a Variant can be anything, right?
Right?
Uhhhh … yeah, but procedures are pickier.
VBA does not allow a Variant or Object variable to be passed to a parameter which was declared with a specific type. It can't validate the methods or properties used in the procedure since a user could pass it any kind of data in the generic variable.
Examples of incorrect generic argument types
The following subroutine calls don’t work. MyVariable is assumed to be a Variant type because it was not declared earlier.
Suppose we define a variable without a data type.
In the first line, AnotherVariable wasn’t given a data type, so it also defaults to a generic Variant type. Thus, the following subroutine call doesn’t work.
Are there any parameter type exceptions?
Some types are similar enough that VBA can automatically convert between them. This happens some with intrinsic data types like Long, Double, or String; but VBA will still complain about any mismatched reference parameters even for intrinsic data types. For example, a Long is just a bigger integer, but we cannot even pass an Integer variable for a reference parameter declared as a Long.
We can work around the type requirements in certain cases, but using explicit types for the extra clarity is not a bad thing either.
Use value parameters
Declaring a value parameter is one partial work around for the type requirements, but it only works for intrinsic data types like Long, Boolean, or String.
Manual type conversions
For some data types, we can force a type conversion (called "type casting") using a few standard functions CLng, CBool, CDbl, CStr, and some others. These, convert a compatible argument to a Long, Boolean, Double, or a String type, respectively.
Use a generic parameter type (only when necessary)
We can also circumvent the type restriction by declaring the parameter as a Variant or perhaps an Object type. Any data type can be converted to Variant, so we could even pass other data types in its place, not just a Variant variable. Unless this is important for the macro task, the vagueness of a generic type is going to cause confusion at some point.
Objects are always reference parameters
All object types such as a Range or a Paragraph are always passed by reference in VBA even if they are declared as a value parameter. A technical difference exists, but for our purposes, VBA effectively ignores the ByVal keyword for any object parameters. Meaning, for most practical use cases, the argument type for any objects must match the parameter type exactly unless we use a generic type (not recommended).
Must use a parameter if defined with one (kind of)
When a function is defined with one or more parameters, we must provide it with corresponding arguments (also with the proper types as explained above). Referring back to the DeleteParagraphEndSpaces subroutine example, we can't delete the end spaces of nothing, so it must have a paragraph to act on it.
However, we can declare a parameter as Optional provided we assign a default value.
Argument order matters (kind of)
If we use a subroutine that has multiple parameters, they must be given in the order listed if parameter names are omitted (which is common).
Assuming we've assigned the MyRange and MyNumber variables properly, a typical subroutine call would be:
We cannot switch the order because VBA expects the arguments to be in the same order as the parameters in the defined subroutine.
VBA would try to assign MyNumber to the SomeRange parameter and MyRange to the SomeNumber parameter. Neither of those assignments would work.
Using named parameters allows any argument order
We can include the parameter names when we pass arguments to a procedure. The above subroutine call also looks like:
This is equivalent to the above usage without the parameter names. It requires more typing and makes the line longer, but it is also clearer.
Potato, potato, I suppose.
Hmmm. That doesn't work in text does it?
I suppose you can imagine me typing the different pronunciations … but both commands are clear. The second version is just extra clear. You pick.
We can change the argument order compared to the parameter order as defined in the procedure if we include the parameter name along with the arguments.
Using named parameters with functions
When using a function like a function (see earlier example function calls), the arguments must be in parentheses whether they are named or not.
If we're using the function like a subroutine and discarding the result, they are used just like a subroutine.
Using Optional procedure parameters
Sometime a reasonable value for a parameter is obvious, so why force the user to include it as an argument every time the procedure is used?
Example of obvious parameter values
A previous macro moves the cursor to the next empty paragraph (nicer than it sounds), but I actually created the base action as a function something like:
The extra descriptive variables made the header line too long, so we used an underscore to continue the line. A human sees two editor lines, but VBA treats it as one long line.
In a main macro, we might use such a function in the following way. Assume the main macro already declared a range variable named MyEmptyRange.
This function call looks for the next empty paragraph after the Selection.Range position. This range is the current editing (i.e., the blinking I-bar waiting for us to type or delete some text). The value of True is passed to the SearchForward parameter, and it tells the function to … search forward in the document. The identified empty paragraph range is returned by the function and stored in the MyEmptyRange variable. The keyword Set is required because a Range is a VBA object not just a plain value like a number.
Presumably, the main macro would then select the range variable or do something else with it. We're neglecting any other practical considerations of the task for this explanation.
Do I have to tell it forward every time?
Searching forward is probably the most common use case, so why can't the macro just assume a forward search direction unless we tell it backward?
That's what Optional parameters do. We add the keyword Optional before the parameter name.
Optional parameters tend to make the header line quite long, so we often need the above underscore _ character to continue the line.
Now, we can just refer to the current position Selection.Range, and it automatically searches forward in the document. If we want to search backward, we add False as the second argument.
Later, we take it a step farther by assuming the current document position is the default starting range.
How do we include an Optional intrinsic type parameter?
Include the Optional keyword in front of the variable name.
It still needs a valid data type, so we include that information as before which is As Boolean for the SearchForward parameter.
We're allowing the writer to opt out of giving it, but the function still needs a value for the parameter if they omit it. We follow the type with an equals = sign and a fixed default value.
The notation is awkward, but it provides a nice ability to simplify function calls while still allowing a search in the other direction if we need it.
Example default optional parameter values
If the function is used without the optional parameter, the default value must be fixed. Examples include:
- Numbers like -1, 0, 5, or 3.14159265
- An empty String "", one with fixed text like "abc", or maybe a paragraph mark character vbCr
- True or False (Boolean)
- Nothing (an unassigned object value)
A default value cannot be assigned with a function call which includes all VBA object methods and properties. Optional object types are explained below.
Can we have more than one optional parameter?
Multiple optional parameters are allowed, but they must be the rightmost parameters of the procedure. No examples are given for brevity, but follow the above explanation for each optional parameter.
How do we use them?
If multiple optional parameter optional parameters exist for a procedure, they must be omitted from right to left, but VBA allows any order if we include the parameter names when passing the arguments.
How do we include an Optional object parameter?
This subsection is a little more technical, so skip ahead to the next topic if you don't want the details.
What if I want to have an optional object parameter? What is the correct default value?
For example, many Range related functions might want to assume the starting range is the current position in the document, specifically Selection.Range. With that in mind, we'll use a Range parameter to be concrete about the example.
[Taps fingers on the table]
What's the problem?
Buuuut … this one's a little trickier because document ranges (or paragraphs, etc.) change as we work. Optional default values require a fixed value. Last time I checked, something that changes isn't fixed. The current position is given by Selection.Range, but the following default value for the Optional StartingRange parameter doesn't work.
Hmmm.
Selection.Range doesn't work because Range references a property of the Selection object. It is evaluated every time it is used, and it obviously changes as we work in a document. We also can't use a variable or any method or function call because they're not fixed values either.
Oh, poodles.
Use Nothing for an optional object default value
We can extend the previous function definition by making the Range parameter optional also.
The obvious default value for an optional object is Nothing since it implies the object variable has not yet been assigned to anything valid. Nothing is also a fixed value.
Cha-ching!
But we're not out of the corn field yet.
Correct for the default Nothing value
Nothing isn't a default value in the sense of 5, True, or "abc". The latter values have meaning outside of the function, but Nothing literally means the StartingRange parameter is not yet assigned to any valid document range. We can't run the function with an invalid range. We need a conditional statement to detect the invalid default value and assign something that makes sense for the function.
Without digging too deep toward the center of the earth, our conditional statement should check whether the optional StartingRange parameter is Nothing. If so, it should assign the current document position using Selection.Range like we used earlier.
In other macros, we would usually just exit the function because Nothing is not a valid document range (see below), but this variation does something meaningful when the StartingRange is not yet assigned. Directly assigning Selection.Range to the parameter unleashes a chihuahua gotcha (see by reference comments below), but we'll neglect this unusual case for brevity (ha).
Use the default optional object parameter
The simplest way to use the modified function is with both optional parameters omitted:
This is nice and clean for the default case. The above If statement will detect the default value of Nothing and set the StartingRange parameter to the initial document position (as a Range).
We dropped the parentheses for the GetEmptyParagraphRange function only because it is currently used with no arguments. We could include empty parentheses () at the end, but it makes no difference in how the function runs. Parentheses are required if we add any arguments.
Other arguments still work (general case is unaffected)
We can still use the function for a more general search by providing a different starting range. Suppose MyRange is a valid Range variable which is assigned somewhere earlier in the main macro. We can pass it to the StartingRange parameter.
MyRange is passed to the StartingRange parameter. A valid StartingRange does not trigger the If statement condition since MyRange is not Nothing, and the function uses the MyRange reference for the search.
Use the default optional object but provide the second parameter
If we want to use the default StartingRange assignment but still specify a backward search, we must use a named argument for the SearchForward parameter.
Since we omitted the StartingRange parameter, it defaults to the starting document position as above.
Invalid use of the default function second parameter
We could not omit the named parameter for the second argument if the first is omitted.
In this invalid example, VBA tries to assign False to the StartingRange parameter because it's occupying the first argument position. False is just a Boolean value not a valid document range, so this would cause an error.
What have we accomplished?
If you survived the mixed metaphors, the function is easier to use with the optional parameters for several common use cases, but we've also preserved the general cases in a simple way (no difference, actually). But, yeah, Algebra is also simple once you understand it.
What steps should be in a procedure?
How do we decide when to create a procedure? Usually, they often present themselves naturally as we’re creating macros.
- We may see a common set of steps that do a specific task as we work in a bigger macro. Perhaps, we're about to copy and reuse them somewhere else with minimal changes, but when our hand eases toward the mouse, a friend slaps our hand and gives us a stern look.
- We might realize the steps performing a specific subtask are actually more general than the current macro requires. A zing of excitement surges, and our friend cheers us on as we proactively create a new, custom VBA tool to make our lives easier.
- In other cases, we might have a series of steps that complete a specific task in an extra long macro, and we’d like to extract them just to tame the huge macro. Our friend gives a subtle, encouraging nod at our macro prowess.
The basic steps for the function are often obvious (see our move sentence member macros, for example). We "just" extract them and create the function scaffolding around them, but they usually need some extra steps to avoid potential problems. Extracting the necessary steps can be a little tricky at times if they are intertwined with other logic in the bigger macro.
If we’ll likely reuse the steps in other macros, then it’s probably worth it. We’re not trying to split the atom. We’re just trying to stop from reinventing the wheel every time we want to do a particular subtask.
Do we create a function or a subroutine?
Any macro assigned to a shortcut must be a subroutine with no parameters, so that's easy. For other tasks, if we just want the subroutine to do it without telling us anything specific about what happened, then a subroutine makes more sense.
If we want a specific result from the procedure, then a function makes more sense. On the other hand, a function can also perform a task, and it gives us more freedom in how we use it. We can always ignore the result, so we lose nothing but a little bit of time to create it as a function.
Since functions are a little more useful than subroutines (aside from editing macros), the rest of the explanation assumes it.
Steps toward creating a function
What extra work is required to create a function? After the common steps are identified, what do we do?
- Of course, we need the extra function scaffolding defining it as a function.
- We need to think about the specific result we want to return. Sometimes it's obvious, but occasionally, we have a choice of reasonable options.
- We often need extra steps to avoid acting on any invalid arguments or handling optional object parameters.
- Inside a main macro, we often have specific circumstances we considering, and we're working toward solving the bigger macro task. The implied constraints can make the subtask steps easier to implement inside a bigger macro. Generalizing the subtask steps into a function often requires considering other possibilities or issues (could it encounter an extra space near a range, for example). We need to consider and handle these cases inside the macro.
I never said creating functions were like a quiet stroll through the park, but they're worth the effort.
Passing arguments by value or reference
Editing macros tend to be more focused than other applications, more like applets or editing widgets, but understanding some additional details about how parameters work will help avoid some confusion.
All arguments in VBA are passed "by reference" unless "by value" is explicitly specified for the parameter.
Why do we care?
Any changes to arguments passed by reference will affect the variable outside the function which can result in unintended changes to arguments passed to procedures.
What? Why did that change?
Yep, just plain ole confusion when something isn't what we thought it was sometime later in the main macro. It's not an error that VBA would complain about because it's not technically wrong. It's just logically wrong, and you'd be surprised how many hours can be wasted tracking down such errors.
What does by reference mean?
If an argument is passed by reference, VBA only passes the location of that information in memory (called a "pointer"). The procedure refers to the location to look up any details or make any changes to the value. For larger objects, this saves VBA from copying piles of object data into a procedure every time it is used which is especially important if the procedure is used a lot.
However, passing by reference is almost counterintuitive for intrinsic data types like numbers, True or False, or a text string; but technically, even if a number 5 passed to a function, it still has a temporary location in memory.
Bleh.
While it has a programmy justification (limiting data copying), it's still strange as a default behavior for all data types.
Use the ByRef keyword
The keyword for passing by reference is ByRef, but using it is redundant because VBA assumes it for all parameters. We just use the parameter name inside the procedure and don't worry about it. We don't even really know it's happening unless we encounter a problem.
Avoid relying on reference parameters for procedure results
I advise against relying on this feature to pass information back to the main macro. It's sometimes tempting when things get complicated, but it will likely lead to confusion at some point due to unexpected changes. But if you do, clearly explain what's happening with a few comments at the top of the macro.
What does by value mean?
When an argument is passed by value, a copy of the value is made and given to the function. The function does not know where the original variable is stored in memory, so any changes to the parameter inside the function only affect the local version inside the function.
Use the ByVal keyword
How do we pass an argument by value? Add the ByVal keyword in front of the parameter name.
A small downside is passing by value is sometimes less efficient, but editing macros usually run so fast, we would never notice the difference.
ByVal does not work for a object parameters (mostly)
Objects cannot be passed by value in VBA. Well, they can be, but any changes to the parameter inside the procedure will still affect the argument outside the function. That is, we can use the ByVal keyword with an object parameter, but it doesn't do what the keyword implies on an object parameter.
Uhhh ... then it's not passed by value, right?
Well, it is, but the memory address is copied and passed to the procedure not the object data. The procedure still has access to the outside information using the copy of the data address.
Hmmm.
There's some super smart people in Redmond, but this one is a head scratcher. If I don't mind copying the whole object every time, that should be my choice. Most Word procedures designed for typical editing tasks would never notice the difference anyhow.
Yep, kind of odd that objects are where we would need it the most in Word VBA, but it doesn't work for them. Moreover, intrinsic data types should default to being passed by value, but by reference is still the default.
Oh, well.
Pick your battles, I suppose.
In my own editing macros, this issue pops up mostly with Range parameters, so I create local duplicates of the argument if I need to manipulate the parameter inside the procedure. Changes to numeric variables are less common in editing macros, so I don't use ByVal much, but it is occasionally useful with String parameters.
Are there any gotchas?
Let's dig into some slightly technical stuff—
Are you okay? Don't choke on your Dr. Pepper.
Yeah, the above stuff was a tad technical, but a little castor oil never hurt anyone unless, of course, you're pregnant, but most of you probably aren't, so we need to talk about something that will inevitably bite us if we don't fix it.
Is the argument even valid?
We often pass objects like a Range or a Paragraph to a procedure, and we specify the type when we create the procedure.
So far, so good … [eyebrows raise]
We're writing a general procedure where we can give it any data. Even if the variable has the correct type, what if we unintentionally give it a reference to an invalid variable?
How would we know if the argument is invalid?
The first clue would be the macro crashes and complains at us, but wouldn't it be nicer if the macro checked for any bad data first? Then it could either do something smart (like assign a reasonable default value) or at least quit the macro without all the hoopla (no one likes error messages).
One basic check we can do before proceeding with the rest of the macro is whether the variable even refers to a valid document element. Let's talk about a Paragraph parameter to be concrete.
Check for invalid cases
When creating a function, we should consider whether the data being given to it is even valid. A rough conditional statement might look something like:
How do we check for an invalid paragraph?
What do we do if it's invalid?
We usually want to exit the procedure peacefully. For a function, we also want to let the user know something went wrong, so we should assign a return value that indicates an invalid result.
Examples of invalid returned values
Any invalid values will depend on the data type and what the result is evaluating, but examples include:
- If the function returns how many characters were deleted, we might assign a result of -1 since a character count cannot be negative.
- For any object, like a Range or a Paragraph, we can assign a value of Nothing as the returned result.
- True or False (Boolean) results should probably default to False.
- Strings often default to an empty string "", but other default values might make sense in some circumstances.
We're looking for reasonable values that cannot correspond to a valid result as a clear flag that something went wrong. It's a good idea to add a comment at the top of the function to indicate what the values mean. It may be obvious, but it's better to be clear.
Is the argument Nothing?
Several document elements are represented by VBA objects. Our toy case uses a Paragraph parameter, and a Paragraph is an object data type.
VBA assigns a value of Nothing (yes, literally) to any object not yet assigned to a valid document element (or whatever the object represents). Unfortunately, we don't compare objects with an equals = sign in VBA like we do with numbers.
Visual Basic introduced a keyword "Is" to compare objects, and VBA inherited it. Since Nothing is a value related to objects, we can literally compare whether an argument is Nothing like … Is Nothing.
This is one of the places where VBA strives to read more like English, so the programming is easier for those less comfortable with it. This condition gives us a Boolean value (True or False) result answering the question.
Assign the invalid result
An invalid result varies by the function and the data type, but a couple examples include:
While VBA will assign a default value even if we don't do so, assuming it is what we want it to be is not a good practice. We should assign the intended invalid result explicitly before exiting the function. In some macros, we should go a step farther and assign an invalid result even if the macro encounters an error (if it crashes, it quits immediately).
Typical argument validation for a subroutine
We could just exit a subroutine if the argument is invalid. It's common to check near the beginning and exit immediately if so. Without a lot of explanation, a quick validation step for a Paragraph parameter SomeParagraph inside a subroutine might be:
If SomeParagraph is not assigned to a valid document paragraph yet, it's value is Nothing (automatically set by VBA). If so, this If statement immediately exits the subroutine. This If statement uses the condensed form on one line for clarity.
Typical argument validation for a function
If a validation is performed inside a function, we should also set the returned result to an invalid value before exiting the function. Suppose we have a function parameter, SomeRange, in a function, GetNextHeadingRange (similar to our Go to heading macro).
This stretches the If statement and thus the function longer, but such validation checks are part of the extra work when creating procedures.
Not foolproof
Checking whether the argument Is Nothing is only a rudimentary validation. We could also check whether a number is within a certain document range, whether a given range is inside the visible page range, and many other conditions. With that said, we're creating focused editing macros not business applications. We'll add some extra validations here or there, but we probably wouldn't include stacks of them.
Are there any downsides to creating procedures for our macros?
Well, not really, but a few shadows might creep up on us and make us feel like procedures are more trouble than they're worth.
- Half of the point of VBA is to make macros more accessible by keeping them easy to read and understand. VBA functions look and feel more like programming while we're writing them, but the main macros usually read better once we're done.
- We often need to add validation steps inside the function to ensure it doesn't encounter any problems with the data it receives. This extra work is often not required if the same steps are contained in the main macro.
- Functions are sometimes an itty bit, just ever so slightly slower, but we would never notice the speed difference in a practical editing macro.
However, the benefits of using procedures far outweigh any slight disadvantages.
Go forth and make functions
Creating VBA procedures is like forging our own custom tools for our editing toolbox. Once we get past how they feel more like a writing a computer program than an editing macro, they make the main macros easier to create and understand because we can think about the problem at a higher subtask level. Provided we use descriptive names, the main macro steps often read even more like English, and VBA already excels at its English-like commands.
While functions and subroutines take time to create, they almost always pay off in saved time and effort in other macros.
