Macro: Intelligent Single to Double Quotes Conversion

What the Macro Does

I felt the need for this macro when I was working on a UK book that had to be converted to US style. I found myself changing single quotes to double quotes often enough that I began looking for a macro to do the job. It's not a good idea to use Find and Replace to do this en masse as the apostrophes denoting possession would also get changed, as also quotes inside quotes. In any case, I'm wary about making global changes.

I eventually cobbled together a macro for the job by adapting code from a source I don't recall now. The macro operated on text bounded by a pair of single quotes, like this: <'This is a test'.> To change the quotes to double quotes, I selected the text between the quotes and then launched the macro by clicking a button on the Quick Access Toolbar (QAT). The result was <"This is a test".> I was delighted at the labor saved.

Note: If you want to skip the explanations and use the final macro right away, jump to the end of this post. You'll find it there.

Flaws in the Macro

As time passed, a couple of weaknesses of the macro became apparent. One, I had to select the text delimited by quotes first, and then launch the macro. The more I used the macro, the more this preliminary step became a nuisance. I wanted to be able to click anywhere in the quotes-delimited text and then fire up the macro.

Two, it worked only with smart or non-smart quotes—I no longer remember which. If launched in text with the wrong type of quote, it would set out on an endless hunt for the elusive quote, and I couldn't interrupt the macro. This also happened if I made a mistake in selecting the text between the quotes, for example, if I included some characters outside the quotes. The resulting infinite loop meant I had to restart Word—not good at all! It was when this became a serious problem (I usually have a handful of documents open, and having to restart Word and then reopen all those documents was costing me time that I couldn't afford to lose, to say nothing about the chances of document corruption) that I decided to do something about it. I reached out to Paul Beverley.

Enter Paul Beverley, the Macro Guru

Paul Beverley is a freelance copyeditor — and macro guru. He's also a macro evangelist and macro popularizer. He has written a free book on macros that can be downloaded from http://archivepub.co.uk/book.html. I had approached him before, and he has always been unfailingly helpful. I explained my problem to him at the beginning of this year. Within a short time, he sent me a macro that did what I needed. I could click anywhere in the text and launch the macro. No more watching the macro going rogue and hanging Word! I was happy.

Enhancing Paul's Macro

A couple of months ago, it occurred to me that the macro could be enhanced. Since the macro was converting single quotes to double quotes (i.e., converting U.K. punctuation to U.S. punctuation), why not go a little further? The enhancement I had in mind was this: If the macro finds periods and commas outside the closing quote, it should pull them inside the double quote, which is where they belong in U.S. style. That is, <'This is a test'.> would be changed to <"This is a test.">

My first instinct was to reach out to Paul again. In fact, I did email him about this new idea. There was no reply for a couple of days, which was unusual. In the meantime, I looked at the existing macro to try and understand how it worked. I had tried just this in February and given up, but now I was intrigued. I wanted to do this myself. I first had to understand how Paul's macro worked, and then I had to figure out a way to implement my new idea. I emailed Paul again, telling him I wanted to try this myself and would ask for help if it became necessary. The next day Paul's reply arrived: he had been busy with guests, and he wished me luck.

Starting Trouble

I'm a heavy user of macros, but am by no means an expert at writing them. I use ready-made macros I find on the Net and in books. Sometimes, I tweak these macros to better suit my needs, and sometimes I borrow an idea in a ready-made macro to create a new macro that does something different.

I looked at Paul's macro, and my eyes glazed over. I didn't understand a thing. Now, I'm not exactly a programming novice; coding used to be a hobby many years ago. In fact, I had even studied and worked with Visual Basic, but I was rusty. Still, how difficult could this relatively short macro be? Yes, some VBA commands in it were new to me, but I could Google them and find out what they did.

Before I continue with my deconstruction of Paul's macro, let me return to the very beginning. What is a macro?

What Is a Macro?

A macro is a program that you can create and run inside Microsoft Word. For copyeditors, macros are a labor-saving device, as repetitive work can be delegated to them. Any time you find yourself doing something repeatedly in Word, you should think of automating it with a macro. Macros range from simple code snippets to full-blown applications.

The easiest way to create—and learn about—macros is to record a macro and examine its code. Let me give you an example of a macro that I recorded a few months ago. I was reviewing chapters returned by an author after copyediting. The documents contained many comments, created by both me and the author, that had to be deleted, as they had served their purpose. To delete a comment, I had to reach for the Review menu and then hit Delete. I could keep the Review tab open, but sometimes I had to use another Word tab, after which I'd have to hit the Review tab again (another keystroke) to access Delete and delete the comment. What I needed was a single-click operation to delete a comment: with the cursor in a comment, I wanted to be able to delete it by clicking on a toolbar button.

Recording a Macro

I decided to record a macro to delete comments. I clicked the Developer tab in the Word menu and then clicked the "Record Macro" button. Now I had to perform the action of deleting a query. I placed the cursor inside a query, clicked the Review tab, the Delete button, and deleted the query, after which I clicked the "Stop Recording" button. The macro was recorded, and I now wanted to examine its code. So I clicked the Visual Basic button under the Developer tab (I could also have used the ALT-F11 combo). Here is the recorded macro:

Sub Macro4()
'
' Macro4 Macro
'
'
    Selection.Comments(1).Delete
End Sub

It's a very simple macro, consisting of just one line of code:

Selection.Comments(1).Delete

I linked this macro to a button on the Word toolbar, and was able delete a comment with just one click from then on. What does the code mean? We can guess that it means "delete the first comment in the selection." Recording a macro to capture a series of actions you find yourself performing repeatedly in Word is simple; all you need to do is record the keystrokes. Remember, the idea is not to save just one or two keystrokes. A copyeditor's working life can be measured in keystrokes, and macros allow more work to be done with fewer keystrokes. They are a labor saving device. It took me just a minute to create the comment-deleting macro, but it saved me a lot of time and physical effort (my wrist is thankful for macros) over the lifetime of the manuscript.

Using the Built-In Word Commands

There's another way to achieve single-click comment deletion, and it doesn't use macros. We tap into the bottomless ocean that is Word's built-in commands. To browse these commands, click the down arrow at the right end of the QAT, and click on "More Commands."

A new window opens, and the popular commands are shown in the left panel.

You can see that "Delete Command" is highlighted. Click Add and OK, and you have added the command to your QAT. All you have to do to delete a command is to click inside the command and click the "Delete Comment" icon in the QAT. Yes, there are many, many more commands under the hood in Word than can be accessed by the user in the provided menu system, and there are worse ways of killing time than idly browsing the list of Word commands. You can pretend you're a geek — and one of the commands you find may be just what you need one day.

Conjuring Up Word's Hidden Capabilities

Let us now step back and take a last look at the forest before immersing ourselves in the trees. Word is a complex piece of software that has powerful capabilities, most of which we will never need. The designers of Word have made available a small subset of these capabilities, the commands they judged to be the most useful features, to users through Word's user interface, the menus. Macros are a tool that we can use to access Word's hidden capabilities by making them available as icons in the QAT (or as keyboard shortcuts). We have also seen that Word has a vast collection of built-in commands that can be linked to icons in the QAT (or to keyboard shortcuts).

Take a look at my QAT below. The icons represent macros, some of which I use daily in my work. I also have keyboard shortcuts for the most heavily used macros.

The icon for the macro I discuss in this post, which converts single quotes to double quotes, is ninth from the left.

Understanding Paul's Macro

Now, back to Paul's macro. Here it is:

Sub SingleT0DoubleQuotesNew()
'
' Courtesy Paul Beverley Feb 2021
'
'
myrange = 1000

Selection.MoveStartUntil cset := ChrW(8216) & "'", Count := wdBackward

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

Selection.Collapse wdCollapseStart

Selection.MoveStart , -1

Selection.TypeText Text := ChrW(8220)

Selection.MoveEndUntil cset := ChrW(8217) & "'", Count := wdForward

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

Selection.Collapse wdCollapseEnd

Selection.MoveEnd , 1

Selection.TypeText Text := ChrW(8221)

End Sub

In my first failed attempt to understand this macro soon after it arrived in February 2021, I had given up when I saw the intimidating second line:

Selection.MoveStartUntil cset := ChrW(8216) & "'", Count := wdBackward

This time, I was determined to get to the bottom of it. I Googled the command, and the first hit was this:

https://docs.microsoft.com/en-us/office/vba/api/word.selection.moveenduntil

It's from the Microsoft documentation, and the explanation is excellent, describing not just the syntax but also simple examples that illustrate how the command is used. I now knew what the

Selection.MoveStartUntil 

command does in the macro, and with that, I got a glimpse of the underlying logic of the macro.

What the Macro Does

It moves backward character by character, looking for a smart (curly) single opening quote (𝙲𝚑𝚛𝚆(𝟾𝟸𝟷𝟼)) or a straight single opening quote ("'"). This backward-looking search is implemented by the following command:

 Selection.MoveStartUntil cset := ChrW(8216) & "'", Count := wdBackward 

Once a single opening quote is found, it is replaced with the opening double quote:

Selection.TypeText Text: = ChrW(8220)

Now the macro moves to the right character by character, looking for a smart (curly) single closing quote (𝙲𝚑𝚛𝚆(𝟾𝟸𝟷𝟽)) or a straight single closing quote ("'"):

Selection.MoveEndUntil cset := ChrW(8217) & "'", Count := wdForward

Once it is found, the single closing quote is replaced with a double closing quote:

Selection.TypeText Text:= ChrW(8221)

And that's basically it.

I confirmed this by tracing the macro from start to finish in the VBA Editor.

Tracing the Macro

To trace a macro is to step into it, executing it command by command, one command at a time. The keyboard shortcut to step into a macro is F8. To execute the next command, hit F8 again. Between the execution of commands, in the suspended state of animation, you can execute commands in the Immediate window. This is useful for debugging, as you can examine the values of various variables to test if the macro is working as it should.

The test file I used is A Tale of Two Cities in Word format, which I downloaded from Project Gutenberg. In the first sentence of the novel, I enclosed "the best of times" in single quotes and placed the cursor to the left of "of," as shown below:

Tracing is done in the VBA Editor, which I entered by hitting Alt-F11. The editor can also be accessed from the Developer menu. I located the macro to be traced,

SingleT0DoubleQuotesNew(), 

and hit F8 to step into the first command of the macro. The VBA interpreter executed the first command, highlighted it in yellow, and then waited for my next instruction.

At this stage, the interpreter has executed the command

 myrange = 1000 

and highlighted the next command. So, I checked the value of the 𝚖𝚢𝚛𝚊𝚗𝚐𝚎 variable by typing

?myrange

in the Immediate window at the bottom of the editor ("?" is the print command). You can see that the value of 𝚖𝚢𝚛𝚊𝚗𝚐𝚎 is printed as 1000, showing that the first command has indeed been executed. What is the purpose of the 𝚖𝚢𝚛𝚊𝚗𝚐𝚎 variable? It stores the maximum number of characters that will be searched in the hunt for single quotes. If this limit (currently 1000) is exceeded, the macro will terminate. This ensures that there will be no infinite loops that may hang Word, which was one of the problems that led me to ask Paul for help in the first place.

I executed two more commands:

The macro has moved beyond the

Selection.MoveStartUntil cset := ChrW(8216) & "'", Count := wdBackward 

command, which means that the opening single quote has been found. I could see this in the document window (see below), which I'd moved to my second monitor to be able to follow the action (if you do not have a second monitor, you can split your monitor window).

The insertion point at which the search commenced has been extended character by character until the opening single quote is found. At this point, the selection is the string "the best ", which I confirmed by entering the command

?selection 

in the Immediate window. The interpreter responded by printing the string "the best ", as shown in the previous screenshot.

I continued to hit F8. On executing the

Selection.Collapse wdCollapseStart 

command, I paused:

The selection, which was a string as seen earlier, has now collapsed to an insertion point. Does the selection exist, now that it has collapsed to an insertion point? Yes, it does. I confirmed this by typing

?selection 

in the Immediate window. You can see the result: "t". The insertion point is also a selection, and returns the character to its right.

At this point, the document window looks like this:

The insertion point lies between the opening single quote and the letter "t".

The next command

Selection.MoveStart , -1

extends the insertion point one character to the left, changing it to a selection that contains one character, the opening quote. The document looks like this:

The next command

 Selection.TypeText Text := ChrW(8220)

replaces the selection (the opening single quote) with the opening double single quote. The next command

Selection.MoveEndUntil cset := ChrW(8217) & "'", Count := wdForward

begins the hunt for the closing single quote, and the movement is now character by character in the forward direction. The editor window looks like this now:

I typed

 ?selection 

in the Immediate window. The string "the best of times" is printed. And in the document window, you can see that this is indeed the case:

The opening single quote was replaced with the opening double quotes earlier; what remains is to replace the closing single quote. This is accomplished by the last three commands of the macro, which are mirror images of the commands used earlier at the other end of the string:

Selection.Collapse wdCollapseEnd

collapses the selection to an insertion point.

Selection.MoveEnd , 1  

extends the insertion point to the right by one character, changing it to a selection that contains one character, the closing quote.

The last command of the macro

Selection.TypeText Text := ChrW(8221)

replaces the closing single quote with a closing double quote.

The First Enhancement

I now understood how the macro worked. This gave me the confidence that I could implement my idea of enhancing the macro by relocating a comma or a period found immediately after the closing quote to a new position inside the quote, in line with US English punctuation. It took a few attempts before I got it working, and I couldn't have done it without tracing — but I finally did it! I wrote to Paul Beverley, and he congratulated me. Here is the modified macro. I needed to add seven lines of code at the end of the macro.

Sub SingleT0DoubleQuotesNew()
'
' Courtesy Paul Beverley Feb 2021
'
'
myrange = 1000

Selection.MoveStartUntil cset := ChrW(8216) & "'", Count := wdBackward

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

Selection.Collapse wdCollapseStart

Selection.MoveStart , -1

Selection.TypeText Text := ChrW(8220)

Selection.MoveEndUntil cset := ChrW(8217) & "'", Count := wdForward

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

Selection.Collapse wdCollapseEnd

Selection.MoveEnd , 1

Selection.TypeText Text := ChrW(8221)

'Following lines added by Santhosh Matthew Paul in Oct 2021
'It deletes a comma or full stop found after the closing quotes and
'inserts it inside the quotes

Selection.MoveEnd , 1

If InStr(".,", Selection) > 0 Then

    comper = Selection.Text

    Selection.Delete

    Selection.MoveEnd , -1

    Selection.TypeText Text := comper
End If

End Sub

The result is that the following punctuation

is turned to this:

I was immediately able to put this macro to use in a long reference list in which article titles were enclosed in single quotes with commas and periods outside the quotes. It was a joy to be able to fix the punctuation with a single click.

The Second Enhancement

One occasional annoyance was that the macro failed when the quoted text contained an apostrophe, for example, 'Alekhine's best games'. I decided to try and fix this. Implementing this second enhancement was much, much harder than implementing the first enhancement. I tried several versions before finally getting it right, and I couldn't have done it without intensive code tracing. It's an invaluable tool.

The challenge was to recognize the apostrophe for what it was and skip it; some fancy footwork was necessary to coordinate the individual steps. So, how does one distinguish between an apostrophe and a single quote mark?

Once a single quote was found, I decided to examine the character to its left; if this character was alphanumeric, then it was an apostrophe. I first thought of checking for a letter of the alphabet, but that might be too restrictive, because some alphanumeric abbreviations can take the possessive case, as in 'G-24's next meeting'. Now, how could I check if a character was alphanumeric in VBA? It turned out that there is no ready-made function for this; code has to be written, and I saw various solutions on the Net. Finally, thanks to the Web page https://wordmvp.com/FAQs/MacrosVBA/CheckifAlphanumeric.htm, I found what appears to be the simplest method, the following command:

If Selection.Text Like "[a-zA-Z0-9]" 

The 𝙻𝚒𝚔𝚎 operator allows pattern matching with wildcards, allowing regular expressions to be used in VBA. Wow! It looks as though this is one smooth operator I'll be relying on in my work on macros from now on.

Here is the doubly enhanced macro:

Sub IntelligentSingleToDoubleQuotes()
'
' Courtesy Paul Beverley Feb 2021
'
'
myrange = 1000

comper = "a"
apos = "N"

Selection.MoveStartUntil cset:=ChrW(8216) & "'" & ChrW(8217), Count:=wdBackward

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

'The following code up to Selection.TypeText added by Santhosh Matthew 
'  Paul Oct 2021
'It checks if the apostrophe is a possessive apostrophe and sets a flag
'A possessive apostrophe is defined as one whose left neighbor (going 
'  backward) is alphanumeric
'or whose right neighbor (going forward) is alphanumeric

Selection.Collapse wdCollapseStart
Selection.MoveStart , -2
Selection.Collapse wdCollapseStart

If Selection.Text Like "[a-zA-Z0-9]" Then
    apos = "Y"
    Selection.MoveStartUntil cset:=ChrW(8216) & "'", Count:=wdBackward
    Selection.Collapse wdCollapseStart
End If

If apos = "N" Then
    Selection.MoveStart , 2
End If
    
Selection.MoveStart , -1

Selection.TypeText Text:=ChrW(8220)

Selection.MoveEndUntil cset:=ChrW(8217) & "'", Count:=wdForward

If apos = "Y" Then
     Selection.Collapse wdCollapseEnd
     Selection.MoveStart , 1
     Selection.MoveEndUntil cset:=ChrW(8217) & "'", Count:=wdForward
End If

'The following If statement added by Santhosh Matthew Paul in Nov 2021
'It takes care of the condition when a possessive apostrophe is present
'and the cursor is to the left of the apostrophe

If apos = "N" Then

    Selection.Collapse wdCollapseEnd
    Selection.MoveEnd , 1
    Selection.Collapse wdCollapseEnd

    If Selection.Text Like "[a-zA-Z0-9]" Then
        apos = "Y"
        Selection.MoveEndUntil cset:=ChrW(8217) & "'", Count:=wdForward
    Else
        Selection.MoveEnd , -1
    End If
End If
     

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

Selection.Collapse wdCollapseEnd

Selection.MoveEnd , 1

Selection.TypeText Text:=ChrW(8221)

'Following lines added by Santhosh Matthew Paul in Oct 2021
'It deletes a comma or full stop found after the closing quotes and
'inserts it inside the quotes

Selection.MoveEnd , 1

If InStr(".,", Selection) > 0 Then

    comper = Selection.Text

    Selection.Delete

    Selection.MoveEnd , -1

    Selection.TypeText Text:=comper
End If

End Sub

The Third Enhancement

This enhancement recognizes the existence of internal double quotes within the outer single quotes, and changes them to single quotes. So, <'Alekhine's "best" games', as> would become <"Alekhine's 'best' games," as>. The outer single quotes have been changed to double quotes, the comma outside the terminal quote has been repositioned inside the quote, and the inner double quotes have been changed to single quotes. The apostrophe is recognized and skipped. And it works for both straight quotes and smart quotes.

To implement the third enhancement, I used the 𝙲𝚘𝚞𝚗𝚝 parameter of the 𝚂𝚎𝚕𝚎𝚌𝚝𝚒𝚘𝚗.𝙼𝚘𝚟𝚎𝚂𝚝𝚊𝚛𝚝𝚄𝚗𝚝𝚒𝚕 command to limit the search for internal quotes to within the string. I also learned that a literal straight double quote is represented in VBA by four successive quotes: """". Again, tracing the code command by command, observing the effect on the target string, and printing the values of variables in the Immediate Window were invaluable for debugging.

I enjoyed the process of enhancing the macros and brushing up my coding skills, which had become rusty from disuse. One application of the macro is for converting UK style to US style. I've also used it for converting reference lists.

To use the macro, copy and paste the following code into your VBA editor. Click anywhere inside text delimited by single quotes and fire up the macro to change the single quotes to double quotes.

Besides, this third enhancement gives the macro something more than intelligence: a third eye!

Here is the triply enhanced macro:

Sub IntelligentSingleToDoubleQuotesExtended()
'
' Courtesy Paul Beverley Feb 2021
'
'Click inside text delimited by single quotes. This macro will change the single quotes to double quotes.

myrange = 1000

comper = "a"
Pspan = 1
Nspan = 1
Subspan = 1
IntQuotes = "N"
apos = "N"
apossublen = 0

Selection.MoveStartUntil cset:=ChrW(8216) & "'" & ChrW(8217), Count:=wdBackward

If Len(Selection) > myrange Then

    Beep

    Exit Sub

End If

'The following code up to Selection.TypeText added by Santhosh Matthew  
' Paul Oct 2021
'It checks if the apostrophe is a possessive apostrophe and sets a flag
'A possessive apostrophe is defined as one whose left neighbor (going   
'  backward) is alphanumeric
'or whose right neighbor (going forward) is alphanumeric

Selection.Collapse wdCollapseStart
Selection.MoveStart , -2
Selection.Collapse wdCollapseStart

If Selection.Text Like "[a-zA-Z0-9]" Then
    apos = "Y"
    Selection.MoveStartUntil cset:=ChrW(8216) & "'", Count:=wdBackward
    Selection.Collapse wdCollapseStart
End If

If apos = "N" Then
    Selection.MoveStart , 2
End If
    
Selection.MoveStart , -1

If Selection = ChrW(8216) Then
   Selection.TypeText Text:=ChrW(8220)
End If
If Selection = "'" Then
    Selection.TypeText Text:=""""
End If



Selection.MoveEndUntil cset:=ChrW(8217) & "'", Count:=wdForward
Pspan = Len(Selection)
Nspan = -Pspan

If apos = "Y" Then
     Selection.Collapse wdCollapseEnd
     Selection.MoveStart , 1
     Selection.MoveEndUntil cset:=ChrW(8217) & "'", Count:=wdForward
     apossublen = Len(Selection)
     Nspan = Nspan - apossublen
End If

'The following If statement added by Santhosh Matthew Paul in Nov 2021
'It takes care of the condition when a possessive apostrophe is present
'and the cursor is to the left of the apostrophe

If apos = "N" Then

    Selection.Collapse wdCollapseEnd
    Selection.MoveEnd , 1
    Selection.Collapse wdCollapseEnd

    If Selection.Text Like "[a-zA-Z0-9]" Then
        apos = "Y"
        Selection.MoveEndUntil cset:=ChrW(8217) & "'", Count:=wdForward
    Else
        Selection.MoveEnd , -1
    End If
End If
     

If Len(Selection) > myrange Then

 Beep

 Exit Sub

End If

Selection.Collapse wdCollapseEnd

Selection.MoveEnd , 1


If Selection = ChrW(8217) Then
   Selection.TypeText Text:=ChrW(8221)
End If
If Selection = "'" Then
    Selection.TypeText Text:=""""
End If

'Following lines added by Santhosh Matthew Paul in Oct 2021
'It deletes a comma or full stop found after the closing quotes and
'inserts it inside the quotes

Selection.MoveEnd , 1

If InStr(".,", Selection) > 0 Then

    comper = Selection.Text

    Selection.Delete

    Selection.MoveEnd , -1

    Selection.TypeText Text:=comper
End If

'The following lines added by Santhosh Matthew Paul in November 2021
'It checks for an internal pair of double quotes and changes them to 
' single quotes


Selection.MoveEnd , -1
Selection.Collapse wdCollapseEnd
'Selection.MoveStartUntil cset:=ChrW(8220), Count:=Nspan
Selection.MoveStartUntil cset:=ChrW(8221) & """", Count:=Nspan 'restricts ' the search for opening quotes to within the string
Subspan = Len(Selection)
If Subspan > 1 Then
    IntQuotes = "Y"
End If
If IntQuotes = "Y" Then
    Selection.Collapse wdCollapseStart
    Selection.MoveStart , -1
    
    
    If Selection = ChrW(8221) Then
        Selection.TypeText Text:=ChrW(8217)
     End If
    If Selection = """" Then
        Selection.TypeText Text:="'"
    End If
    
    Selection.MoveStartUntil cset:=ChrW(8220) & """", Count:=Nspan + Subspan 'restricts the search for closing quotes to within the string
    Selection.Collapse wdCollapseStart
    Selection.MoveStart , -1
    
    If Selection = ChrW(8220) Then
        Selection.TypeText Text:=ChrW(8216)
     End If
    If Selection = """" Then
        Selection.TypeText Text:="'"
    End If
End If
End Sub