What are ListObjects in VBA?
In a table normally what we see is a data set but in VBA terminology there are much more such as there is range of the total data list range, the column is known as the list column and row is known as the list row and so on, so in order to access this properties we have an inbuilt function known as Listobjects and which is used with the worksheet function.
VBA ListObject is a way of referring to the excel tables while writing the VBA code. By using VBA LISTOBJECTS we can create, delete table, and totally we can play around with excel tables in VBA code. Excel Tables are tricky, beginners, and even to an extent intermediate level users find it difficult to work with tables. Since this article talks about referencing excel tables in VBA coding it is better you have good knowledge about tables in excel.
When the data is converted to tables we no longer work with a range of cells rather we need to work with table ranges, so in this article, we will show you how to work with excel tables to write VBA codes efficiently.
Create Table Format Using ListObjects in Excel VBA
For example, look at the below excel data.
Using VBA ListObject code we will create a table format for this data.
- For this data first we need to find what is the last used row & column, so define two variables to find this.
Sub List_Objects_Example1() Dim LR As Long Dim LC As Long End Sub
- To find the last used row and column use the below code.
LR = Cells(Rows.Count, 1).End(xlUp).Row LC = Cells(1, Columns.Count).End(xlToLeft).Column
- Now define one more variable to hold the reference of the data.
Dim Rng As Range
- Now set the reference to this variable by using the below code.
Set Rng = Cells(1, 1).Resize(LR, LC)
Now we need to use VBA “ListObject.Add” method to create a table and below is the syntax of the same.
ListObject.Add (Source, XlListObjectHasHeaders, Destination, TableStyleName)
Source: This is nothing for which range of cells we are inserting the table. So we can supply two arguments here i.e. “xlSrcRange” and “xlSrcExternal”.
XlListObjectHasHeaders: If the table inserting data has headers or not. If yes we can provide “xlYes” if not we can provide “xlNo”.
Destination: This is nothing but our data range.
Table Style: If you want to apply any table style we can provide styles.
- Ok, now in the active sheet we are creating the table, so below code would create a table for us.
Dim Ws As Worksheet Set Ws = ActiveSheet Ws.ListObjects.Add xlSrcRange, xllistobjecthasheaders:=xlYes, Destination:=Rng
- After this, we need to give a name to this table.
Ws.ListObjects(1).name = "EmpTable"
- Below is the full code for your reference.
Sub List_Objects_Example1() Dim LR As Long Dim LC As Long LR = Cells(Rows.Count, 1).End(xlUp).Row LC = Cells(1, Columns.Count).End(xlToLeft).Column Dim Rng As Range Set Rng = Cells(1, 1).Resize(LR, LC) Dim Ws As Worksheet Set Ws = ActiveSheet Ws.ListObjects.Add xlSrcRange, xllistobjecthasheaders:=xlYes, Destination:=Rng Ws.ListObjects(1).name = "EmpTable" End Sub
Ok, let’s run the code and see the magic.
It has created the table to the mentioned data and given the table name as “EmpTable”.
Formatting Excel Tables with VBA ListObjects
Once the Excel table has been created we can work with tables by using vba ListObject collection.
- First, define the variable as “ListObject”.
Sub List_Objects_Example2() Dim MyTable As ListObject End Sub
- Now set the reference to this variable by using the table name.
Sub List_Objects_Example2() Dim MyTable As ListObject Set MyTable = ActiveSheet.ListObjects("EmpTable") End Sub
Now the variable “MyTable” holds the reference for the table “EmpTable”.
- Enter the variable name and put a dot to see the properties and methods of the VBA ListObject.
For example, if we want to select the entire table then we need to use the “Range” object and under this, we need to use the “Select” method.
This would select the entire data table including the heading.
- If you want to select only the contents of the table without headers then we need to use “DataBodyRange”.
Like this, we can play around with tables.
- Below is the list of activity codes for your reference.
Sub List_Objects_Example2() Dim MyTable As ListObject Set MyTable = ActiveSheet.ListObjects("EmpTable") MyTable.DataBodyRange.Select 'To Select data range without headers MyTable.Range.Select 'To Select data range with headers MyTable.HeaderRowRange.Select 'To Select table header rows MyTable.ListColumns(2).Range.Select 'To select column 2 including header MyTable.ListColumns(2).DataBodyRange.Select 'To select column 2 without header End Sub
Like this, we can use the “ListObject” collection to play around with excel tables.
Things to Remember
- VBA ListObject is the collection of objects to reference excel tables.
- To access ListObject collection first we need to specify what worksheet we are referring to is.
This has been a guide to VBA ListObject. Here we discuss how to use VBA ListObject.Add method to create a table in excel using examples downloadable excel sheet. You can learn more from the following VBA articles –