chrispsn/tables_in_vba.md

## tables_in_vba.md

      
    Raw
  

              tables_in_vba.md
            
          
    Excel Tables and VBA

It's 2020, and there's now many ways to work with data of arbitrary length in Excel:

Power Query's M language
JavaScript
dynamic arrays.

But if you use or support older Excel versions, VBA can still be useful.
Here are some short and fast ways to manipulate Excel Tables in VBA. I use them all the time.
Why Excel Tables?

I like using tables to work with data of arbitrary length because:

structured references are often more readable than location references, and allow formulas to precisely reference data of arbitrary length (so when a table resizes, you don't need to change your formulas or worry about accidentally including data underneath the table)
tables can have zero rows - and your formulas won't break and the table will still appear on the grid
calculated columns auto-fill themselves across the whole table, including when you resize a table
you can set the length of a table
you can name a table for easy reference in both Excel formulas and VBA
you can have multiple tables on one sheet (handy for widescreen monitors)
the ListObject API provides clean ways to access parts of the table: data rows and/or columns, or the header or total rows.

As a general rule, I try to express model logic via sheet formulas and (where appropriate) tables, with a little bit of manually triggered VBA to control the contents or length of tables, as follows.
1. Get a reference to a table

Most of the time I store code that manipulates an Excel Table on the same sheet as the table, so we can easily get the reference using Me:
Dim t as ListObject: Set t = Me.ListObjects("table_name")

Avoid using anything that relies on the workbook being active, such as:

[table_name].ListObject
Range("table_name").ListObject.

For example, if you call your code from another workbook, it may look for table_name in the calling workbook, not the callee.
More generally, I've found it's often useful to (speaking loosely) use relative instead of absolute references:

storing the VBA code in the sheet itself instead of a separate module, and using Me instead of stating the sheet name
using locals instead of globals (for both VBA variables and named sheet ranges).

That way, a self-contained sheet can be copied to other workbooks and you don't need to update references (except for buttons).
2. Clear a table

Delete all data rows:
If Not t.DataBodyRange Is Nothing Then: t.DataBodyRange.Delete

If you don't have headers enabled, this will delete the table (even if the total row is visible), so you may need to temporarily enable them before the delete using .ShowHeaders = True, then add an empty data row using t.ListRows.Add.
3. Resize a table

This can be handy if you want to:

generate data using a table that entirely comprises calculated columns
dump data from VBA to Excel (see #4 below).

If you know how many rows you need, don't insert rows one at a time. Instead, resize the table range:
t.Resize t.Range.Resize(RowSize:=row_count + 1)

Note that row_count has to be at least 1, so if you could have zero data rows, you'll want to at least temporarily have the header row visible: .ShowHeaders = True; and if the header row is visible, it's included in the row count.
t.Resize will leave the DataBodyRange as Nothing if the row count is 1.
If the DataBodyRange is Nothing when the resize occurs, t.Resize seems to subtract one from RowSize if the total row is visible - for proof see the test at the bottom of this doc. (It would be nice if we could set the ListObject.DataBodyRange range and avoid having to think about the header and total rows, but unfortunately the property is read-only.)
Don't put values underneath your tables when you use this! They won't move out of the way of the table and will become part of its data.
Finally, if you've deleted the DataBodyRange and you know a resize would result in no data rows (eg because you prepped your data already and know its count), you could just skip the resize (and maybe other steps): resize or no resize, the DataBodyRange will still be Nothing.
4. Write to a table

First, if you're not sure that t.DataBodyRange is not Nothing, check before trying to write! For example, it will still be Nothing if the t.Resize step above used row count 1.
If Not t.DataBodyRange Is Nothing Then
    ' table writing code
End If

If you can, write the data to the table in one hit. You may need to resize the table in advance (see #3 above).
Building the array in VBA can be tricky - I'll update this doc if I find some general principles that make this easier.
t.DataBodyRange.Value2 = my_array

Sometimes you only need to write data to one or more columns. For example, sometimes I will write to the leftmost column and let calculated columns do the rest of the work (lookups to other tables, calculations, etc). In these cases we can get a column using its index or - for readability and in case the column order changes - its name:
t.ListColumns("col_name").DataBodyRange.Value2 = my_col_data

You could do the resize and write in one hit - it seems to be a little faster - but it will break if you have the table's total row enabled, so I'd avoid it. Modify as needed depending on whether you're writing to the entire table or only one column.
If Not t.DataBodyRange is Nothing then: t.DataBodyRange.Delete
t.ListRows.Add.Range.Resize(4) = WorksheetFunction.Transpose(Array(1, 2, 3, 4))

5. Append to a table

Could you instead build up the array in VBA - using ReDim or pre-sizing the array at the start - and then transfer it to the sheet in one hit? Using .NET's ArrayList may make this process easier, since you can append items to it using .Add then convert it to a regular Array once done using .ToArray.
Or you could resize once but write in several hits by taking sections of the table/column using some_range.Offset and/or some_range.Resize:
t.DataBodyRange.Rows(1 + previous_row_count).Resize(new_rows_count).Value2 = new_rows_data

or similarly:
With t.ListColumns("col_name").DataBodyRange
    Range(.Item(start_row_idx), .Item(end_row_idx)).Value2 = my_col_data
End With

For small tables, you could insert rows one-at-a-time using t.ListRows.Add, but it's very slow:
t.ListRows.Add.Range.Value2 = my_row_data

Aside: t.Resize weirdness

Try this:
Sub rowcount_check(t As ListObject)

    Debug.Print ".Range.Rows.CountLarge is: " & t.Range.Rows.CountLarge
    Debug.Print "After the resize, I should have rowcount: " & t.Range.Rows.CountLarge + 2
    Debug.Print "And I calculate resized rowcount of: " & t.Range.Resize(t.Range.Rows.CountLarge + 2).Rows.CountLarge
    t.Resize t.Range.Resize(t.Range.Rows.CountLarge + 2)
    Debug.Print "-Resized-"
    Debug.Print "I now have rowcount: " & t.Range.Resize.Rows.CountLarge
    
End Sub

Sub test()

    Dim t As ListObject: Set t = Me.ListObjects("Table1")
    
    Debug.Print "WITHOUT TOTAL ROW"
    t.ShowTotals = False
    If Not t.DataBodyRange Is Nothing Then: t.DataBodyRange.Delete
    rowcount_check t
    
    Debug.Print
    
    Debug.Print "WITH TOTAL ROW"
    t.ShowTotals = True
    t.DataBodyRange.Delete
    rowcount_check t
    
    Debug.Print
    
    Debug.Print "WITH TOTAL ROW AND PRESENT DATABODYRANGE"
    rowcount_check t

End Sub

Output:
WITHOUT TOTAL ROW
.Range.Rows.CountLarge is: 2
After the resize, I should have rowcount: 4
And I calculate resized rowcount of: 4
-Resized-
I now have rowcount: 4

WITH TOTAL ROW
.Range.Rows.CountLarge is: 3
After the resize, I should have rowcount: 5
And I calculate resized rowcount of: 5
-Resized-
I now have rowcount: 4

WITH TOTAL ROW AND PRESENT DATABODYRANGE
.Range.Rows.CountLarge is: 4
After the resize, I should have rowcount: 6
And I calculate resized rowcount of: 6
-Resized-
I now have rowcount: 6