在Excel VBA中遍历表格 [英] Iterate through Table in Excel VBA

问题描述

迭代表中每个单元格的内容并检索/存储其值的最佳方法是什么. 使用当前的方法，我无法在变量val

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

样品表:

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

推荐答案

您有一个ListObject，请使用其API！ ListRows和ListColumns是对象集合，并且是迭代这些对象的最快方法，

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

如果您只想将内容收集到2D值数组中，则无需进行任何迭代-只需抓住DataBodyRange并将其像其他常规" Range一样对待:

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

如果以后需要迭代2D变量数组，最快的方法(与上述相同)是For...Next循环:

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

这篇关于在Excel VBA中遍历表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！