Working with Microsoft Excel tables in VBA

This blog post will be based on working with tables via VBA. They present some unique issues, which has to do with how their structured in the object model. Good or bad, it’s what we have. These are based on my trial and errors, so hopefully this will help somebody out there. That being said I consider this an intermediate level post.

If you’re not up to speed with tables, take a look at these:

http://excelandaccess.wordpress.com/2013/01/07/why-you-should-use-structured-references-in-tables/

When looking for information about tables, or adding/deleting data, it can be tricky. Here are some pieces of information to keep in mind, which isn’t very obvious, but are cornerstones in working with tables in VBA:

·                    Deleting the DataBodyRange (data portion of a table) will set it to Nothing *

·                    If table parts (i.e. header, totals row) aren’t showing, attempting access will result in an error

·                    Accessing the straight Range of a table includes all visible parts (i.e.

[#All])

Tables are ListObjects *

If you’ve worked with tables in VBA, you may have noticed that when deleting a table’s entire data body, you are still left with a single, blank row. This is because you must have at least one row of data for the table to still be “alive”. No data rows, no table. You can hide the header and totals row, but you can’t get rid of the body. This presents some interesting coding when working with tables, and can be problematic, aka headache-inducing.

* Check the links at the bottom of the post for additional information on these topics.

Here are some VBA examples for doing various things with tables in VBA. The file will be available (link at bottom of post) for download.

Common variables used throughout

Public WS As Worksheet

Public loSet As ListObject
Public loGet As ListObject
Public loTest As ListObject

Public rCols As Range
Public rRows As Range
Public rBody As Range
Public rData As Range
Public rHeader As Range
Public rStart As Range

Public iCol As Long
Public iRow As Long
Public iStep As Long
Public iLastRow As Long
Public iRowCnt As Long
Public iColCnt As Long

Public sMsg As String

Public Const sSheetName As String = “Sheet1”
Public Const sTableName As String = “Table1”
Public Const sTableName2 As String = “Table2”
Public Const NL As String = vbNewLine
Public Const DNL As String = vbNewLine & vbNewLine

As you can see I used some constants for testing in this specific workbook. Change these variables for testing in your environment. I tried to keep the naming convention simple.

Getting information from your tables

When working with tables via VBA it’s important to know how to look at them, and thus where to go for information. These are some examples of how to get dimension(s) information from your tables.

Listing columns

This will step through a table and list the column headers in a message box, as well as the Immediate window in the Visual Basic Editor (Ctrl + G to show it).

Sub Table_ListColumns()

On Error Resume Next
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loTest = WS.ListObjects(sTableName)
On Error GoTo 0

If WS Is Nothing Or loTest Is Nothing Then
MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
Exit Sub
End If

sMsg = vbNullString
For iStep = 1 To loTest.ListColumns.Count
Debug.Print loTest.ListColumns.Item(iStep).Name
sMsg = sMsg & NL & iStep & “) ” & loTest.ListColumns(iStep)
Next iStep
sMsg = “‘” & loTest.Name & “‘ column headers:” & NL & sMsg

MsgBox sMsg, vbOKOnly, “COLUMN HEADERS”

End Sub

Column count

This will not loop through the table, but give a straight count of columns, along with which/where the start/end columns are.

Sub Table_CountColumns()

On Error Resume Next
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loTest = WS.ListObjects(sTableName)
On Error GoTo 0

If WS Is Nothing Or loTest Is Nothing Then
MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
Exit Sub
End If

sMsg = “There are ” & loTest.ListColumns.Count & ” columns in ‘” & sTableName & “‘.”
sMsg = sMsg & DNL & “Starting on column ” & loTest.ListColumns(1).Range.Column & ” (” & loTest.ListColumns(1) & “)”
sMsg = sMsg & NL & “Ending on column ” & loTest.ListColumns(loTest.ListColumns.Count).Range.Column & ” (” & loTest.ListColumns(loTest.ListColumns.Count) & “)”

MsgBox sMsg, vbInformation, UCase(sTableName)

End Sub

Row count

Again, no loop, just find out how many rows are in the table and where they are.

Sub Table_CountRows()

On Error Resume Next
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loTest = WS.ListObjects(sTableName)
On Error GoTo 0

If WS Is Nothing Or loTest Is Nothing Then
MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
Exit Sub
End If

sMsg = “There are ” & loTest.ListRows.Count & ” rows in ‘” & sTableName & “‘.”
sMsg = sMsg & DNL & “Starting on row ” & loTest.ListRows(1).Range.Row
sMsg = sMsg & NL & “Ending on row ” & loTest.ListRows(loTest.ListRows.Count).Range.Row

MsgBox sMsg, vbInformation, UCase(sTableName)

End Sub

Resizing Tables

Resize table based on original

This will basically clear out the manual data and leave the formulas. It gets a row count and uses that to insert rows to its original size. Please note these focus on resizing rows programmatically, not columns, which can be trickier.

Sub ResizeTableRows()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

‘/// Ensure the table has a data range
If loSet.DataBodyRange Is Nothing Then
MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
Exit Sub
End If

‘/// Get the number of rows in the table
iRowCnt = loSet.ListRows.Count

‘/// Delete destination table data
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Delete
End If

‘/// Resize to source table size
For iStep = 1 To iRowCnt
loSet.ListRows.Add iStep
Next iStep

‘///////////////////////////////////////////////////////////////////////////
‘/// At this point the table is restructured, and the formulas which were
‘/// there are re-populated
‘///////////////////////////////////////////////////////////////////////////

End Sub

Resize table based on another tables size (in rows)

This utilizes two tables. The second table is only used for its row size and makes the first table the same size. It deletes the DataBodyRange as well, so all manual data will be erased, leaving only formulas.

Sub ResizeTableRowsBasedOnAnother()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)

‘/// Set two tables, one to set (destination)
‘/// and one to resize from (source)
Set loSet = WS.ListObjects(sTableName)
Set loGet = WS.ListObjects(sTableName2)

‘/// Delete destination table data
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Delete
End If

‘/// Ensure source table has data range
If loGet.DataBodyRange Is Nothing Then
MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
Exit Sub
End If

‘/// Resize to source table size
For iStep = 1 To loGet.ListRows.Count
loSet.ListRows.Add iStep
Next iStep

‘///////////////////////////////////////////////////////////////////////////
‘/// At this point the table is restructured, and the formulas which were
‘/// there are re-populated
‘///////////////////////////////////////////////////////////////////////////

End Sub

Resize table columns

This example will add a single column. While I am very hesitant to code something like this, here is an example. While it’s easier to add rows, adding columns present some unique problems, which include:

·                    Where should the columns be inserted?

·                    Without proper absolute structured formula referencing, this could throw off formulas

·                    Ideally no data should be to the right of the table, so an error won’t be thrown.

Sub ResizeTableCols()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

‘/// Ensure the table has a data range
If loSet.DataBodyRange Is Nothing Then
MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
Exit Sub
End If

‘/// Get the number of columns in the table, add one
iColCnt = loSet.ListColumns.Count + 1

‘/// Delete destination table data
If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Delete
End If

‘/// Resize to source table size
On Error Resume Next
For iStep = 1 To iColCnt
If loSet.ListColumns(iStep) Is Nothing Then
Err.Clear
loSet.ListColumns.Add
If Err.Number > 0 Then
‘something prevented the column from being added
Exit For
End If
End If
Next iStep
On Error GoTo 0

‘///////////////////////////////////////////////////////////////////////////
‘/// At this point the table is restructured, and the formulas which were
‘/// there are re-populated
‘///////////////////////////////////////////////////////////////////////////

End Sub

Select table parts

Table ranges

It’s always best to work with actual table range referernces. While you can access the range object of the underlying worksheet, it gets tough. This shows an example.

Sub ListTableParts()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

If loSet.DataBodyRange Is Nothing Then
If MsgBox(“Data range has been deleted. Create now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ListRows.Add 1
End If
End If

‘/// Using standard table referencing objects
sMsg = “Standard table referencing” & DNL
If Not loSet.DataBodyRange Is Nothing Then
sMsg = sMsg & “Data range: ” & loSet.DataBodyRange.Address(0, 0) & NL
End If
If Not loSet.HeaderRowRange Is Nothing Then
sMsg = sMsg & “Header range: ” & loSet.HeaderRowRange.Address(0, 0) & NL
Else
sMsg = sMsg & “Header range: (none set)” & NL
End If
If Not loSet.TotalsRowRange Is Nothing Then
sMsg = sMsg & “Totals range: ” & loSet.TotalsRowRange.Address(0, 0) & NL
Else
sMsg = sMsg & “Totals range: (none set)”
End If

‘/// Using range referencing objects
sMsg = sMsg & DNL & “Range referencing” & NL
If Not loSet.DataBodyRange Is Nothing Then
sMsg = sMsg & “Data range: ” & loSet.Range(1, 1).Address(0, 0) & “:” & loSet.Range(loSet.Range.Rows.Count, loSet.Range.Columns.Count).Address(0, 0) & NL
End If

MsgBox sMsg, vbInformation, “INFO”

End Sub

Select table Header

This selects just the header range of the table, if it is showing. If it’s not showing the user gets the option to show it at that time.

Sub SelectHeader()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

If Not loSet.HeaderRowRange Is Nothing Then
loSet.HeaderRowRange.Select
Else
If MsgBox(“Header row isn’t showing. Show now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ShowHeaders = True
End If
End If

End Sub

Select table data

This selects just the data range of the table, if it is not deleted. If it’s not there the user gets the option to add it at that time.

NOTE: Adding a data body (DataBodyRange) to a table is as simple as inserting a row. This is quite a useful trick when working with tables via VBA.

Sub SelectData()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

If Not loSet.DataBodyRange Is Nothing Then
loSet.DataBodyRange.Select
Else
If MsgBox(“Data range has been deleted. Create now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ListRows.Add 1
End If
End If

End Sub

Select table Totals row

This selects just the totals row range of the table, if it is showing. If it’s not showing the user gets the option to show it at that time.

Sub SelectTotalRow()

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

If Not loSet.TotalsRowRange Is Nothing Then
loSet.TotalsRowRange.Select
Else
If MsgBox(“Totals row isn’t showing. Show now?”, vbYesNo, “ERROR!”) = vbYes Then
loSet.ShowTotals = True
End If
End If

End Sub

Show table parts

This routine has a Boolean (True/False) variable passed to it to turn on or off all table parts (non-formatting)

Sub ShowTableParts(Optional ByVal bState As Boolean = True)

‘/// Set misc variables
Set WS = ThisWorkbook.Worksheets(sSheetName)
Set loSet = WS.ListObjects(sTableName)

loSet.ShowTotals = bState
loSet.ShowHeaders = bState
If bState = True Then loSet.ShowAutoFilter = bState

End Sub

Note that the ShowAutoFilter portion only runs if a True variable is passed (turning properties on). This is because if you turn off the header row, there’s nothing to filter on, so there’s no need to worry about it.

Summary

The important parts of the object model to look at with respect to tables are:

·                    ListObject

·                    DataBodyRange

·                    HeaderRowRange

·                    TotalsRowRange

·                    ListRows

·                    ListColumns

There are other things you can do with tables via VBA, this certainly isn’t the complete guide. Some of those things include:

·                    Work with slicers *

·                    Unlist it (convert to standard range)

·                    Set style/formatting options

Additional information

Jan Karel Pieterse: http://www.jkp-ads.com/articles/Excel2007Tables.asp

Colin Legg: http://colinlegg.wordpress.com/2012/09/01/clearing-excel-tables/

Sample file for download: tblExample

File tested and works in Excel 2010 and 2013 without issue.

A special thanks to Chris “Smitty” Smith, Christopher T. Fennell and Colin Legg, equally for the direction, support and inspiration for this blog post. Thank you.