Creating Tables the Right Way

Posted on February 24, 2015 by Dick Kusleika

And by ‘right way’ I mean the way I want. JKP commented:

I wish MSFT would put a tablename box right into the “Format as Table” dialog as it is the first thing I do after formatting a range as a table. O, and always put that checkbox on. My tables always have a header row.

I couldn’t agree more. So why not repurpose Ctrl+T to do what I want.

Sub MakeTable()
    
    Dim sh As Worksheet
    Dim sName As String
    Dim lo As ListObject, loExists As ListObject
    
    Const sSHEETSTART As String = "Sheet"
    Const sTABLESTART As String = "tbl"
    
    Set sh = ActiveSheet
    
    'Get the name of the table from the user
    sName = Application.InputBox("Enter the table name", "Table Name")
    
    'If the user didn't click Cancel
    If sName <> "False" Then
        'Start the table with 'tbl' if it doesn't already
        If Left$(sName, Len(sTABLESTART)) <> sTABLESTART Then
            sName = sTABLESTART & sName
        End If
        
        'Create the table and name it
        Set lo = sh.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes)
                
        'See if that name exists on this sheet
        On Error Resume Next
            Set loExists = sh.ListObjects(sName)
        On Error GoTo 0
        
        'If the name doesn't exist
        If loExists Is Nothing Then
            lo.Name = sName
            
            'If the sheet isn't already specifically named, name it
            If Left$(sh.Name, Len(sSHEETSTART)) = sSHEETSTART Then
                On Error Resume Next
                sh.Name = Replace$(lo.DisplayName, "tbl", vbNullString)
            End If
        End If
    End If

End Sub

Sub MakeTable()

Dim sh As Worksheet

Dim sName As String

Dim lo As ListObject, loExists As ListObject

Const sSHEETSTART As String = "Sheet"

Const sTABLESTART As String = "tbl"

Set sh = ActiveSheet

'Get the name of the table from the user

sName = Application.InputBox("Enter the table name", "Table Name")

'If the user didn't click Cancel

If sName <> "False" Then

'Start the table with 'tbl' if it doesn't already

If Left$(sName, Len(sTABLESTART)) <> sTABLESTART Then

sName = sTABLESTART & sName

End If

'Create the table and name it

Set lo = sh.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes)

'See if that name exists on this sheet

On Error Resume Next

Set loExists = sh.ListObjects(sName)

On Error GoTo 0

'If the name doesn't exist

If loExists Is Nothing Then

lo.Name = sName

'If the sheet isn't already specifically named, name it

If Left$(sh.Name, Len(sSHEETSTART)) = sSHEETSTART Then

On Error Resume Next

sh.Name = Replace$(lo.DisplayName, "tbl", vbNullString)

End If

End Sub

This code makes a lot of assumptions about how I work with tables, so it may not work for you. First, I ask for the table name. I start all my table names with tbl, so if I don’t include that the code includes it for me. Next, I create a new ListObject based on the CurrentRegion of the ActiveCell. This is different than what Excel does. If you only have one cell selected, Excel will use the CurrentRegion. If you have more than one cell selected, Excel assumes you’ve defined the range you want and uses that. I put one table on one sheet and it’s the only thing on there. Therefore, I always want everything on that sheet to be the table.

Next, I see if a table with that name already exists on the sheet. If it does, skip the whole naming part.

Finally, I change the name of the sheet if it’s still named the generic ‘Sheetx’. I drop the ‘tbl’ part from the DisplayName property and name the sheet. The error avoidance is in case there’s already a sheet with that name. In that case, the name remains unchanged.

Why the DisplayName? If you name a table

tblList

tblList

, you can’t name another table

tblList

tblList

on the same sheet. In fact, in the user interface you can’t name another table

tblList

tblList

in the whole workbook. But in code, you can name another table

tblList

tblList

as long as it’s on a different sheet. If that name already exists, ListObject.Name remains

tblList

tblList

, but ListObject.DisplayName is changed to

tblList_1

tblList_1

. That’s why I check for the existence of that table on the same sheet but not the whole workbook. And that’s why I use the DisplayName to name the sheet.

I should have skipped all this error checking and just put a big

On Error Resume Next

1	On Error Resume Next

at the top. I probably will never have two tables with the same name, and if I accidentally did, it would just keep the default name.

29 thoughts on “Creating Tables the Right Way”

Jeff Weir says:

February 24, 2015 at 8:24 pm

One thing I hate about Tables is that Excel isn’t programmed smartly enough to handle what happens if you have some unrelated text sitting above your nicely formed table. Will post a screenshot in a second. I was going to change your code to handle this situation, but I don’t have time at the present moment.

Here’s that screenshot:

Sub M_snb()
   If ActiveSheet.ListObjects.Count = 0 Then
        c00 = InputBox("Table Name", "snb")
        
        If c00 <> "" And ActiveCell.Address <> ActiveCell.CurrentRegion.Address Then
            With ActiveSheet
              .Name = c00
              .ListObjects.Add(1, , , 1).Name = "T_" & c00
            End With
        End If
   End If
End Sub

Sub M_snb()

If ActiveSheet.ListObjects.Count = 0 Then

c00 = InputBox("Table Name", "snb")

If c00 <> "" And ActiveCell.Address <> ActiveCell.CurrentRegion.Address Then

With ActiveSheet

.Name = c00

.ListObjects.Add(1, , , 1).Name = "T_" & c00

End With

End If

End Sub

I just ran into a situation where I didn’t want CurrentRegion. I imported a text file, cleaned up the data, and prepared to make a table. But the CurrentRegion extends out where old data once existed, but doesn’t any more. So I guess I need to account for selecting the table.

'Create the table and name it
If Selection.Cells.Count = 1 Then
    Set rTable = ActiveCell.CurrentRegion
Else
    Set rTable = Selection
End If
Set lo = sh.ListObjects.Add(xlSrcRange, rTable, , xlYes)

'Create the table and name it

If Selection.Cells.Count = 1 Then

Set rTable = ActiveCell.CurrentRegion

Else

Set rTable = Selection

End If

Set lo = sh.ListObjects.Add(xlSrcRange, rTable, , xlYes)

Jan Karel Pieterse says:

February 25, 2015 at 2:10 pm

Dick:

Visual Basic

If sName <> "False" Then

1

If sName <> "False" Then

fails for non-English Excel versions as sName will contain a translated string (Onwaar” in Dutch). Better is to change sName to a variant and then test type:

Visual Basic

If Typename(sName) <> "Boolean" Then

1

If Typename(sName) <> "Boolean" Then

That’s where (VBA.)inputbox, instead of appclication.inputbox comes in:
Click on ‘Cancel’ in both cases.

Sub M_snb()
   c01 = Application.InputBox("first", "last")
   magbox TypeName(c01)

   c02 = InputBox("first", "last")
   MsgBox TypeName(c02)
End Sub

Sub M_snb()

c01 = Application.InputBox("first", "last")

magbox TypeName(c01)

c02 = InputBox("first", "last")

MsgBox TypeName(c02)

End Sub

Mike says:

February 26, 2015 at 2:42 am

Great approach. Is someone willing to manipulate the code a bit so it would add also a name range for the table? I use it a lot for dashboarding.
Gruff999 says:

February 26, 2015 at 4:40 am

Are “0” and “1” internationally recognised?

Visual Basic

If sName = 0 Then

1

If sName = 0 Then
Dick Kusleika says:

February 26, 2015 at 7:42 am

I used InputBox originally and then switched to Application.InputBox because InputBox doesn’t return False. But I don’t see any downside to checking Len(sName), so I should have stuck with InputBox.
Dick Kusleika says:

February 26, 2015 at 7:42 am

Mike: What kind of name do you need that isn’t provided automatically by the table?
Mike says:

February 26, 2015 at 7:48 am

Dick: I use range names to use in Data Validation and formulas, so when I create a table and name it I also select the range of data and name it.
Jeff Weir says:

February 26, 2015 at 1:43 pm

Mike – Tables are essentially a collection of named ranges that Excel maintains automatically on your behalf, and so you can already refer to them in your formulas. Granted, the Data Validation dropdown doesn’t accept Table Names, but if you just point your DV list at the cell range where say a table column is, then your list will automatically update if that list grows or shrinks. So I’m not sure why you need to assign names to Tables. Can you elaborate?

About the only time I add Range Names to Tables is when I want to manipulate them via VBA, so that if the user (or me) changes the name of a table element (e.g. changes the column header) then the code will still work.

Here’s some content from a book I’m writing that illustrates the Dynamic Named Range aspect of Tables. Note that in the below screenshots I’m NOT using Table Names in my formula but just plain cell references…I introduce the concept of Table Names the next page over:

Here’s a table that has some conditional formatting on the Total column, as well as some other things that point at it: a formula, a data validation dropdown, and a chart.

At the moment, the table above has got our weekly food budget in it: Vege, Fruit, and Meat. Hardly a balanced diet. Watch what happens when we add a fourth row to that table, to hold an important staple we left out:

Wow, will you take a look at that: the Table expanded like a stomach to automatically hold our new category of ‘Beer’. But even better, that conditional formatting got copied down to the new row, and the formula, chart, and dropdown that reference our table all got updated automatically, before we could say ‘Prost’. Spooky! So use a table, and you never need repoint formulas, conditional formatting, charts, and dropdowns again!
snb says:

February 26, 2015 at 2:14 pm

Adding 2/3 of your budget to spend on beer doesn’t result in a more balanced diet I’d say…
Jeff Weir says:

February 26, 2015 at 2:53 pm

You’re right snb. So I prefer to think of it as spending under 40% of total budget on beer, as opposed to increasing the budget by 2/3rds.
Jeff Weir says:

February 26, 2015 at 2:56 pm

…and as you can see by the size of the graph, it’s just a small budget anyway. ;-)
snb says:

February 26, 2015 at 3:40 pm

You must be living in a thorny thirsty country….

Can’t decipher whether it’s a daily, weekly, monthly or elsely budget.
Mike says:

February 26, 2015 at 3:59 pm

Dick: Interesting, I didn’t know that. Thanks for the info!
Jeff Weir says:

February 26, 2015 at 4:01 pm

Dude, that’s my breakfast budget. You should see the lunch one…
stevej says:

February 27, 2015 at 4:33 pm

For the very simple case of importing text data and then formatting that as a table I have a simple keyboard shortcut hack. Assuming the ActiveCell is the Top-Left cell of the table, do a Ctrl-B before the Ctrl-T. It seems the Insert-Table dialog will think the first row is a header if it detects some bold formatting there, even in just one cell.
Mike says:

February 28, 2015 at 4:15 am

Hi again, Dick

Although is true Data Validation can be used just by pointing the reference where the table is, I’ve noticed it’s a bit annoying to do so every time I want to use a DV list, that’s why a name is useful, then you only have to write it in the formula dialog.
Jeff Weir says:

February 28, 2015 at 4:43 am

Mike: good point. Especially given that you can push F3 to bring up the Paste Name dialog box, meaning you don’t even have to type that name in there, let alone point to a cell reference.
Mike says:

March 1, 2015 at 12:25 pm

This is the code to add a name:

Visual Basic

ActiveWorkbook.Names.Add Name:="Name", RefersTo:="Sheets("Sheet1").ListObjects("tblname").Range.Select"

1

ActiveWorkbook.Names.Add Name:="Name", RefersTo:="Sheets("Sheet1").ListObjects("tblname").Range.Select"

I guess this must not be very difficult to implement in the existing code, I’ll try it.
Mike says:

March 1, 2015 at 12:55 pm

Ok, I think I’ve got it. The code I pasted was wrong, but I was able to get this to add a name just for the data range of a table:

Visual Basic

Sub AddNameToTableTest() Dim lo As ListObject Set lo = Sheets("test").ListObjects("tbltest") lo.DataBodyRange.Name = "myData" End Sub

1
2
3
4
5
6
7
8

Sub AddNameToTableTest()

Dim lo As ListObject
Set lo = Sheets("test").ListObjects("tbltest")

    lo.DataBodyRange.Name = "myData"

End Sub

So, adding this little bit of code after the last End if of Dick’s code, seems to work:

Visual Basic

rngname = Application.InputBox("Enter the range name", "Range Name") lo.DataBodyRange.Name = "rngname"

1
2
3

rngname = Application.InputBox("Enter the range name", "Range Name")

    lo.DataBodyRange.Name = "rngname"
Mike says:

March 1, 2015 at 12:58 pm

Delete the “” from rngname, though
snb says:

March 1, 2015 at 3:28 pm

@Mike

You probably overlooked my first reply in this thread.
Mike says:

March 1, 2015 at 4:30 pm

@snb

True. Sorry, does it do the same? I’m still learning the basics and some coding it’s still obscure for me.
snb says:

March 1, 2015 at 5:09 pm

Why not giving it a try ?
Mike says:

March 1, 2015 at 5:16 pm

@sub

I’ll do :) Where I am is now time to sleep. Night!
Mike says:

March 2, 2015 at 2:05 am

@sub

Your macro creates a table name, not a range name.
Jeff Weir says:

March 2, 2015 at 2:18 am

I just found a handy routine over at Jan Karel’s blog – Sub RemoveFormattingOfTable() – that gets rid of ‘historical’ formatting by applying an amended Normal style to the cells concerned. Maybe something worth adding to the re-purposed Ctrl T shortcut?

http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp