Heading

This is some text inside of a div block.
This is some text inside of a div block.
This is some text inside of a div block.
11 Jan 2022
•
This is some text inside of a div block.
min read

Delete range forced

VBA Functions
Word • Macros • Editing • Functions
Peter Ronhovde
March 12, 2024
•
10
min read

When deleting a range in a macro, we often need to ensure proper spacing whether between words or sentences afterward. Toward this end, we need to detect and reverse Word’s automatic spacing adjustments while the macro is running.

Thanks for your interest

This content is part of a paid plan.

Delete range forced

This is another in a series of functions intended to streamline our editing macros allowing us to write better macros faster.

The Problem

When deleting or moving text, Word sometimes adjusts spacing based on the context. In real-time editing, this is usually a net positive since it saves us from manually correcting some spacing issues, but in macros, the automatic spacing adjustments by Word can be frustrating. We still want to ensure proper spacing before a macro ends, so we need to detect and correct any automatic adjustments to the text while the macro is running, so the text finishes in the expected state.

This function is more of a convenience since repeatedly testing and correcting for automatic spacing adjustments by Word is annoying, and it unnecessarily complicates our main macros.

Original Macro Sample

In past macros, we've repeatedly checked for and sometimes deleted extra spaces around text. This is obviously a very general task, so it makes a good candidate function. A sample of similar steps from the previous move sentence macros are as follows:

' Delete working range
r.Delete

' Delete straggling space Word sometimes reinserts
FirstCharacter = r.Characters.First.Text
If FirstCharacter = " " Then
r.Delete
End If

These particular steps have been edited for clarity. We do this a few times in the move sentence macros, but the basic steps are quite general.

What’s the plan?

It's a good practice to think about how the macro will work. I wouldn't quite say you should delve into as much detail as possible, but considering some of the details before you start creating the macro is probably a good idea, In this function, our basic plan of action is simple:

  • Check range conditions (see below)
  • Delete given range
  • Adjust any final spacing depending on whether Word automatically inserted a space

We’ll need to add some checks for any spaces nearby the range to help use determine whether to correct for Word’s automatic spacing adjustments. We also need to check for some other possible problems such as:

  • Is the initial range invalid? Don’t do anything if so.
  • Is the given range empty? Don’t delete anything if so.
  • Is a space already present outside the given range? If so, don’t delete it since Word didn’t insert it.

In practice, we’ll probably be deleting a target range full of spaces, but it’s actually less work to just assume it’s a general text range since we don’t have to verify the range contents before deleting anything. Plus it makes the function more general for other possible macros.

Let’s translate the above steps and conditions into a function.

Create function skeleton

The function skeleton is basically the same as previous ones we’ve created.

Function DeleteRangeForced(r As Range) As Long
' Include function steps ...

' Return how many characters were deleted
DeleteRangeForced = -1
End Function

The return value is temporarily set to -1 since we don’t know what it is yet.

What parameters to use?

Since we’re deleting a range, we need a target range as a parameter in the parentheses which we’ll just call “r” for simplicity.

Return type

What do we return from the function? We don't necessarily have to return anything (we could change it from a function to a subroutine instead), but it's nice to give the user feedback about the progress made during the function.

We probably shouldn't return a range from the function because we’re deleting it. Plus, the user would already know the location of the empty range.

The only other value that makes sense is returning how many characters were deleted, so we added “As Long” after the function name. Remember a “Long” value is just an Integer with a larger possible maximum value.

Return values

Recall the return value is set by assigning a value to the function name somewhere inside the function. We don’t have to assign the return value at the end of a function. The value DeleteRangeForced holds when the function ends is its returned value.

If the range is empty, we return 0 characters.

If the user gives us an invalid range, we return an error value of -1. This indicates to the user there was a problem with the given range since we can’t delete less than 0 characters. By invalid, we do not mean the range was empty.

Including an error case is common since we should communicate to the user if something went wrong especially if it's with the data they provided to the function.

In the regular case, we return how many characters were deleted.

Check special cases

Often, there are some initial special cases we can or should consider before continuing with the main steps of the macro.

Invalid range

If the range is invalid, we definitely want to quit the function without doing anything else. We also want to let the user know about the problem, so we return an invalid value of -1 since we can’t delete less than zero characters.

An invalid range is most likely one that has not been properly assigned to anything yet, and it has a value of “Nothing”. We use the notation “Is Nothing” in the condition check since it is not a regular value.

' Check whether target range is valid before continuing
If r Is Nothing Then
DeleteRangeForced = -1 ' Return invalid value
Exit Function
End If

We make sure to exit the function immediately, so no other later references are attempted with the invalid range r. If we try to reference an invalid range, we’ll get an error.

Empty range

If the target range r is empty, meaning it spans no characters, we just exit the function, but we still return the value of 0 since no characters were deleted.

Detecting an empty range

An empty range is one in which the Start and End positions are the same, so we can check those positions explicitly against each other:

' Check whether target range is empty and exit if so
If r.Start = r.End Then
DeleteRangeForced = 0 ' No characters deleted
Exit Function
End If

We exit the function immediately because no other checks are necessary.

Count property does not detect an empty range

Note we cannot check whether a range is empty by looking at how many characters are in the range since the Count property has a value of 1 even if the range is empty.

No, no VBA. This is not intuitive.

Get next character

We need to set up for detecting whether Word reinserts a space after the upcoming Delete command. Specifically, we need to know the current character immediately after the target range.

NextCharacter = r.Next(Unit:=wdCharacter).Text

The Next command (called a method) returns the given Unit. In this case we want a character, so we specified wdCharacter as the Unit. The Next command returns the corresponding range for the requested content. We had to include the Unit option within parentheses since we needed to access the Text property of the resulting range to get the character we want, and we're storing the result in a variable.

If we wait to get the next character, it might be a reinserted space, so we have to store it now. If this character is already a space, Word will not attempt to add one (but this isn't the only criterion Word applies).

Delete range

We finally delete the range as advertised.

DeleteRangeForced = r.Delete

We store how many characters were deleted in the function name which is the returned value.

Check for reinserted space

Word may reinsert a space immediately after the Delete command depending on a few contextual considerations (no space already present, beginning of a paragraph, etc.), but we don’t need to know every criteria Word uses.

We just need to know if there was already a space present, which is why we stored the next character above before we ran the Delete command, and whether there is a space after we Delete the range r.

Get first character

We need to know whether there is a space now after the Delete command. We get the first character after the now empty range r.

FirstCharacter = r.Characters.First.Text

We use the range's Characters collection where the First reference returns the first corresponding character range. We then access the Text property of that resulting range to get the character.

Remember Word considers the character immediately to the right of an empty range to be its “first” character. I consider this unintuitive (an empty range should have no characters), but it’s how ranges work in VBA, so we’re stuck with it.

Check next character

We check if the next character stored prior to the Delete command was a space. The condition looks like:

NextCharacter <> " "

Recall the <> symbol means “not equal” which literally means less than or greater than. This condition results in a True-False (Boolean) value for whether the stored NextCharacter is not a space. Characters in VBA (and programming in general) have numerical values under the hood, so we can make this comparison directly.

Check first character

The condition for the first character to be a space is:

FirstCharacter = " "

Unfortunately, this resembles a command where we’re assigning a space to FirstCharacter, but it is used as a True-False condition in our upcoming If statement. It would’ve been more clear if VBA had adopted the notation of some other languages that use a double equals == to indicate a Boolean value.

If this first character is a space, it may be one Word reinserted into the document after the Delete command, but we don’t know for sure with this information alone.

Compound condition

We need to put the two conditions together to discern whether Word added a space after the Delete command. Both conditions must be True, so we use an And in a compound condition before we possibly delete the extra space:

If NextCharacter <> " " And FirstCharacter = " " Then
r.Delete
End If

Update number of characters deleted?

    Skip this aside if you just want the working macro.

As a programming aside, we could further add one to the returned value to indicate we deleted an extra space reinserted by Word.

DeleteRangeForced = DeleteSpacesForced + 1 ' Not used

Adding one to the total would let the user know it occurred if they ever wanted to do anything with that information.

The above would be a reasonable choice; however, I chose not to include it since ignoring the extra deleted space makes a little more sense to me. After all, it’s addition and subsequent deletion would be invisible to the user, and returning the original number of deleted characters is more consistent with the returned value of a stand-alone Delete command. Our function is attempting to mimic a regular Delete command for a range just forcing the issue if Word tries to "correct" the text spacing.

It’s useful to think about how our functions and macros work in the context of others. All else equal, consistency is usually the better choice.

Final Function

Putting everything together, we have:

Function DeleteRangeForced(r As Range) As Long
' Delete target range r regardless of its contents
' Deletes an extra space if reinserted by Word after Delete command
' Spacing after target range is checked but not changed
' Returns how many characters were deleted including an extra space
' if reinserted by Word

' Check whether target range is valid before continuing
If r Is Nothing Then
DeleteRangeForced = -1 ' Return invalid value
Exit Function
End If

' Check whether target range is empty and exit if so
If r.Start = r.End Then
DeleteRangeForced = 0 ' No characters deleted
Exit Function
End If

' Get next character before delete to check below for existing space
NextCharacter = r.Next(Unit:=wdCharacter).Text

' Delete target range
DeleteRangeForced = r.Delete

' Delete straggling space Word sometimes reinserts
' Check first character after delete for possible reinserted space
FirstCharacter = r.Characters.First.Text
If NextCharacter <> " " And FirstCharacter = " " Then
r.Delete
End If
End Function

Something so simple still takes quite a few steps to carry out accurately every time.

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.