excelvbaexcel-tableslistobject

Read/write to a table using arrays

I have read that that reading and writing data from a table (List Object) is more efficient using an array. I know how to save my table into an array, but I am not sure how to read from it and add/edit the data in it.

My table has an ID column and other properties listed in the other columns. Currently, I am searching the first column of my table for the ID and returning the row I then search for the property(string) I am looking for in the header and return the column number I then use both the row and column number to get the range which I can read/write

How do I do this using arrays and in the most efficient way possible? My table holds the information of shapes, which I drag/drop and updates frequently.

Solution

  • A Table (ListObject) Lookup

    enter image description here

    Option Explicit

Sub GetTableMatchTEST()

    Dim tbl As ListObject: Set tbl = Sheet1.ListObjects("Table1")
    
    Dim Result As Variant: Result = GetTableMatch(5, tbl, 1, "Name")
    If Not IsEmpty(Result) Then
        Debug.Print Result ' 6
    End If
    
End Sub

Function GetTableMatch( _
    ByVal LookupValue As Variant, _
    ByVal tbl As ListObject, _
    ByVal LookupColumnID As Variant, _
    ByVal MatchColumnID As Variant) _
As Variant
    
    If tbl Is Nothing Then Exit Function
    
    On Error Resume Next
    Dim lrg As Range: Set lrg = tbl.ListColumns(LookupColumnID).DataBodyRange
    Dim mrg As Range: Set mrg = tbl.ListColumns(MatchColumnID).DataBodyRange
    On Error GoTo 0
    
    If lrg Is Nothing Then Exit Function
    If mrg Is Nothing Then Exit Function

    Dim rIndex As Variant: rIndex = Application.Match(LookupValue, lrg, 0)
    If IsError(rIndex) Then Exit Function
    
    GetTableMatch = mrg.Cells(rIndex).Value
    
End Function