Unknown number of tables

[JenniferMurphy]

I am working on a macro that will calculate overall product ratings based on my ratings of various properties of the products that are available. I am in the market for an electric car, so I thought it would be a good test.

Most of the EV properties are numerical, such as price, miles/kWh, range, horsepower, headroom, and legroom. For numerical properties, all I need is to know whether higher numbers are better (range, headroom, star ratings), or lower numbers are (price, availability, rankings).

Several properties are not numerical, like body style (sedan, SUV, ...) and drive (4WD, AWD, RWD, FWD). And some numerical properties, like number of doors, are not linear. For properties that are not numerical and linear, I create "helper" tables that convert the options to linear (and proportional) numbers. Here are a few examples:

Weighted Ratings.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
4	Body Type	Rtg	Comments		Drive	Rtg		Heater	Rtg		Doors	Rtg
5	Sedan	10	4 doors, traditional trunk		4WD	5		Heat Pump	3		5	5
6	SUV	7			AWD	4		Resistive	1		6+	4
7	MPV	7	Multi-purpose vehicle		RWD	3					4	2
8	Hatch	7	Hatchback		FWD	1					2	0
EV Tables

The macro will use these tables to convert non-numerical and non-linear values to linear numerical values that can be aggregated with the other properties.

The problem is how to handle these in the macro. The names of the tables are not fixed so I can't declare them. I need some sort of dynamically variable way of accessing the tables. It is my understanding that VBA supports arrays of arrays (also called "jagged" arrays). Can I declare a jagged array then load these tables into it? I have never done this before, so before I try it, I thought I'd ask if this is the way to go or if there is a better way. I would appreciate any hints or tips on how to do it.

Thanks

 

[DanteAmor]

Try:

Sub Loop_Tables()
  Dim sh As Worksheet
  Dim tbl As ListObject
  
  Set sh = Sheets("Ev Tables")
  
  For Each tbl In sh.ListObjects
    
    'Here you can do what you need using the tbl object, for example:
    tbl.DataBodyRange.Select
  
  Next
  
End Sub

 

[JenniferMurphy]

Try:

Sub Loop_Tables()
  Dim sh As Worksheet
  Dim tbl As ListObject
 
  Set sh = Sheets("Ev Tables")
 
  For Each tbl In sh.ListObjects
   
    'Here you can do what you need using the tbl object, for example:
    tbl.DataBodyRange.Select
 
  Next
 
End Sub

There are a couple of details that I am not sure of.

• "Ev Tables" is the name of the sheet containing the helper tables, right?
• Is "DataBodyRange" the name of one of the tables or is that a keyword?
• What exactly does the "tbl.DataBodyRange.select" statement do?
• How do I load one of the tables into a VBA array?
• Does this code assume that the Ev Tables sheet contains only helper tables and no other objects?
• What code would I need if I wanted to load just one table, say, "TblHeater" or TblDoors"?

Thanks for this help. It looks like it will do what I need if I can only get my head around it.

 

[DanteAmor]

I will try to answer everything:

"Ev Tables" is the name of the sheet containing the helper tables, right?

Right
-------------------------------

Is "DataBodyRange" the name of one of the tables or is that a keyword?

DatabodyRange is a part of a table

-------------------------------

What exactly does the "tbl.DataBodyRange.select" statement do?

Select body of table

Sub Loop_Tables()
  Dim sh As Worksheet
  Dim tbl As ListObject
  Dim tbname As String
 
  Set sh = Sheets("Ev Tables")
 
  For Each tbl In sh.ListObjects
  
    'select Body
    tbl.DataBodyRange.Select
  
    'get table name
    tbname = tbl.Name
  
  Next
End Sub

-------------------------------

How do I load one of the tables into a VBA array?

For example:

Sub test2()
  Dim sh As Worksheet
  Dim tbl As ListObject
  Dim arrtbls As New Collection
  Dim itm As Variant
 
  Set sh = Sheets("Ev Tables")
 
  For Each tbl In sh.ListObjects
    arrtbls.Add tbl.Name
  Next
 
  For Each itm In arrtbls
    MsgBox itm
  Next

End Sub

-------------------------------

Does this code assume that the Ev Tables sheet contains only helper tables and no other objects?

Only listobjects (tables)

-------------------------------

What code would I need if I wanted to load just one table, say, "TblHeater" or TblDoors"?

Sub test1()
  Dim sh As Worksheet
  Dim tbl As ListObject
 
  Set sh = Sheets("Ev Tables")
 
  Set tbl = sh.ListObjects("TblHeater")
 
  'select all table
  tbl.Range.Select
End Sub

-------------------------------


----- --

 

[Alex Blakenburg]

Just a thought. The key values in all your tables look to be Unique across all tables, the only one that is worrying is the Doors table.
Also all the tables are Col1 Lookup value, Col2 rating.
Is it worth considering loading them all into a dictionary with key=col1 and value=col2 ?
Presumably you are planning to do a lookup to get the rating.

Without a more code & making it more complex than it needs to be, I don't believe there is an efficient way to load all the tables in a single read statement. Looping through the table per Dante's suggestion would be the way to go.