We implement a utility function to delete any spaces at the end of a given paragraph.
Thanks for your interest
This content is part of a paid plan.
Delete spaces at the end of a paragraph
Deleting spaces at the end of a paragraph seems like a trivial task … and it is, but the goal of this article is to encapsulate the steps into an easy function as a logical subtask. If an editing macro needs to ensure a paragraph does not end with a straggling space, just use this function. Done. We don't need to think about subtask details again in the main macro.
A previous pair of macros moved a sentence forward and backward in the document. They carried out their tasks well, but they were awkwardly long due to the extra steps required to ensure proper sentence spacing after the move. Focusing on a particular spacing correction, sentences occasionally move between between paragraphs, so one subtask deleted any extraneous spaces at the end of either the source or the target paragraph. The spacing needed to be checked for both the source and target locations in each macro, meaning the steps were repeated four times between the two macros.
A common way to create a good candidate function is to identify such groups of general purpose steps, and it's even more apparent when the steps repeat as they did with the end-of-paragraph spacing corrections. We present the previous group of steps for completeness, but we'll cover the problem from scratch in this article for completeness.
Move sentence article series
We're building several functions to streamline the previous move sentence macros.
- An essential subtask gets the current sentence range while excluding the ending paragraph mark. The function further includes or excludes double quotes or parentheses as needed.
- The current article covers deleting any spaces at the end of a working paragraph.
- Another function ensures proper spacing between sentences.
- We also need to check for and delete an empty paragraph as a result of the sentence movement.
- In the process, we need another utility function to get the next intuitive character after a range.
Each of these functions is also general enough to be useful in other macros, so they make great additions to a general VBA editing macro toolbox.
Create the empty function
We begin with the empty function. Typically we open the VBA editor using Option+F11 in Word for Mac (or Alt+F11 in Windows) and start typing.
What are the basic features of the function?
Of course, it needs the Function keyword and a unique name. It finishes with End Function keywords. Our function steps go in between, of course. Including descriptive comments using the single quote character is strongly recommended.
What is the parameter?
We often want to reuse the same function with other macros, so we design it to receive data. In the function, it's called a parameter, and the data a user gives it is called an argument. With this function, we’re working with a paragraph, but a sibling article implements a similar subtask using a provided Range instead.
Using a paragraph as input let's us work with a known structure. A typical paragraph begins with some text including one or more sentences. It then ends with a paragraph mark. The latter point would not be assured if we were working with a general document range.
A Paragraph is an object type in Word VBA which has its own methods (actions) and properties (data). It's almost like a document range, but it's more specific. We'll call our working paragraph TargetParagraph, and we declare it as a Paragraph data type inside the parentheses.
What is the result?
It's a function, so we expect a result from it. Functions can also carry out actions as this function does, but we expect a final result summarizing what happened inside the function.
The returned value will be the number of spaces deleted. In principle, it's a small counting number, but programming history and progress leaves us with several choices of how to represent the number.
A typical number of spaces we might need to delete would vary from zero, to a few, or perhaps ten or more in unusual cases. Hundreds would be strange. These values are small enough we could use an Integer number type which allows numbers up to about 32 000. A Long number type allows far bigger numbers up to about two billion, which is excessive for this function, but it is used often in VBAs own methods and properties, so we'll use it in this function also.
We added “As Long” on the header line after the parentheses to indicate the returned data type. The returned result should be assigned to the function name at any step before the end of the function.
We just assign a default value of zero for now because we don’t know how many spaces were deleted yet. An obvious default value is zero. The move command below actually tells us if no spaces were spanned, so it handles the default case automatically.
Previous macro steps
For reference, the steps that inspired the current function appeared in the previous move sentence macros. This group of steps deleted unwanted spaces at the end of a given paragraph using a range variable. Rather than dissecting each line here, we're using it as a basis for this function which we tackle from scratch below.
This pattern was repeated in the main macros a total of four times with only small changes in between each use. As simple as the idea sounds, that's begging for a function.
Delete end of paragraph spaces function
What do we know about the subtask?
- We’re generalizing the above steps into a function working with a Paragraph since it is a common case (it's also easier).
- The above set of steps worked with a range, rSentence, which was already positioned at the end of the paragraph. This function will instead be given a document paragraph.
- We're working with a Paragraph variable, but most of the relevant methods that manipulate the content are actually Range methods, so we'll need to convert it to a Range variable.
Do we copy and convert the previous steps?
A direct approach is to copy the above steps over to the new function and just make changes. The intention is to save time, and it can sometimes … but sometimes it doesn't either.
Even when I've already worked through the logic in a different main macro, experience has nudged me toward creating the function version from scratch. It's often easier to think separately through the details and any new gotchas. Neither approach is wrong, but the necessary steps will usually change and probably expand because the general case often has slightly different assumptions compared those that were written when working inside the constraints of the original macro.
Define some useful variables
I prefer to declare any variables for extra clarity, but it's optional in general VBA.
Each variable requires a data type, or it will default to a generic Variant. We also explain each variable when it is used. If you want to enforce variable declarations for your own macros, include Option Explicit at the top of the macro module (not inside any macro).
I like preceding Range variables with an "r" as a reminder. The name rEnd reminds me that it works with the End of the paragraph range. Declaring variables takes a little effort, but it will save time and confusion in some macros by avoiding some obvious mistakes.
Define the working Range
We first set a working range to span the given TargetParagraph.
Huh? What?
You just said something about a paragraph—
Why use a working range variable?
Just hold on. Why use a Paragraph parameter for the function if we’re just going to immediately define a working range based on it anyhow.
- If we ask the user for a Paragraph, we know it includes the whole paragraph with an ending paragraph mark. No more and no less. This reduces the number of special cases to consider.
- A Paragraph object has some methods or properties, but few of them deal with directly editing the paragraph content (mostly adjusting paragraph-level things like structure or formatting). Using a working Range variable allows us to navigate and modify the document content.
- While we begin with a target paragraph, the bulk of the steps work with a small range at its end.
A separate article uses a more general Range variable input, but assuming the user gives us a valid paragraph simplifies the logic because we know the extent of the paragraph content in the document. We immediately convert the given Paragraph argument to an internal Range variable as a practical step, so we can work with the end-of-paragraph text.
Assign the working range variable
A Paragraph is not a Range, so We refer to its Range property and store it in the previously defined working range variable.
Using Set is required when assigning a document range since it is a VBA object. Objects store more than just plain values like a number or a text string.
Exclude the paragraph mark
A paragraph mark is a single character in Word, so we remove it from the End position of the Range using the MoveEnd method.
MoveEnd literally moves the End position by a specified Unit from a table of unit constants. The default is to move by characters, so we can omit the unit option. We can also control how many units are moved using Count option, and backward movement uses negative values. Thus, we assign -1 to the Count option.
This is an example where knowing a specific starting state makes the logic easier. We know a single paragraph mark is at the end of the paragraph range, so we can take a specific action based on that information. If we had a general target Range parameter, we would need something different.
Collapse the working range
We Collapse the working range since we’re planning on selecting the spaces using the range.
We collapse toward the end using the Direction option since we want to eliminate any spaces there. Now, our empty range is positioned just before the paragraph mark and just after any spaces if they are present.
Extend over any spaces
The working range is now empty, so we want to span any spaces before it. More specifically, we move the Start position of the range backward over any spaces using the MoveStartWhile method.
MoveStartWhile requires a character set as a plain text string, so we assign a space character " " to the Cset option. We want to extend the range backward, so we also need to assign the constant wdBackward (from a table of Word constants) to the Count option. The two options must be separated by a comma.
MoveStartWhile does not change the End position of the Range, so if any spaces exist, the working range will span them after this step.
Get number of characters moved
The catch is we need to track how many characters the Start position moved since that is the returned result of the function. MoveStartWhile tells us how many characters Start moved. We often ignore this information, but this time we store the value in a variable for later use.
MoveStartWhile returns a Long value indicating how many characters the Start position moved, so our SpacesMoved variable is also a Long number type. By storing the result, we're using the MoveStartWhile method like a function, so VBA requires parentheses around the arguments.
Correct the sign of the move result
MoveStartWhile returns a negative value if the Start position of the range moved backward in the document (or zero if Start did not move). The number of spaces deleted is conceptually either zero or a positive number, so we need its absolute value.
Abs(…) is the standard absolute value function we learned in general mathematics years ago just in the computer world. Absolute value returns the positive version of whatever number is given to it (i.e., drop any negative sign). The absolute value of zero is still zero.
We don't need to declare an extra variable just for this, so we just stored the result back in the same variable. Now, it is either zero or a positive value which makes more sense.
If someone cringes at this simple step … (aside)
It's annoying to include an extra line for just the absolute value, so we could combine the two previous steps.
This command looks a lot like real programming. Summarizing what's happening, the MoveStartWhile method runs and VBA immediately passes that result to the absolute value function. The result of the absolute value is then stored in the variable.
It's a step higher thinking (more like a programmer), but it's also common for those that prefer concise macros. Two clear lines or a single compact, but harder to read line. Some people like broccoli and other consider it to be rabbit food. Personally, I like broccoli steamed al dente with just salt. Yum.
Delete any spaces
We want to delete any spaces contained in rEnd, but we can’t just use the Delete method directly.
Why not?
What if no spaces were at the end of the paragraph?
Delete will delete any spaces spanned by the range, which is what we want. If no spaces were found, meaning rEnd is empty, the command would still delete the character to the right similar to tapping the (forward) Delete key on the keyboard. In this case, that character is the paragraph mark, and deleting it would merge two paragraphs.
Hmmm.
We only want to delete something if spaces were found.
Are any spaces spanned condition?
We need to check whether any spaces are spanned by rEnd. We could verify at least one space exists in the range, but we've already stored the number of spaces spanned in the SpacesMoved variable. Why not use that result?
The condition to check for any spanned spaces is simply whether SpacesMoved is bigger than zero.
Intuitive character count condition (gotcha)
Strangely, we cannot check how many characters are stored in the working range using the Count property. This does not work because even empty ranges will indicate they contain one character.
This would be logical condition to tell us whether the range is empty, but VBA treats empty ranges as containing a single character (the one just to the right of its position). Unfortunately, a range that actually spans a single character also contains a single char. The overall treatment of empty ranges in VBA is counterintuitive, so we must avoid this seemingly obvious condition.
Do any spaces exist conditional statement?
A rough conditional statement checking for spaces would look something like:
Putting the above condition and the delete command together, a tentative conditional statement is:
But we have a small problem …
Delete a straggling space
A helpful little Word feature appears out of nowhere. Like that annoying sibling that pops up at the wrong time. Word adjusts spacing for us as needed during manual editing.
It acts like, "Ooops, you forgot a space. Let me fix that for you."
It's often helpful in real-time editing, but for macros, it borders on annoying.
Did Word insert a new space?
We need to first check whether Word actually inserted the space and delete it if so. The simplest approach is to just check whether the character immediately after the empty working range is a space.
The range is empty at this point, but the "extra" space will not be inserted until after we delete the spanned spaces. See … annoying. The correction must be done as a separate step.
Get the first character after the working range
Without getting bogged down in VBA muck, the working range rEnd is currently empty near the end of the paragraph. If Word inserted a space for us, it will be just after the empty range. Intuitively, this would be the "next" character, but VBA treats it as the "first" character when the range is empty (different if the Range spans any text).
We refer to the Characters collection of the range variable.
The First property of the collection gives us the first character range.
The First property returns the range of that character, but we need its text for the comparison. We use the Text property of the character range.
We store it in a conveniently named variable, so the conditional statement below is clearer.
Delete space conditional statement
Our condition simply checks whether this first character is a space " ".
This condition will be evaluated as a True or False (Boolean) value when it's used in a conditional statement.
Delete the space command
If a space exists, we just delete it with the Delete method.
The first character reference and the delete command both rely on the rEnd variable being empty at this point in the macro. The previous use of Delete acted on a range spanning some document content, so it deleted that content. This command deletes the first character to the right of the range much like tapping the (forward) Delete key on the keyboard would do if we had an insertion point (i.e., the blinking I-bar) in the document.
Delete straggling space conditional statement
Putting the conditional statement and the delete command into our quick conditional statement, we have:
It is annoying to include an extra conditional statement to take care of a single space reinserted by Word after we deleted the others, but we are now sure no spaces exist at the end of the target paragraph.
Assign the return value
We now know how many spaces were spanned, so we can set the return value at the end of the function appropriately.
This value is returned to the macro that called the function. It also works when no spaces were deleted because the MoveStartWhile method will return a value of zero in that case. This number does not include the extra space Word reinserted during the process.
Gotchas
What could go wrong?
Is the Paragraph valid?
A common error to check is whether the data given to the function is valid. This function is passed a Paragraph variable, so a simple validation we can perform is to just check whether the argument actually refers to a paragraph in the document. A more thorough explanation of such a validation check is given in the previous move sentence article, but we summarize it here.
Condition checking whether the target paragraph is valid
VBA assigns a literal value of Nothing to any object variable that is not yet assigned to a valid document element. We cannot compare VBA objects with an equals = sign, so we need to use the "Is" comparison (operator). The condition for whether the TargetParagraph variable is not yet assigned to a valid paragraph is:
Yep, it's that simple. If the TargetParagraph is not yet assigned, we need to exit the function.
But this is a function … so we gotta think about the returned result.
Assign an invalid result for the number of deleted spaces
Since this is a function, we should not leave the returned result to some default value decided by VBA. We need to be specific. The number of spaces deleted cannot be negative, so assigning a negative value will indicate an error occurred in the function.
Conditional statement validating the target paragraph
including the above pieces, our conditional statement is:
Anyone using the function can check if an error occurred. This should be included at the beginning of the function, since we cannot take any other action with an invalid Paragraph variable. The Exit Function command immediately quits the macro without running any further steps.
Final delete end-of-paragraph spaces function
Now let’s put the function steps together. This one returns how many spaces were deleted.
The bulk of the steps are similar to the group of steps in the original move sentence macro, but this function required some extra steps to generalize it.
Examples of using the function
Having most of the above steps inside the main macro makes it hard to read, but a simple call to this function is much easier to understand. We'll assume our main macro has a valid document paragraph stored in a variable named SomeParagraph.
Ignore the returned result?
If we don't need to know how many were deleted, we can just use the function like a subroutine to carry out the action. It's easier to just use the function name and the paragraph variable.
After either example, we can be sure the paragraph stored in SomeParagraph will not have any spaces at the end regardless of whether any were present or not. Both of these versions ignore any information returned by the function.
The second version specifically assigns SomeParagraph to the TargetParagraph parameter name as given in the header line of the function. A colon equals := symbol is required when assigning an argument to its parameter name.
Store the returned result?
If we need to know how many spaces were deleted (perhaps the main macro requires a later validation), we can store the value returned by the function in a separate variable.
VBA requires parentheses around the argument SomeParagraph since we're storing the value in a variable. We can also include the parameter name inside the parentheses if we want to be extra clear.
