CODEWITHSUNDEEP
arraysexcelvba
vib500
vib500

Reputation: 91

Create array from excel table name

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?

Upvotes: 0

Views: 531

Answers (2)

Olly
Olly

Reputation: 7891

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

Upvotes: 1

AntiDrondert
AntiDrondert

Reputation: 1149

Use a dictionary

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)

Upvotes: 1

Related Questions