Heading

This is some text inside of a div block.

11 Jan 2022

•

This is some text inside of a div block.

min read

Pad sentence spacing

VBA Functions

Word • Macros • Editing • Functions

Peter Ronhovde

March 5, 2024

•

min read

A menial task in many text manipulation macros is correcting spacing issues after text is manipulated. The task is tiny, trivial even—inserting individual spaces between document elements—but the details matter if the calling macro is to work as expected.

When the macro ends, the spacing should match what we expect in a document. If we force ourselves to edit manually after running a macro, it almost defeats the point of creating it in the first place.

Thanks for your interest

This content is part of a paid plan.

Pad Sentence Spaces

We are building several functions to streamline our previous move sentence macro.

The Problem

In the original article, the macros to move a sentence forward or backward in the document are both awkwardly long, so we are breaking them down into smaller chunks. In practice, we would do this while creating the original macros to make the work easier, but we’re attacking the problem from the other side this time.

Original Macro Sample

To create a function, identify steps in a macro that perform a specific, usually repeated task. The move sentence macros needed to adjust sentence spacing, if necessary, at both the original deleted sentence and around the moved locations. Moreover, it seems like a general enough task to be a good candidate function.

For example, the steps below adjusted spacing after the moved sentence text:


' Ensure a space separates sentences inside a paragraph
LastCharacter = rCopy.Characters.Last.Text
' NextCharacter checks for end of paragraph exception
NextCharacter = rCopy.Next(Unit:=wdCharacter).Text
If LastCharacter <> " " And NextCharacter <> vbCr Then
' No ending space found, so add one
rCopy.InsertAfter Text:=" "
End If

The other occurrences are spread through the two macros.

What’s the plan?

To simplify the commentary, we’ll work specifically on adjusting spacing at the beginning of the given range, but after we’re done, we’ll create a second function to pad spacing on the right side of the range instead.

We need to make a few basic decisions about how the macro works. Nothing Earth shattering. We just need to decide specifically what we want to do since vague goals can lead to trouble and wasted time (I sense a life lesson there, but I will not digress).

What are our conditions?

We’ll work under several assumptions:

We’re given a target range presumably corresponding to a sentence somewhere inside a paragraph.
The target range may or may not have proper spacing near the beginning of the target range.
The target sentence range may be anywhere in the paragraph.

The follow up macro will work on the corresponding end of the range.

What about the details?

Spacing outside the range?

Do we correct any spacing issues inside and outside the given range?

Both for me at least with this function.

Short detour into function philosophy

Skip this if you just want the specifics for this function.

Inevitably, we have to make choices about how a function or macro works, what data we receive, and how we work with it.

In general, it might be questionable to make changes outside the given target range since a user (the person writing the main macro) might expect any changes to be constrained within the range they provide to the function.

That makes sense, and it’s a good programming practice in principle. However, the main point of this function is to finish with correct sentence spacing.

It’s a small, specific task that makes limited changes to a document. If we force the function user to precisely span the text before the function works correctly, it limits its usefulness. Someone (that’s probably you) writing the main macro would need to add extra steps to handle excess spaces around the planned target range.

That’s a lot of talky-talky to say it doesn’t make as much sense in such a specific function to have the user add those extra steps, and we sure don’t want to force the novel writer to make any edits after the main macro is run.

That’s not to say the former approach (constraining changes to within the target range) is wrong. It’s a preference, but given the limited action of this function, we might as well let it make necessary adjustments regardless of whether the spaces are inside or outside the beginning of the range. If there’s any question about how it works, we’ll just be clear in the description of the macro.

Include modified spacing in output range?

Do we include any changed spaces in the returned range at the end?

No, for the beginning, but yes for any spaces at the end (the second version of the macro). This matches how Word typically treats automatic selections, and since we’re returning a spanned text range, it’s consistent to mimic that behavior.

Whew …

That’s a lot of thinking for such a simple task, but it saves us from having to think as much about it later. It should just work.

Specific actions to take

Let’s decide on the details of how the function works:

Accept a target range presumably for a sentence.
Select any spaces before or after the beginning of the range.
If a paragraph mark exists before the range and any preceding spaces, delete any spaces, if necessary.
If no spaces exist add one.
If multiple spaces exist either inside or outside the beginning of the target range, delete all but one.
End with the sentence range selected but leave the preceding spaces out of the returned range.

Now let’s translate those choices to the function.

Create the function skeleton

We start by creating the function skeleton.


Function PadSentenceSpaceLeft(TargetRange As Range) As Range
' Include function steps ...


' Return final sentence range whether modified or not
Set PadSentenceSpaceLeft = Nothing  ' Temporary
End Function

Return type

It’s up to you what value you return from the function as long as it makes sense in the context of the macro. In this function, it didn’t make sense (at least to me) to return how many spaces were deleted or anything similar. It made more sense to return the ending sentence range possibly adjusted for the space padding.

With that in mind, I declared the returned data type “As Range” after the Function name and parentheses with the intention of returning the modified sentence range.

Recall ranges carry more information than just a value, so we used Set to assign it. I don’t know at this point what the sentence range will be, so I temporarily set it to Nothing at the end of the function. Nothing is a value for an object that doesn’t exist, but we’ll modify this later.

What parameters to use?

We almost always want to reuse the function in other macros, so this function requires a user to give us a valid sentence to work with it.

No sentence objects in Word

We would like to somehow ensure the user gives us a sentence, similar to how our previous function accepted a Paragraph object. Unfortunately, sentences aren’t objects in Word like Paragraphs are.

That leaves us with just accepting a generic range argument for the function, presumably one that corresponds to a sentence in the English sense.

Huh?

Yeah, sentences are just ranges, but Word will check punctuation and such under the hood when manipulating or navigating between them, so they can feel like specific objects as you work with them in your documents.

We could add some extra validation to test whether our given TargetRange is a real sentence, but it’s not important enough to include the additional steps in this function since our changes are limited to just spacing corrections at the beginning of the range.

Fill the function

I often create functions from scratch since it can be easier to think through the logic that way. Even if you copy steps from a macro in progress, you’ll probably need to modify the steps due to different assumptions used when creating the general purpose function.

Define the working Range

We first set a working range using the given TargetRange as a starting point. We’re going to manipulate our working range, and we don’t want to change the TargetRange in the process (see gotcha comment below).


Set r = TargetRange.Duplicate

I usually use a short variable name like this plain “r” . It makes the function or macro look more like programming, but it’s easier to type.

Collapse the range

We collapse the working range since we’re planning to select the spaces at the beginning of the range.


r.Collapse

Remember the default is to collapse toward the beginning of the range.

Select any existing spaces

Extend the working range over spaces in both directions.


' Extend over spaces either direction
r.MoveStartWhile Cset:=" ", Count:=wdBackward
r.MoveEndWhile Cset:=" "

We span spaces in both directions from the working range since we want a single space between sentences or no spaces at the beginning of a paragraph. Any spaces to the left of r were not in the given TargetRange.

Get first character

We need the first character of the working range to check later whether we are spanning any spaces.


' Make sure at least one spaces exists before deleting
FirstCharacter = r.Characters.First.Text

We'll decide in bit how to handle each case.

Get previous character

We need the previous character before the working range to check whether it’s a paragraph mark.


' Check for beginning of current paragraph
PreviousCharacter = r.Previous(Unit:=wdCharacter).Text

We want to know if this is the first sentence of a paragraph.

Delete any spaces at beginning of paragraph

We use an If Else statement to verify the presence of a prior paragraph. Then we Delete any spaces at the beginning of a paragraph.


If PreviousCharacter = vbCr Then
' Delete spaces at beginning of paragraph
r.Delete  ' Oops if there are no spaces
Else
' Otherwise do regular case with sentences ...


End If

Remember a vbCr special character marks the end of a paragraph, so we're checking if the character immediately prior to our extended working range r indicates a paragraph.

Unfortunately with this If statement, if the target range is at the beginning of the paragraph, the Delete method will still delete a character even if there are no spaces. We need an additional check for whether the first character is a space (ensuring we span at least one space) before deleting anything.


If PreviousCharacter = vbCr Then
' Delete spaces at beginning of paragraph
FirstCharacter = r.Characters.First.Text
If FirstCharacter = " " Then
r.Delete
End If
Else
' Otherwise do regular case with sentences ...


End If

See the gotchas below if you’re tempted to combine the two conditions for the paragraph mark and space checks into one If statement.

Range character count ambiguity

Skip this if you just want the working function.

It might feel more intuitive to just check how many characters are in the working range using the Count property and delete any spaces included based on that information.


nCharacters = r.Characters.Count

Sounds reasonable …

Do you sense what’s coming?

The problem is an empty range where the Start and End positions are the same is considered to contain one character. Likewise, a range spanning literally one character is considered to contain one character.

Umm …

It doesn’t take a rocket scientist to see a problem here, and I can’t imagine how this ambiguous “feature” made it through development of VBA.

In our case, we would be checking if a range has a single character, but given the above ambiguity, we can’t tell the difference between that and an empty range when using the Count property. It almost invalidates the usefulness of the property.

Arghhh.

We could alternatively do an indirect check with the Start and End positions, but in our case above, it was easier to just literally check whether the first character was a space since we knew any spanned characters would be spaces.

Variable name aside

As a tiny aside, we can be a little smarter with variable names to make reading our personal macros easier.

For example, if I have a variable that is a “number” of something, I generally start it with an “n” as above. I also use an “r” prefix for ranges, “s” for text variables, “i” for counting variables, etc.

I further use a lower case first letter if it changes and a capital first letter if it is a fixed value within the macro.

Ensure a single space between sentences

When our working range is between sentences (not at the beginning of a paragraph), we can just replace its text with a single space.


' Replace any spanned spaces with a single space
r.Text = " "

This approach works even if the range is empty (there were no spaces) since it replaces the empty range text with a space.

Alternative approach

Skip this if you just want the more direct approach.

An alternative approach is to just delete the spaces and use the InsertBefore method to add a space later if needed.

Unfortunately, we then we have to consider whether the range is empty as a special case, and we also need to check whether Word “helps” us by automatically reinserting a space when we delete the working range text.

Ughhh, but here is the alternative version.


' Version not used
' Delete any spaces and reinsert as necessary later
FirstCharacter = r.Characters.First.Text
If FirstCharacter = " " Then
MyRange.Delete
End If


' Check for beginning of current paragraph
PreviousCharacter = r.Previous(Unit:=wdCharacter).Text
' Use first character to check if Word automatically reinserted a space
FirstCharacter = r.Characters.First.Text
If Not (PreviousCharacter = vbCr Or FirstCharacter = " ") Then
' Add a single space before empty range between sentences
r.InsertBefore Text:=" "
End If

I originally preferred this approach, but it didn’t feel like it was working well for a tutorial. It takes a few extra steps to do the same task, but on the other hand, I think the steps are cleaner if you can get used to the logic. Sometimes a little forethought will save some effort and result in a simpler macro.

Set returned range

Word generally doesn’t count preceding spaces as part of a selection, so it makes sense to exclude them from the returned range.

Set sentence range

We modify the working range r to be the range we want to return from the function. Specifically, we want the returned range to correspond to the original sentence range but exclude any introduced spaces.

This means the modified sentence range starts at the End of the current working range but still ends at the original End position of the TargetRange argument. We redefine the working range r using the SetRange command.


r.SetRange Start:=r.End, End:=TargetRange.End

The Start and End options simply reassign the Start and End positions of the range to be equal to the given values.

Recall the Start and End positions are literal character positions in the document starting from 0 at the beginning of the document (counting even non-printing characters).

Return sentence range

We finally return the sentence range as a slightly modified TargetRange if any spaces were removed from the beginning.


' Return sentence range whether modified or not
Set PadSentenceSpaceLeft = r

Function parameter types

When someone uses this function, they must supply a Range variable, of course, but it must be an explicitly declared Range not an implicitly defined variable.

We start by declaring two Ranges: SomeRange and AnotherRange.


Dim SomeRange As Range  ' Optional for return value
Dim AnotherRange As Range  ' Required for function argument
' Call function with AnotherRange and store value in SomeRange
Set SomeRange = PadSentenceSpaceLeft(AnotherRange)

Here we give the function AnotherRange and store the resulting returned range in the SomeRange variable.

Since AnotherRange is used as the function argument, it must be declared explicitly as a Range as shown above, but declaring SomeRange as a Range is optional since VBA will infer it’s type based on the returned value of the function.

Also recall we must “Set” the returned range in SomeRange just like with any other Range variable assignment.

Gotchas

Beginning of paragraph with no spaces

The beginning of a paragraph could cause some problems in this macro if we’re not careful. In the earlier If statement, it might have been tempting to combine the conditions for the beginning of the paragraph check and the space check into something like this.


If PreviousCharacter = vbCr And FirstCharacter = " " Then
' Delete spaces at beginning of paragraph
r.Delete
Else
' Otherwise do regular case with sentences ...


End If

The problem is this will delete the first character of a paragraph if there are no spaces.

I prefer a slightly simpler logic (in my opinion; see above alternative), but it’s a little odd if you aren’t used to it. I just delete the spaces before the paragraph check regardless and add one back later if necessary.

Target range parameter gotcha

This is a more problematic gotcha.

When returning the function result, we could potentially just modify the given TargetRange directly.


' Manually remove any added space from TargetRange
TargetRange.MoveStartWhile Cset:=" "  ' Not used

The intention would be to return that range at the end of the function, but this has a negative side effect.


Set PadSentenceSpaceLeft = TargetRange  ' Has a side effect

Unfortunately, this command changes the given TargetRange regardless of whether the returned range is stored or not in the calling macro.

Huh?

For example, a user may not need the function’s returned range for anything, so she ignores it by not storing it anywhere.


' Return value is not stored in a range variable
Call PadSentenceSpaceLeft(SomeRange) ' Has a side effect

VBA is supposed to leave parameters unchanged by default (see parameter comment below), so she probably wouldn’t expect the given TargetRange to be changed by the function since she’s throwing the result away.

But it is changed.

Bad VBA. Don’t do that.

Technical by value parameter issue

Skip this if you don’t want the details.

While Word passes data to functions “by value” by default (to restrict any variable value changes inside from escaping the function), some object parameter types (ranges, for example) are still modified when changed within a function.

The details of why are well beyond the scope of this article, but it means changing the original data referred to by the variable (our TargetRange parameter) will modify the original in the calling macro (SomeRange in the above example).

Arghhh.

We want to leave the TargetRange unmodified except for any space adjustments when we’re done, so we instead redefined the working range r and returned that range.

Final Functions

Between the two macros, there are a lot of steps for such conceptually simple tasks, but they handle all special cases as necessary.

Pad spacing to left of sentence range

Putting everything together, the function to pad spaces on the left side of the given sentence range is:


Function PadSentenceSpaceLeft(TargetRange As Range) As Range
' Adjust padding on left side of target range to a single space
' except at beginning of a paragraph
' Makes necessary adjustments outside the given range
' Deletes any excess spaces


' Define a working range and collapse toward start
Set r = TargetRange.Duplicate
r.Collapse


' Extend over spaces either direction
r.MoveStartWhile Cset:=" ", Count:=wdBackward
r.MoveEndWhile Cset:=" "


' Check for beginning of current paragraph
PreviousCharacter = r.Previous(Unit:=wdCharacter).Text
If PreviousCharacter = vbCr Then
' Ensure at least one space exists before deleting
FirstCharacter = r.Characters.First.Text
If FirstCharacter = " " Then
r.Delete
End If
Else
' Not at beginning of paragraph
' Replace any spanned spaces with a single space
r.Text = " "
End If


' Return sentence range whether modified or not
r.SetRange Start:=r.End, End:=TargetRange.End
Set PadSentenceSpaceLeft = r
End Function

Pad spacing to right of sentence range


Function PadSentenceSpaceRight(TargetRange As Range) As Range
' Adjust padding on right side of target range to a single space
' except at end of a paragraph
' Makes necessary adjustments outside the given range
' Deletes any excess spaces


' Define a working range and collapse toward end
Set r = TargetRange.Duplicate
r.Collapse Direction:=wdCollapseEnd


' Extend over spaces either direction
r.MoveStartWhile Cset:=" ", Count:=wdBackward
r.MoveEndWhile Cset:=" "


' Get first character of potential spaces range
FirstCharacter = r.Characters.First.Text
' Get next character in intuitive sense for space check
If r.Start = r.End Then
' Range is empty, so get first character
NextCharacter = r.Characters.First.Text
Else
' Get regular next character after spanned text
NextCharacter = r.Next(Unit:=wdCharacter).Text
End If


' Check for end of current paragraph
If NextCharacter = vbCr Then
' Ensure at least one space exists before deleting
FirstCharacter = r.Characters.First.Text
If FirstCharacter = " " Then
r.Delete
End If


' Delete straggling space Word sometimes reinserts
FirstCharacter = r.Characters.First.Text
If FirstCharacter = " " Then
r.Delete
End If
Else
' Not at end of paragraph
' Replace any spanned spaces with a single space
r.Text = " "
End If


' Return sentence range whether modified or not
r.SetRange Start:=TargetRange.Start, End:=r.End
Set PadSentenceSpaceRight = r
End Function

This one is a little longer than the previous version because we had to get the correct next character (see below) and correct for a straggling space Word will reinsert.

The straggling space issue is again annoying in a macro because we need to delete the space(s) twice inside the If statement to catch and correct Word reinserting a space automatically. You could just have two delete commands, but I don’t like taking a chance that Word doesn’t insert the extra space in some weird circumstance.

Next character text

Remember the “next” character after an empty range (when the Start and End positions are the same) is actually the “First” character. This is not true if the Range spans any text where the next character is literally the character immediately after the End position of the range.

This pops up enough that we’ll create a separate function to handle it later.

With functions

The latter function begs us to write a couple secondary functions. For example, it would be nice to encapsulate the steps to delete the excess spaces and to get the next character both of which are annoying sequences of steps inside the original function.

Declare range

In order to use the range with the function, we need to declare it explicitly. Otherwise, we'll get an error when we try to pass it to the above function.


Dim r As Range

We didn't need to do this previously because VBA will infer the type of the variable (using a generic type), but VBA is pickier when we send data to functions.

Pad sentence spacing right with functions

The PadSentenceSpaceLeft function does not need any additional simplifying functions, but we can reduce the complexity of the PadSentenceSpaceRight function.


Function PadSentenceSpaceRight(TargetRange As Range) As Range
' Adjust padding on right side of target range to a single space
' except at end of a paragraph
' Makes necessary adjustments outside the given range
' Deletes any excess spaces


' Define a working range and collapse toward end
Dim r As Range
Set r = TargetRange.Duplicate
r.Collapse Direction:=wdCollapseEnd


' Extend over spaces either direction
r.MoveStartWhile Cset:=" ", Count:=wdBackward
r.MoveEndWhile Cset:=" "


' Check for end of paragraph using next character
If GetRangeNextCharacter(r) = vbCr Then
Call DeleteRangeForced(r)
Else
' Not at end of paragraph
' Replace any spanned spaces with a single space
r.Text = " "
End If


' Return sentence range whether modified or not
r.SetRange Start:=TargetRange.Start, End:=r.End
Set PadSentenceSpaceRight = r
End Function

See the respective articles for the functions GetRangeNextCharacter and DeleteRangeForced. They are small but convenient additions to your toolbox.

Affiliate Links

If you're interested in using Word or another tool related to the article, check out these affiliate links. I may make a small commission if you purchase when using them, but there is no increase in cost for you, and it helps to support this site and associated content.

I've been using Microsoft for Business for commercial use (that's us writers) on one of the lower pricing tiers for years. I get to use my macros, have online storage, and don't have to worry about software updates.