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)
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
try this:
Dim row as Range
For Each row in [tabWorkers[first name]].Rows
MsgBox row.Value
Next
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