Structured Table Referencing and Double Brackets in Column Headers
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:
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.
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.