Do you want to be able to rename your Excel tables and columns without breaking existing PowerQuery queries? Do you always change the default name of a new table? Want to have quick navigation to your tables in your workbook?
Use my free TableTools add-in
Enjoy!
Jan Karel Pieterse
jkp-ads.com
Hi Excel lovers,
Wanted to draw your attention to our fifth
June 7 and 8 , 2018
We’ve worked hard to get an exciting line-up of speakers presenting on a wide variety of Excel subjects during our two-day conference.
So if you’re an Excel power-user, this is one of those one-of-a-kind conferences you cannot afford to miss!
Hope to see you in Amsterdam on June 7 and 8 2018!
Jan Karel Pieterse, Tony de Jonker
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
|
1 |
=[@[ |
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))
|
1 |
One…Two |
or a macron (chr$(175))
|
1 |
One¯Two |
That’s a tough one.
Way back when, I wrote a post about naming columns that turned into a post about VLOOKUPs and MATCHes using structured table references. As usual, there was gold in them there comments. Since then I’ve been using the COLUMN() function in my VLOOKUP formulas to identify the return column. Like this:
|
1 |
=VLOOKUP(D7,tblData,COLUMN(tblData[Name]),FALSE) |
This has the restriction that your table start in A1. Mine do. One table per worksheet and it starts in A1. This has been working well for me, but I’m sick of typing the COLUMN part. So I made a macro and here it is.
|
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 29 30 31 32 33 34 35 36 37 |
Private Sub mxlApp_SheetChange(ByVal sh As Object, ByVal Target As Range) Dim vaSplit As Variant If Target.CountLarge = 1 Then If Target.HasFormula Then Application.EnableEvents = False If IsTableVlookup(Target.Formula) Then vaSplit = Split(Target.Formula, ",") vaSplit(2) = "COLUMN(" & vaSplit(1) & "[" & vaSplit(2) & "])" Target.Formula = Join(vaSplit, ",") End If Application.EnableEvents = True End If End If End Sub Private Function IsTableVlookup(ByVal sFormula As String) As Boolean Dim vaSplit As Variant Dim bReturn As Boolean Const sVL As String = "=VLOOKUP" Const sTBL As String = "tbl" On Error Resume Next vaSplit = Split(sFormula, ",") bReturn = Left$(sFormula, Len(sVL)) = sVL bReturn = bReturn And Left$(vaSplit(1), Len(sTBL)) = sTBL bReturn = bReturn And (vaSplit(3) = "TRUE)" Or vaSplit(3) = "FALSE)") bReturn = bReturn And InStr(1, vaSplit(2), "COLUMN(") = 0 IsTableVlookup = bReturn End Function |
The IsTableVlookup function needs a little help. I just kept adding conditions as I tested it (and broke it), but I should probably just rethink the whole thing. Or maybe not, we’ll see. With this little gem in my custom class that handles application events, I can type
|
1 |
=VLOOKUP(D7,tblData,name,FALSE) |
and it will insert the COLUMN part for me. I could even modify it for tables that don’t start in A1 by subtracting the COLUMN of the first column. The function also assumes you prefix all of your tables with “tbl”. You can change to suit. If you don’t use a naming convention, then I guess you’ll have to loop through all the tables and see if it matches one.
In an effort to be more like Debra, I made a video. As with every video I record with CamStudio, the first few seconds are blurry. I don’t know why.
I was reading Contextures post about duplicate entries. In it, she creates a named range that refers to a Table column. That named range will expand and contract as the Table does.
Tables have their own built-in names and they are so handy that I find myself using Tables whenever possible. Here’s me using the table name and an open bracket to get a list of columns while entering a formula.
You may have guessed that I prefix all of my Tables with “tbl”. I also use the singular noun, so it’s tblCustomer and not tblCustomers. One of my favorite uses of Table references is using them in VLOOKUPS. The third argument to VLOOKUP is the column from which to pull. I’ve never been a fan of making that dynamic. Instead, I would hard code the number and change it if the columns change. But with Table references, I find myself making that argument dynamic. It’s a little less error prone, a lot more self-documenting, and while it’s more typing, I don’t have to count columns. An old VLOOKUP of mine might look like
=VLOOKUP(G1133,$A$1:$J$331,2,FALSE)
And now it looks like this
=VLOOKUP(G1133,tblCustomer,MATCH("Carrier",tblCustomer[#Headers],FALSE),FALSE)
Instead of hard coding “2” for the column, I’m hard coding “Carrier”, which is the column name. Obviously that could change, but it will give me an #N/A! rather than pulling wrong information.
The autocomplete feature of the table references makes this easy to enter. I can type “tbl” and get a list of all the tables in my workbook.
Then I can hit the tab key to finish the job. Next, the open bracket gives me all the elements in the table, including #Headers.
But this was supposed to be about naming columns and I got off on this VLOOKUP thing. One of the limitations of Table references is that you can’t use them directly in data validation and conditional formatting. The way around that, as Contextures employs, is to create a named range that refers to the column. I use a convention when naming Table columns.
The name is prefixed with “dv” (for data validation). Then the table name without the “tbl” prefix. Then the column name. The big advantage to me to using this convention is that it’s easier to type the named range in the Data Validation box. There’s no intellisense in the Data Validation box, so you have to remember the named range exactly. Also, when you’re entering a formula in the formula bar, it’s nice to type “dv” and see all of the named ranges that start with that. Or to type “dvCustomer” to see all of the columns in that table that have a named range.
One of the downsides is that the named range can get long. If you have tblCustomer that has a CustomerName column, the named range becomes dvCustomerCustomerName. I think the benefits outweigh the costs.
Leave a comment with your conventions for naming ranges.
