I would like to create a macro which go through all table of my worksheet and for each of them, create an array with the same name as the table and populate the array with value from the table. I started with this:
Dim tbl as ListObject
For Each tbl In Worksheets(Brand).ListObjects
But I don't see how can I proceed to create a array with my tbl.Name as name of array? is it possible?
You could use a collection:
Dim coll As New Collection
Dim ws As Worksheet
Dim lo As ListObject
For Each ws In ThisWorkbook.Worksheets
For Each lo In ws.ListObjects
coll.Add lo.DataBodyRange.Value, lo.Name
Next lo
Next ws
You can name an array with table's name as you add them to dictionary.
Dim iDict As Object
Dim tbl As ListObject
Set iDict = CreateObject("Scripting.Dictionary")
For Each tbl In ThisWorkbook.Worksheets("Brand").ListObjects
iDict.Add tbl.Name, tbl.DataBodyRange.Value
Next
Additionaly you can retrieve an array as
Dim vArr() : vArr = iDict(anytablename)
Dim iDict As New Scripting.Dictionary instead of Object and CreateObject.
Late Binding so no reference is needed
tbl[x][y]wherexis the table's index andyare the actual datarows for each and every table. Has the same functionality as naming separate ids and is actually easier to work with