VBA LISTOBJECTS

What are ListObjects in VBA?

In VBA (Visual Basic for Applications), a ListObject is a feature introduced in Excel that represents a table. Tables are a powerful way to manage and analyze data in Excel, and ListObject provides a programmatic way to work with tables through VBA code.

• Definition: A ListObject is essentially a range that has been converted into a table. It has a structured format, with columns having names and the ability to use structured references in formulas.

• Creating a ListObject: You can create a ListObject by selecting a range of data in Excel and then converting it to a table. You can use VBA ListObjects.Add querytable code to create a ListObject and specify the range.

Consider a set of values in Excel.

Convert these values into a VBA ListObjects table with the VBA code below.

Define a subroutine to create a table in Excel using the VBA ListObjects.Add function. Here, you can also name the table in the function, as shown, and the table is generated.

Thus, a VBA ListObjects table is formed successfully.

How To Create Table Format Using ListObjects in Excel VBA?

Here, we learn how to create a table format using the VBA ListObjects table using VBA. Previously, you saw how to convert already existing data into a table.

How to Format Excel Tables with VBA ListObjects?

Learn how to use VBA ListObjects to format Excel tables by following the steps below.

• Step 1: Open the Excel Workbook, and in the title bar, select the “Developer tab” option.

Select “Visual Basic”. It will open the VBA Editor, which will be empty. In the Editor, select “Insert” on the VBA toolbar.

Select “Module” in the drop-down.

• Step 2: Consider a set of values given below.

Use “Ctrl+T” to convert it into a table.

• Step 3: Define a subroutine to format Excel tables with VBA ListObjects AutoFilter.

• Step 4: Define the table and filter the table based on the third column where only the values whose result is false can be seen. The xlAnd operator is also used.

Using the VBA ListObjects.AutoFilter function, only the values with the “FALSE” result will be shown.

• Step 5: Define another subroutine to remove all the active filters.

• Step 6: Select the Header which has been filtered. Here, the column with the header “S” has been filtered.

• Step 7: Check using an If conditional statement whether the FilterMode property of the ListObject returns “TRUE.”

If it is true, then the ShowAllData property is called, which will remove all the filters.

Code:

Sub FilterListObject()
ActiveWorkbook.Sheets(“Sheet3”).ListObjects(“Table3”).Range.AutoFilter Field:=3, Criteria1:= _
“FALSE”, Operator:=xlAnd
End Sub

Sub ClearAllFilters()
Range(“Table3[[#Headers],[S]]”).Select
If ActiveWorkbook.Worksheets(“Sheet3”).FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub

• Step 8: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Editor.

Table when the FilterListObject subroutine is run.

Table when the ClearAllFilters subroutine is run.

It is how you format Excel tables with VBA ListObjects.

Examples

Go through some interesting examples to see how VBA ListObjects are being used in Excel.

Example #1

When you click on any cell inside a table, and you have multiple tables, you can get the name of the table by checking the ActiveCell’s object name by following the steps below.

Consider an example table:

• Step 1: Define a sub-procedure to find the table name the ActiveCell is in.

• Step 2: Declare a string variable to store the name of the ListObject.

• Step 3: To prevent any errors in case the ListObject does not exist, use VBA On Error Resume Next.

• Step 4: Using an If-condition, check if the string variable is not an empty string.

If it isn’t empty, print the name of the ListObject in a MessageBox.

• Step 5: Declare an Else condition in case there is no table where your ActiveCell is.

Code:

Sub SelectedCellInTable()
Dim Test As String
On Error Resume Next
Test = ActiveCell.ListObject.name
On Error GoTo 0
If Test <> “” Then
MsgBox “Cell is part of the table named: ” & Test
Else
MsgBox “Cell is not part of any table”
End If
End Sub

• Step 6: Run the sub-procedure and see the results.

Your cursor is currently in the table “Table1.”

Example #2

Consider a table where you need to sort its numerical values in descending order. VBA ListObjects can be used to store the table and then sort using the functions available in the VBA ListObjects.

• Step 1: Name the subroutine which will sort a given table in either ascending or descending order.

• Step 2: Using VBA constants define the order of sorting and store it in an integer variable. Here, the table will be sorted in descending order.

• Step 3: Define a ListObject variable to store the table to be sorted.

• Step 4: If there are any sortings done earlier, remove them and bring them back to the default state. If it is not done, it may result in unexpected outputs.

• Step 5: Using the Sort.Sortfields function in ListObject, sort the second column with numerical values in descending order.

Using the Add2 function, the rows in the table are sorted according to the “Key” component. In that, using the ListColumns function, select column 2 and sort it based on the sort order defined earlier.

• Step 6: Apply the sorting function.

Code:

Sub SortTable()
Dim SortOrder As Integer
SortOrder = xlDescending ‘(or xlAscending)
Dim tb2 As ListObject
Set tb2 = ActiveSheet.ListObjects(“Table7”)
‘Clear Any Prior Sorting
tb2.Sort.SortFields.Clear
tb2.Sort.SortFields.Add2 _
Key:=tb2.ListColumns(2).Range, _
Order:=SortOrder
tb2.Sort.Apply
End Sub

• Step 7: Run the above subroutine and then view the table to see the results.

With that, you’ve successfully sorted the table in descending order.

Example #3

Suppose you want to find the location of a value in the given table. If you type in the value to be searched, it will return the row number of the table it is in. This can be done using VBA ListObjects.

• Step 1: Name the subroutine to search for values in the table given above.

• Step 2: Get the value to be searched by the user using the InputBox function.

The Application.InputBox function doesn’t change pages to the Excel Workbook from the VBA Editor as the normal InputBox function does. The title of the InputBox is defined after the query to be asked. 9985650255

• Step 3: Define the table with a ListObject. It is the table where the value will be searched for.

• Step 4: Perform error handling in case the table does not exist.

• Step 5: Search for the lookup value in the table. In this example, only the first column is considered for searching.

Using the DataBodyRange attribute in the ListObject, find the value in the table by going through the whole table using the “xlWhole” constant in VBA. If found, store the address in the variable.

• Step 6: Reset the Error numbers to 0.

• Step 7: Check if the cell is not empty. If so, print the table index where the value is found in a MessageBox.

• Step 8: Declare an Else condition to be executed in case the value isn’t found in the table.

Code:

Sub LookupTableValue()
Dim LookupValue As String
LookupValue = Application.InputBox(“Enter value in table”, “Search Table”)
Dim tbl As ListObject
Set tbl = ActiveWorkbook.Sheets(“Sheet4”).ListObjects(“Table4”)
On Error Resume Next
Dim FoundCell As Range
Set FoundCell = tbl.DataBodyRange.Columns(1).Find(LookupValue, LookAt:=xlWhole)
On Error GoTo 0
If Not FoundCell Is Nothing Then
MsgBox “Found in table row: ” & _
tbl.ListRows(FoundCell.Row – tbl.HeaderRowRange.Row).Index
Else
MsgBox “Value not found”
End If
End Sub

• Step 9: Run the subroutine to search for the ID number in the table.

You’ve successfully created a subroutine to search for values in the table.

Important Things To Note

• Leverage structured references when working with columns and data within ListObjects to enhance code readibility.
• Before performing operations on a ListObject, check if it exists on the specified sheet.
• When resizing tables, ensure that the new range maintains the table’s structure.
• If your table is linked to external data, don’t forget to refresh it after the data source changes.
• When resizing tables, avoid specifying ranges that disrupt the table’s structure, such as changing the number of columns inconsistently.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA ListObjects, with its syntax and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA ListObjects. Here we learn how to create table format & format them using VBA coding, with examples & points to remember. You can learn more from the following articles –

• VBA Variable Range
• VBA Object Required
• VBA GetObject