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:
|
|
Public Sub TestColHeaders() Dim i As Long For i = 1 To 255 Range("G8").Value = "One" & Chr$(i) & "Two" If Left(Range("h9").Formula, 4) = "=[@[" Or InStr(1, Range("h9").Formula, "'") > 0 Then Debug.Print i, Range("h9").Formula End If Next i End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Public Function CleanTableColumnHeader(ByVal sHeader As String) As String Dim i As Long Dim sReturn As String sReturn = sHeader For i = 32 To 47 sReturn = Replace$(sReturn, Chr$(i), vbNullString) Next i For i = 58 To 64 sReturn = Replace$(sReturn, Chr$(i), vbNullString) Next i For i = 91 To 96 sReturn = Replace$(sReturn, Chr$(i), vbNullString) Next i sReturn = Replace$(sReturn, Chr$(123), vbNullString) sReturn = Replace$(sReturn, Chr$(125), vbNullString) sReturn = Replace$(sReturn, Chr$(126), vbNullString) sReturn = Replace$(sReturn, Chr$(9), vbNullString) sReturn = Replace$(sReturn, Chr$(10), vbNullString) CleanTableColumnHeader = sReturn End Function |
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.
