Single Quotes in Worksheet Names

Single Quotes in Worksheet Names

I was working on a function that uses regular expressions to determine whether a potential name for a workbook, worksheet or range contains illegal characters. I started by writing a little routine to determine which characters are illegal for sheet names. Of course, I could have just used one that I knew was prohibited and got the message below. But then I might never have thought about the use of single quotes in worksheet names.

Illegal worksheet character message

Here’s the code I used:

Sub IllegalWsNameCharacters()
Dim i As Long

With ActiveSheet
    .Range("A1").Cells.ClearContents
    For i = 0 To 127
        On Error Resume Next
        .Name = Chr(i)
        If Err.Number <> 0 Then
            .Range("A1").Value = .Range("A1").Value & Chr(i)
        End If
        On Error GoTo 0
    Next i
End With
End Sub

It cycles through each of the 128 ASCII characters and tries to use it as the ActiveSheet’s name. An error means that character is illegal, at least by itself, so it’s added to the contents of cell A1. I don’t know why I printed to a cell instead of the Immediate window, but it kept me from immediately (ha!) noticing that the first illegal character was a single quote or, as normal people say, an apostrophe.

Single quote in cell

You can see it doesn’t show up in the cell contents, only in the formula box. That’s because the single quote is the escape character that tells Excel that whatever follows it is text, and part of its duty is to stay quietly out of sight.

At any rate, I saw it and was surprised at it’s illegality. I’m pretty sure I’ve used an apostrophe in a sheet name before. And, sure enough you can:

Name with single quote

What you can’t do is use it as the first or last character in a sheet name. And this makes sense, since single quotes are how Excel surrounds worksheet and workbook names that have spaces in them, as in:

='A spaced out name'!A1 * serious_wks_name!A1

All in all, it seems you are best off leaving single quotes out of your sheet names. A quick web search reveals issues with hyperlinks, OLEDB references and Excel Services REST, whatever that is.

Finally, and interestingly, you can name your sheets with some of the “nonprintable” ASCII character codes, i.e., characters 0 to 31, if only through VBA:

ActiveSheet.Name = Chr(8) & " " & Chr(12) & " " & Chr(17) & " " & Chr(15) & " " & Chr(14)

Mind you, I’m not recommending it.

Worksheet name with nonprintable characters

3 thoughts on “Single Quotes in Worksheet Names

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>