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.

Here’s the code I used:

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.

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:

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:

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:

Mind you, I’m not recommending it.