Excel Tables - and how useful they are

Today we will talk about a topic that is useful for both VBA and Excel. The tables are one of the most useful Excel features and, we have noticed, are sometimes left aside by Excel users. Believing that the reason for that is the lack of real understanding of the potential of the tables, specially regarding scalability, we are writing this article to show their importance and how to use them.

Table is an Excel object that provide more tools to work on Excel ranges. The typical layout of an Excel table is shown in the Image 01 below.

Name	Age	Company
John	30	ABC
Peter	40	XYZ
Mary	24	XYZ
Frank	56	XYZ
Mark	34	ABC

Image 01

When a range is transformed into a table, it is automatically formated so it becomes similar to the Image 01. This formatting can be easily changed, but the layout in Image 01 is the most common.

Once a table like in Image 01 is created, some very useful tools become available. You can easily sort the table, filter it, show totals at the bottom of it (total count/total sum etc.). All this and other features are explained in many topics that are spread around the web, but those are not the foccus of this article. The foccus of this article is scalability.

Let's suppose we have the very same data of Image 01, but in the form of ranges. See Image 02

Name	Age	Company
John	30	ABC
Peter	40	XYZ
Mary	24	XYZ
Frank	56	XYZ
Mark	34	ABC
Paul	56	ABC

Image 02
And lets supose we have a code to add up the ages (we know this is something that could be easily done by the =SUM() function, but this is just an example). Let's see how the code could be:Image 02

Option Explicit

Public Sub Sum_Ages()
    
    Dim i As Long
    Dim aux As Double
    
    For i = 2 To 6
        aux = aux + int(Range("B" & i).Value2)
    Next i
    MsgBox aux
    
    Exit Sub
    
End Sub

This is not a really terrible code. It does work and it does solve the problem. There are many other ways to solve this problem, but this code is as good (or as bad) as any other that do not use tables.
If the reader has not seen the issue yet, let's imagine that a new row is created. Let's say that the ranges is now like in the image 03 below.

Name	Age	Company
John	30	ABC
Peter	40	XYZ
Mary	24	XYZ
Frank	56	XYZ
Mark	34	ABC
Paul	56	ABC

Image 03

As it can be seen, a new row, was added. In that case, would the code above still give us the answer we want? The answer is NO. The code adds only the ages from row 2 to 6, so the new line, the 7th, would be left out. To solve this one would have to rewrite the code to get it - something we are keen to avoid. So what would be the solution for this problem? The answer: tables.

Let's now convert this very range into a table and name it "tbl_employee" (to name the table, after you convert the range, you should go to "Design" and then "Table Name" and put the name you want).

Now we's have a table like in the image 04.

Name	Age	Company
John	30	ABC
Peter	40	XYZ
Mary	24	XYZ
Frank	56	XYZ
Mark	34	ABC
Paul	56	ABC

Image 04

Again, the name of the table is "tbl_employee". With that in mind, let's write a new code that does the same of the previous one.

Option Explicit

Public Sub Sum_Ages2()
    
    Dim myTable As Range
    Dim m As Long
    Dim i As Long
    Dim aux As Double
    
    Set myTable = Range("tbl_employee")
    m = myTable.Rows.Count
    
    For i = 1 To m
        aux = aux + int(myTable(i, 2).Value2)
    Next i
    MsgBox aux
    
    Exit Sub
    
End Sub

The difference between this code and the previous one is that now we do not fix the number "6" as the number of rows. Instead, we set the range object as the table we have just created and ask for the number of rows of it. So for as many rows as the table has, we are certain to get the right value. We have just made our table scalable vertically. The same worths, though, for horizontal scalability, since a new column could be easily captured by the ".Columns.Count" method of the range object.

As you can see, all we had to do was to convert the range into a table, name it and then use it in our code as a normal range. The outcome, though, was very different from not using a table, since we couldn't get the updated number of rows or columns that easily.

It is important to notice that the tables automatically understand new rows and new columns. A new value right below the last row is immediately understood as a new row. This makes adding new content to the tables extremely easy and, once again, all the objects related to the table are also automatically updated.

Now, again, this is a very VBA related subject. But using tables has many benefits that go beyond VBA. Once the range is converted into table, any object that references it will also become scalable by definition, since the references maps not the cells addresses (which are absolute and fixed) but the table's relative addresses, which are not fixed and are dynamic. Graphics, formulas and VBA are made scalable when using tables.

Bottom line: there is no reason for not to use tables instead of regular ranges. It is a powerful tool Excel provides and it should be used to make our projects more robust and easy to develop.