We create a function to trim a character from either side of a range if it appears only on one side.
Thanks for your interest
This content is part of a paid plan.
Trim characters from a range
Some editing subtasks are specialized for a small set of macros, but they're also a little wordy inside the main macro. Trimming characters from a range is a relatively simple task, but it requires several variables and multiple steps to accomplish inside our move sentence macros. We can extract the steps into a separate function, so they become a logical subtask that we can just use as needed, making the main macro easier to read and write.
We want to trim one of two given characters from either side of a range if they only appear on one side. Typical uses include trimming double quotes from a sentence of dialog text or a parenthesis if it only appears on one side of the sentence.
Create the empty function
Open the VBA editor via Alt+F11 in Word for Windows (or Option+F11 on a Mac). The empty macro has the same structure as the previous ones we’ve created.
The comments begin with a single quote ' character and describe how the function works. The underscore _ character continues the long header line for better readability, but VBA does not care unless it exceeds 25 lines.
About the name
The suffix "Xor" in the name refers to a Boolean operator Xor, and it describes how the function trims the characters. The Xor operator stands for "exclusive or", and it gives a True result only if one condition is True. It is False if both conditions are either True (the exclusive part) or False.
We could pick a simpler name, but it's descriptive using only three extra characters.
What are the parameters?
We obviously need a target range as input. We conveniently call it TargetRange and give it a data type As Range.
The function needs the trim characters. Since we allow different characters for either side, perhaps a left and right parenthesis, we add two String parameters, sLeft and sRight. We also declare them as ByVal, meaning "by value," because we reuse the variables inside the function. Without ByVal, these changes would affect the external variables passed to the function by the user (see our introduction to functions and subroutines for more information).
All three parameters are required, and they must be separated by commas. While it would make sense to add some default values for the trim characters, it's not essential.
What is the return type?
The function result will be the trimmed range. If no characters are trimmed, the original range is returned. We provide a return type “As Range” at the end of the Function declaration line. We assign Nothing to the function name as a placeholder result.
See a previous article for a description of what Nothing means for VBA objects, but briefly, VBA assigns Nothing to objects not yet assigned to a valid document element.
Trim a character from the range
How should the function work?
- Start with a target range and the given trim characters
- Check whether the left or right trim characters are only present on one side
- Trim the character from the range, if appropriate
Define a working range
We declare the working range variable to be clear.
Dim is the keyword to declare a variable in VBA. We’re using a simple working range name r, so it’s easier to type.
Assign the working range
We use the Duplicate property to set our working range equal to a copy of the target range.
Working with a copy is important because any changes to the TargetRange parameter inside the function will also change the argument outside the function. While this may sound reasonable, it's better to let the user store the result in whichever variable they chose rather than directly modifying the argument they passed to the function. In principle, we should not change an argument as a side effect (see our introduction to functions and subroutines article for more explanation).
Restrict the trim strings to one character
The sLeft and sRight trim character parameters are just strings. What if the person using the function gives the trim text as "abc" and "def". These are longer than a single character, but the function specifically checks and trims a single character from either side of the target range.
We use of a standard string function Left(…) which literally returns the leftward portion of a given string.
SomeText is the text we wish to truncate. Length is a counting number indicating how many characters to keep.
We usually store the result in a variable. In this case, we just reuse the sLeft and sRight parameter variables which we can do because we declared them as ByVal (see above explanation). We want a single character in each string, so the length is 1 for both.
The gotchas below talks about avoiding empty strings "".
It's a good practice to treat input data with suspicion until verified, and these reassignments are consistent with the intent of the function. Also, the internal logic assumes one character, so the function would not act as expected if it used a longer trim string.
Get first and last characters
We declare two plain text variables to store the respective first and last characters of the range for extra clarity.
For the later comparisons, we can access the first character of the range using the Characters collection.
The collection contains all spanned characters, but we want the First one.
The First property actually gives us the character range, but we need to reference its Text property for the text. We finally store the character in the appropriate variable.
Similarly, we get the last character of the range using the Last property.
When do we trim a character?
Let's focus for a moment on a specific example to clarify how the function will work. If we're editing dialog text in a novel, the macro should naturally interpret whether the dialog text is a full sentence or just one of several sentences inside the double quotes. A similar effect occurs with parentheses where we just want the sentence text if multiple sentences are present within the parentheses.
With these examples in mind, we should trim the double quotes if they only appear on one side of the target range since that would imply more sentences exist inside the dialog. We need some logic to detect the desired character on either side of the range.
When do we trim the left character?
We need to check the first and last characters to decide whether we trim one of them.
First character condition
We compare the first character to the given left character.
For text comparisons, equality requires an exact match including the length and alphabetic case, if appropriate.
Not assigning a value
The equals = sign doubles as a comparison operator meaning the result will be a True or False (Boolean) value when it's used in a conditional statement. It's unfortunate VBA uses the same notion for assigning and comparing values, but we're stuck with it.
Second character condition
If the left character is present, we also need to know if the right character is not present. The second condition is:
The not equals <> symbols is the opposite of equals. We can't type a plain text mathematical not-equals ≠ symbol on a keyboard, so some programming languages use this "less than or greater than" symbol to indicate not equal.
Compound condition
We only trim the character if both conditions are True which corresponds to the “And” operator.
And requires both conditions to be True before the compound condition is True. Otherwise, it gives a False result.
Trim the left character from the range
The command to remove the left character from the beginning of the range is the MoveStart method.
MoveStart works like the name implies. It moves the Start position of the range by a given Unit and Count. It does not change the End position unless Start moves past the End. The default movement is forward by a single character. This is what we need for the trimmed character, so we can omit the Unit and Count options.
Trim the left character conditional statement
The If statement to remove a left double quote when appropriate is:
This conditional statement is relatively concise, so we can condense it onto one line.
When do we trim the right character?
Alternatively, we may need to trim the right character. The logic is very similar, but the conditions are reversed.
We need to ensure the left character is not present before we trim the right one from the range.
Trim the right character from the range
The MoveEnd method will trim the right character from the end the range.
MoveEnd moves the End position of the range without changing the Start position (unless End precedes Start). The negative Count value moves backward in the document by 1 Unit. The default movement unit is a character, so we can omit the Unit option.
Trim the right character conditional statement
Following the above pattern, the conditional statement to possibly trim the right character is:
We use the condensed form of the statement since the command is relatively simple.
Gotchas
What could go wrong in the function?
We have several validations in this function. It's annoying to stack them since they require almost as many lines as the rest macro, but it's also safer. No one likes a function that crashes unexpectedly. A function should handle the input data responsibly and give reasonable results even if the input data was invalid.
What if the target range is invalid?
This is a common gotcha for any function or subroutine that has an object parameter. If the TargetRange parameter is not yet assigned, then it will have a value of Nothing. Unfortunately, such an object will cause an error if we try to use any of its methods or properties, so we check for this value and exit the function immediately, if so.
VBA uses the "Is" keyword to check whether two objects refer to the same thing. This also applies when comparing it to Nothing.
The result will be a True or False value which we can use to make a decision in a conditional statement.
We need to return a value as the function result and then exit the function. An If statement to avoid an invalid target range argument is:
Nothing makes sense as a result since the input data was not valid.
What if the trim characters are empty?
What if the user provided an empty string "" for either trim character?
Ughhh.
Yeah, we gotta be careful if we want the function to be complete. The two conditions are:
We could set a default value, but it's probably clearer to require specific input from the user. If either trim character is empty, we just exit the function.
We need an Or operator since we exit the function if either or both strings are empty. Or yields a True result if one or both of the conditions is True.
Since the result is the same as that for an invalid range, we can combine them into one conditional statement which we do in the final function.
What if the target range is empty?
If the TargetRange parameter is valid but empty (Start and End positions are the same), this function doesn't make sense since no characters are present to trim from the range. The empty range condition is:
If so, we just return the empty range without any modifications and then exit the function.
However, we can combine this empty range validation with the next one.
What if the target range has too few characters?
We can do better than checking whether the range is empty. Logically, the function only makes sense if the range contains at least two characters.
We can get the number of characters in the range using the Count property of the Characters collection.
Unfortunately, Count is ambiguous for empty ranges since VBA attributes one character to them even though they are empty, but it works for more than one character. If the range is invalid for the purposes of this function, this Count value is less than two.
This uses the plain ole less than < symbol from general mathematics. Using this condition in an If statement, we assign the original target range as the result and just exit the function.
We must check for an assigned target range before this since we're referencing the Characters collection. If TargetRange were not assigned to a valid document range, even one that is empty, this check would crash the function.
Final character trim function
The function to trim a character from either end of a target range is:
It is a specialized function, but it helps make a few other macros more concise and easier to read. The “Xor” suffix in the function name refers to the Boolean operator Xor. It’s like Or, but it’s only True if one of the conditions is True, not both like with the Or operator.
The function includes extra comments to be clear about how it works.
Examples of using the function
Typical use cases might be parentheses from one side of a sentence range. Let's assume the sentence range is valid and is named rSentence.
The straight double quotes around the parentheses are required because we need to specify a plain text string.
The double quote version uses some previously defined character constants, but we could also pass the characters explicitly as "“" and "”", respectively, but these constants are not clear.sm
We do not need straight double quotes around LeftDQ or RightDQ because they are already string constants. In fact, "LeftDQ" would be a different string not a double quote character.
Of course, the advantage of the function is we could add square bracket or brace versions, or any other characters desired.
Disadvantage of using the function?
We perform several validation checks early in the function. These are necessary, so the function will not crash on bad input data, but it's also somewhat inefficient. On the other hand, they literally take less than a millisecond, so it will almost never matter in practical use cases.
