Detect punctuation mark type

Some VBA functions are short, but they make things more convenient when writing macros. They help keep our main macros less cluttered with copies of repeated commands to accomplish common tasks.

A task that pops up occasionally is to check whether a particular character is a punctuation mark. More specifically in Word macros, we often make text spacing adjustments based on the context of our document, so we need to distinguish between different types of punctuation marks.

Punctuation categories

For our functions today, the various types of punctuation marks include:

• Sentence ending
• Usually preceded by a space or paragraph mark
• Usually not preceded by a space or paragraph mark

Since we don’t have any specific terminology, we’ll refer to the second group as “left” punctuation marks in analogy with left single or double quotes. We’ll call punctuation marks that are usually not preceded by a space or paragraph mark a “right” punctuation mark.

The main function covered below will focus on the last case, but the extended content shows simple variations to detect the other two cases. Of course, the first category overlaps with the last. Some characters are omitted because they are not used often, or their typical text spacing is not consistent.

What’s the plan?

Our task is simple. We literally compare a given character to a list of specific punctuation marks: ? .  ; , ! : ; ] ) right single quote, right double quote, en-dash, or em-dash, and others depending on which question we’re asking. We then return whether we find a match.

Create function skeleton

We start by creating the function skeleton for the last case above.

The return value will be modified below.

Return type

We’ll return a True or False (Boolean) value based on whether the character matches any punctuation marks in the list.

What parameters to use?

We accept a String parameter sPunctuation corresponding to the single-character punctuation mark to check.

Get first character

A general String (plain text) variable can have more than one character. While there is a way to declare a fixed-length string, it doesn’t play nice as a function parameter in VBA, so we’ll manually look at the first character of the given punctuation string.

We use a standard string function Left(…) that returns a given number of characters NCharacters from the left side of a string.

We pick out the first character in our given sPunctuation variable and store it in a variable c.

Any other characters after the first are ignored. For example, if someone called the function using the string “;abcdef” then after this command, the variable c would store just the semicolon “;”.

I often use more descriptive variable names, but here we have a long chain of comparisons to check for punctuation matches, so I made the variable name super short.

Return comparison result

Returning a value in VBA is the same as assigning a value to the function name.

Single character example

If we check versus a single character, such as a comma, we return a True or False (Boolean) value based on whether it literally equals = a comma “,”.

The double quotes around the comma “,” are required when indicating a character is a text string.

The parentheses aren’t strictly necessary, but I like to put them around the comparison to clearly separate it from the variable assignment. Unfortunately, the respective equals signs mean two different things. The left = is a value assignment to the function variable name, but the right one indicates a logical True-False comparison.

Arghhh. I don’t like this notation inconsistency in VBA, but we’re stuck with it.

Sentence ending punctuation

We can include more characters in the comparison, but we want any of them to give us a True result. That sounds like an Or operation (see another article for more explanation).

For sentence ending punctuation (see extended content), the returned value would be:

Right punctuation marks

We also need a version for right punctuation marks (those not usually preceded by a space). There are too many to include everything, so we include the more common ones. Even then, the list is a little long. Regardless, we chain all the choices together with Or operators.

When a command exceeds a line length, we can continue the line using an underscore _ at the end. There is a limit to how many continued lines, but it’s not usually a problem.

Some special characters are system dependent where character numbers change between Word for Mac or Windows, so several cases above use functions defined in a previous article.

Some style guides have varied rules such as spacing around a dash in a sentence, so add or remove characters as needed.

Unfortunately, VBA does not short circuit logical conditions like this (i.e., stop as soon as it finds a True result) like some other languages, so it should probably not be used in a time-intensive portion of a macro unless necessary.