My spreadsheets use a lot of ListObjects (tables). This excellent article explains that importing data into an array is much faster than pulling it from the spreadsheet:
https://www.soa.org/News-and-Publica...s42-roper.aspx
I know I can make the array by saying:
Dim array1 as Variant
array1 = ActiveSheet.ListObjects(1).DataBodyRange
I'm sure that this concept will help me a lot if I can just resolve these issues:
1. One of the things I like about ListObjects is how I can refer to a column by name such as:
tblInfo.ListColumns("FirstName") ' This is better than a column number because I may change the columns around.
' But if I really need the column number, I can use:
tblInfo.ListColumns("FirstName").Index
Is there a way to do that in an array?
2. I can find a specific value in the table with something like this:
ID = InputBox("Enter ID Number")
WorkerName = WorksheetFunction.VLookup(ID, tblInfo.Range, 2, False)
Is there a way to do that in an array?
3. How much data can the array hold reasonably? I have some tables with 17,000 rows and about a dozen columns.
Bookmarks