If you use certain characters in the column names of your table, then you have to use double brackets around those names when you refer to them in formulas. I don’t care for that, so I don’t use those characters. I was converting some imported data into a table and, of course, it had spaces in the column names (and a few other naughty characters). I needed to clean up those characters before converting to a table. The formula below shows what the double brackets look like.

This office web page provides a list of characters that cause this behavior. I don’t know where they got that list, but I did notice that there was no underscore on it. I like to use underscores to separate words, but I can’t use them in tables because of the double bracket thing. I figured I’d better make my own list if I’m going to clean up data.

I wrote this code to list out all of the bad characters:

It loops through all the characters in the basic ASCII character set, inserts the character into a the column header, and reads the formula that references that column. I check for

or an apostrophe. It turns out that all the characters you have to escape with an apostrophe also cause double brackets, so I only needed to check for the double brackets.

With my list, I created a function to clean the data before I use it as a column header. I don’t check for Chr(13) because I don’t think you can have that in a cell.

Now, to make sure my Excel workbooks are utterly un-editable by anyone else, I’m going to write an event handler that converts all my underscores to something else as I type them. The only question, is should I use an elipsis (chr$(133))

or a macron (chr$(175))

That’s a tough one.