How do I loop an excel 2010 table by using his name & column reference?

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

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

try this:

Dim row as Range
For Each row in [tabWorkers[first name]].Rows
     MsgBox row.Value
Next

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"