16

Since Excel 2010 I'm using a lot of tables within Excel. For example, I have a table "tabWorkers" with 3 columns: "ID", "Firstname", "Lastname".

I already found out I can refer to a table in VBA using [].

For example:

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns(2).Value)
Next

This will give me the Firstname of every row which works great. But I want to make it more dynamic by using the name of it's column like this:

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns("Firstname").Value)
Next

Of course I could make some kind of lookup that binds the column index '2' to a var like FirstnameIndex, but I want the correct syntax. I'm sure it's possible but just not really documented (like with [tabWorkers].Rows)

peterh
  • 11,875
  • 18
  • 85
  • 108
Tiele Declercq
  • 2,070
  • 2
  • 28
  • 39
  • what exactly do you mean by name of column? because this could mean differnt things - is the name iside a cell? an other name instead of the usual A-B-C... label? a named range? – Jook Sep 19 '12 at 13:42
  • you know, you can select a column, then name it, so you get a named range like "Firstname". you could then use `Range("Firstname").value` to access that column. – Jook Sep 19 '12 at 13:43
  • @Jook: with name of a column i mean the name of the tablecolumn. Excel itself refers to this in his formulas using tablename[@columnname] – Tiele Declercq Sep 19 '12 at 13:53
  • @Jook: Defining a name for a worksheet column might work but that's creating another culpitt.. I want to access the table's column directly. – Tiele Declercq Sep 19 '12 at 13:54

8 Answers8

19

I'm not very familiar with the shorthand method of referring to tables. If you don't get an answer, you might find this longhand method, that uses the ListOject model, useful:

Sub ListTableColumnMembers()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet
Dim lr As Excel.ListRow

Set ws = ThisWorkbook.Worksheets(2)
Set lo = ws.ListObjects("tabWorkers")

For Each lr In lo.ListRows
Debug.Print Intersect(lr.Range, lo.ListColumns("FirstName").Range).Value
Next lr
End Sub
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
8

try this:

Dim row as Range
For Each row in [tabWorkers[first name]].Rows
     MsgBox row.Value
Next
Spencer Hutton
  • 81
  • 1
  • 1
  • 1
    i think your solution is pretty neat. Can you point me to any documentation that explains how a table in a spreadsheet can be cleanly iterated? Thanks! – YSD Jul 27 '16 at 17:58
8

I too have pondered this question and searched and searched without finding a really good answer

Finally today the penny dropped and I found something which I would like to share to help those following with the same question that was bugging me.

In brief the question was: how to reference the rows of a table in vba using the standard names of the parts of a table. It turns out to be quite easy. You can refer to particular rows of the table and call the columns by their true name without the need to redefine any further variables.

The construct is like this: Range("TableName[ColumnName]")(RowNumber)

where TableName and ColumnName are the standard table and column names from Excelside, and RowNumber is the simple integer index of the row number from 1 to Range("Table1").Rows.Count

For a two column table called Table1 with columns called ID and Data you can loop through the table elements using this construction

For Rw = 1 To Range("Table1").Rows.Count
     MsgBox(Range("Table1[ID]")(Rw) & "has value" & Range("Table1[Data]")(Rw)    )
Next Rw

This keeps to very similar constructs to those used in Excel and requires no extra naming. And it is so very readable! You can also append the appropriate range modifiers such as .Text, .Value, .Formula etc after that (Rw) part as needed.

Note that if you have a single row table (eg for parameters), or wish to refer to the first row of a larger table, you can skip the (Rw) bit and use the Range directly. So a Table called Params with columns called Colour, Size and Height can be referred to using Range("Params[Colour]") or Range("Params[Height]") etc. Don't you like that? :-)

This discovery has really hooked me on Tables. They now provide for me a very tidy link between vba and the sheets in a readable and compatible way.

Thank you Microsoft!

Bob JordanB

Raphael Petegrosso
  • 3,870
  • 2
  • 24
  • 27
user2092957
  • 311
  • 3
  • 5
3

I typically do it like this

Dim rng as Range
Set rng = Application.Range("Tablename[Columnname]")

You only need to refer to the workbook because the table names are unique across the whole workbook. But if you have identically named tables in different open workbooks then this will affect the active workbook, not both or the one in the background. To prevent that you should call the range object of the actual sheet in which your table resides.

Hopefully, this will show it can be done in the way I described above:

For example I have a method in an Access database which applies conditional formatting to a table in an Excel document I would have just created and populated using CopyFromRecordSet.

Which has a signature of

Private Function HighlightBlankOrZeroColumn(RangeToFormat As Range, HighlightColor As Long)

and I call like this

HighlightBlankOrZeroColumn ApXL.Range("Table1[" & SoucreRst.Fields(intCount).Name & "]"), BlankOrZeroColor

where ApXL is a New Excel.Application and SoucreRst is an ADO recordset.

By that point I'd already made my Recordset --> Range a table by calling these two methods:

xlWSh.ListObjects.Add(xlSrcRange, xlWSh.UsedRange, , xlYes).Name = "Table1"
xlWSh.ListObjects("Table1").TableStyle = "TableStyleLight16"
Brad
  • 11,934
  • 4
  • 45
  • 73
  • ThisWorkbook has no range property. – Doug Glancy Sep 19 '12 at 14:44
  • Ack, I guess what I'm really doing is using `Application.Range` but I thought that might be *too* broad. It works for me because whenever I am doing this I am creating my own instance. But I'll update – Brad Sep 19 '12 at 14:49
  • 1
    That makes sense. Inspired by your answer, I tried it with `Thisworkbook.Names("Tablename[Columnname]").RefersToRange` but couldn't get it to work. – Doug Glancy Sep 19 '12 at 14:51
  • `Application.Range()` worked for me, was exactly what i was looking for. – KurzedMetal Aug 26 '13 at 15:47
3

More concise, tested in Excel 2007:

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox Intersect (row,[tabWorkers[FirstName]]).Value
Next
Antoni Gual Via
  • 714
  • 1
  • 6
  • 14
0

Thank you Doug! That helped a lot. a working example:

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns(row.ListObject.ListColumns("Firstname").Index).Value)
Next
Tiele Declercq
  • 2,070
  • 2
  • 28
  • 39
0

This is my first post also. It's been a long time since the question was asked, but I hope it to be of help anyway.

The main idea is to keep the piece of code as neat as it gets.

Set selrange = Selection.EntireRow

For Each rrow In selrange.Rows
        selrange.Parent.Parent.Names.Add "rrow", rrow
        name = [table1[name] rrow].Text
        age = [table1[age] rrow].Text
        gender = [table1[gender] rrow].Text
Next
selrange.Parent.Parent.Names("rrow").Delete
Ciro Rocha
  • 1
0

Thanks for all the answers to this thread, with some additional research, below is my version of the code. Basically you define the columns earlier in the code and while you loop through the rows, you extract the values of the defined columns for each row.

Sub xlTableLoop_namedColumns()
Dim lst As ListObject
Dim rw As ListRow
Dim colSubject As ListColumn
Dim colDate As ListColumn
Set lst = ActiveSheet.ListObjects("mlist")  'the name of Table 
Set colSubject = lst.ListColumns("Subject") 'the name of column 1 in table header 
Set colDate = lst.ListColumns("date")       'the name of column 2 in table header
For Each rw In lst.ListRows                 'loop through all the rows
    Debug.Print colSubject.DataBodyRange.Rows(rw.Index).Value  'get value of column 1
    Debug.Print colDate.DataBodyRange.Rows(rw.Index).Value     'get value of column 2
Next
End Sub

Hope it helps someone.

Uttam
  • 596
  • 1
  • 6
  • 11