Menu
Is it possible in Excel VBA to reference a named table?
Hypothetically this could be...
Sheets("Sheet1").Table("A_Table").Select
I have seen some mention of tables being a list object but I'm not sure if that is the same thing.
609
Add A Reference Make sure that you click on the workbook you want to add the reference to, and from the VBA editor menu choose Tools -> References. In the displayed list check the box beside your renamed add-in, and then click on OK. You'll see that your workbook now has a new reference to the add-in.
When you create an Excel table, Excel creates a default table name (Table1, Table2, and so on), but you can change the table name to make it more meaningful. Select any cell in the table to show the Table Tools > Design tab on the ribbon. Type the name you want in the Table Name box, and press Enter.
If the Excel VBA Range object you want to refer to is a single cell, the syntax is simply “Range(“Cell”)”. For example, if you want to make reference to a single cell, such as A1, type “Range(“A1″)”.
The OP asked, is it possible to reference a table, not how to add a table. So the working equivalent of
Sheets("Sheet1").Table("A_Table").Select
would be this statement:
Sheets("Sheet1").ListObjects("A_Table").Range.Select
or to select parts (like only the data in the table):
Dim LO As ListObject
Set LO = Sheets("Sheet1").ListObjects("A_Table")
LO.HeaderRowRange.Select ' Select just header row
LO.DataBodyRange.Select ' Select just data cells
LO.TotalsRowRange.Select ' Select just totals row
For the parts, you may want to test for the existence of the header and totals rows before selecting them.
And seriously, this is the only question on referencing tables in VBA in SO? Tables in Excel make so much sense, but they're so hard to work with in VBA!
81
A "table" in Excel is indeed known as a ListObject.
The "proper" way to reference a table is by getting its ListObject from its Worksheet i.e. SheetObject.ListObjects(ListObjectName).
If you want to reference a table without using the sheet, you can use a hack Application.Range(ListObjectName).ListObject.
NOTE: This hack relies on the fact that Excel always creates a named range for the table's DataBodyRange with the same name as the table. However this range name can be changed...though it's not something you'd want to do since the name will reset if you edit the table name! Also you could get a named range with no associated ListObject.
Given Excel's not-very-helpful 1004 error message when you get the name wrong, you may want to create a wrapper...
Public Function GetListObject(ByVal ListObjectName As String, Optional ParentWorksheet As Worksheet = Nothing) As Excel.ListObject
On Error Resume Next
If (Not ParentWorksheet Is Nothing) Then
Set GetListObject = ParentWorksheet.ListObjects(ListObjectName)
Else
Set GetListObject = Application.Range(ListObjectName).ListObject
End If
On Error GoTo 0 'Or your error handler
If (Not GetListObject Is Nothing) Then
'Success
ElseIf (Not ParentWorksheet Is Nothing) Then
Call Err.Raise(1004, ThisWorkBook.Name, "ListObject '" & ListObjectName & "' not found on sheet '" & ParentWorksheet.Name & "'!")
Else
Call Err.Raise(1004, ThisWorkBook.Name, "ListObject '" & ListObjectName & "' not found!")
End If
End Function
Also some good ListObject info here.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
