Iterate through Table in Excel VBA

Question

What is the best way to iterate through the contents of each cell in a table and retrieve/store its value. With my current approach, I'm unable to obtain set the value of a table in my variable val

Sample Table:

Set ws = ActiveSheet
ws.Name = "sheet1"

Set tbl = ws.ListObjects("tblBor")
Application.Calculation = xlCalculationManual
ws.Calculate

With tbl.Sort
         .SortFields.Clear
         .SortFields.Add Key:=Range("tblBor[ID]"), SortOn:=xlSortOnValues, Order:=xlAscending
         .Header = xlYes
         .Apply
End With

Set rng = Range(tbl)
rows = tbl.Range.rows.Count
Columns = tbl.Range.Columns.Count

For iter = 1 To rows
    For col = 1 To Columns
        'Iterate through each row by each column
        'val = tbl.DataBodyRange(iter, col).Value

    Next col
Next iter

Val is a built-in function. Maybe use a different name. Also you have a typo Columns = tbl.Rable.Columns.Count, what's Rable? Finally, you need to be consistent - you're using both tbl.Range and tbl.DataBodyRange. tbl.Range includes the header row. Not sure you want that. You might also want to consider a different name for rows and Columns. — BigBen
– BigBen, Commented Jun 5, 2019 at 18:20
changing the variable names did the trick. Also, would it make more sense to iterate through each column or is it more advisable to use tbl.ListRows(iter).Range(1, col).Value — Josh
– Josh, Commented Jun 5, 2019 at 18:32
I think the current approach is cleaner. tbl.DataBodyRange.Cells(iter, col). — BigBen
– BigBen, Commented Jun 5, 2019 at 18:34
@BigBen the problem with Range.Cells(row, col) is that it's retrieving objects in an object collection by index, which is inefficient. Object collections want to be iterated with a For Each loop. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 5, 2019 at 19:16
@MathieuGuindon I agree. I would have proposed an array approach as you actually did :) (guess I'm just too lazy today to write out an answer). — BigBen
– BigBen, Commented Jun 5, 2019 at 19:22

Mathieu Guindon · Accepted Answer · 2019-06-05 19:14:13Z

2

You have a ListObject, use its API! ListRows and ListColumns are object collections, and the fastest way to iterate these, by several orders of magnitude, is with a For Each loop:

Dim tblRow As ListRow
For Each tblRow In tbl.ListRows
    Dim tblCol As ListColumn
    For Each tblCol In tbl.ListColumns
        Debug.Print "(" & tblRow.Index & "," & tblCol.Index & "): " & tblRow.Range(tblCol.Index).Value
    Next
Next

If you just want to collect the contents into a 2D array of values, you don't need to iterate anything - just grab the DataBodyRange and treat it like any other "regular" Range:

Dim contents As Variant
contents = tbl.DataBodyRange.Value

If you later need to iterate that 2D variant array, the fastest way (same source as above) is For...Next loops:

Dim currentRow As Long
For currentRow = LBound(contents, 1) To UBound(contents, 1)
    Dim currentCol As Long
    For currentCol = LBound(contents, 2) To UBound(contents, 2)
        Debug.Print "(" & currentRow & "," & currentCol & "): " & contents(currentRow, currentCol)
    Next
Next

answered Jun 5, 2019 at 19:14

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chris neilsen Over a year ago

any reason you put the Dim currentCol inside the first For loop?

Mathieu Guindon Over a year ago

@chrisneilsen because I like my variables declared as close as possible to where they're used. Dim statements aren't executable though, so it pragmatically doesn't make a difference; Dim currentRow As Long, currentCol As Long above the outer loop would be just as good, but I don't like declaring multiple variables in a single instruction, so...

chris neilsen Over a year ago

Thanks for that. so it pragmatically doesn't make a difference. That's why I asked, unless one is aware of that, it might be misleading. (I've answered a Q where the OP was expecting a Dim to execute on each iteration )

Mathieu Guindon Over a year ago

@chrisneilsen TBH in real code I'd probably extract the inner loop into its own procedure; having the variable inside the outer loop body makes it easier to just cut that code and paste it in a new method... where it would now be declared just above the loop body ;-)

Collectives™ on Stack Overflow

Iterate through Table in Excel VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related