Can I load a 2-column table into a VBA Dictionary with a single instruction?

[JenniferMurphy]

Suppose I have a table like this one in a sheet.

Weighted Ratings.xlsm
	C	D
7	Product	Part#
8	A	K42-X3
9	B	M21-44
10	C	DL402
11	D	MD3-902
12	E	GT35
MrExcel

Can I load it into a VBA Dictionary or Collection object so that I can search on the key (Product) and get back the value (Part#)?

Or do I need to do it in a loop one row at a time?

Sub DictTest()

Const rnTable As String = "Table16"
Dim NumRows As Long
NumRows = Range(rnTable).Rows.Count
Debug.Print NumRows & " rows"

Dim DictTest As New Dictionary
Dim i As Long
For i = 1 To NumRows
  DictTest.Add Range(rnTable).Cells(i, 1).Value, Range(rnTable).Cells(i, 2).Value
Next i

For i = 1 To NumRows
  Debug.Print "Part# for product " & Range(rnTable).Cells(i, 1).Value _
        & " is " & Range(rnTable).Cells(i, 2).Value
Next i

End Sub

Thanks

 

[Zot]

Dictionary can only be populated one by one as far as I know

 

[JenniferMurphy]

Dictionary can only be populated one by one as far as I know

That's what I was afraid of. Seems like a very natural and obvious extension, but then it's VBA.

 

[Peter_SSs]

Seems like a very natural and obvious extension

Not to me. Since a dictionary Key can only have one Item associated with it, a dictionary created from the data below would only have 3 Keys, not one per row, and it seems best to me to have the user decide whether to load from top to bottom or bottom to top or perhaps find the longest string for each key (& have a decision method for ties) or one of a number of other ways.

JenniferMurphy.xlsm
	C	D
7	Key	Item
8	A	z
9	B	yy
10	A	xxx
11	C	w
12	A	v
13	B	uu
Sheet2

 

[JenniferMurphy]

Not to me. Since a dictionary Key can only have one Item associated with it, a dictionary created from the data below would only have 3 Keys, not one per row, and it seems best to me to have the user decide whether to load from top to bottom or bottom to top or perhaps find the longest string for each key (& have a decision method for ties) or one of a number of other ways.

Hmmm...

Using that logic, VBA should not allow any instructions that could be invalid. I think we'd be back to BAL.

VBA is already perfectly capable of popping up cryptic error messages if I try to subscript something that is not an array, or if try to do arithmetic on non-numeric data, and so on. It could easily lay down the law for what is and is not an acceptable table and generate an error if those conditions are not met. Your example would get this error message, which I just triggered by trying to add a key already in the dictionary.

VBA is eons behind the times, but, as we know, that is not going to change.

 

[Peter_SSs]

Your example would get this error message, ..

My example was of data, not code, so it wouldn't get any error message. Perhaps some code you tried with my data produced an error message. If so, that would be an issue with the code not the data.

.. error message, which I just triggered by trying to add a key already in the dictionary.

I wouldn't be trying to add a key that was already in the dictionary.

 

[JenniferMurphy]

My example was of data, not code, so it wouldn't get any error message. Perhaps some code you tried with my data produced an error message. If so, that would be an issue with the code not the data.


I wouldn't be trying to add a key that was already in the dictionary.

One of us is not paying attention. My point was that being able to load a table into a Dictionary in a single statement would not only be simpler and clearer, but more efficient.

Your example was a table with duplicate entries (Keys) in Column C. I thought your point there should not be a load table option because tables like yours could not be loaded because of the duplicate keys (or multiple values for some keys, which is the same thing).

My response was that if the Dictionary object was implemented properly (IMO), there would be a load table option and your data would get an error. Your example data is not a compelling argument against the table load option. All functions have limits, If the limits are exceeded, error messages are generated.

 

[RoryA]

A Dictionary is not part of VBA. It is part of a library (the scripting runtime) available to VBA.

 

[shinigamilight]

One of us is not paying attention. My point was that being able to load a table into a Dictionary in a single statement would not only be simpler and clearer, but more efficient.

Your example was a table with duplicate entries (Keys) in Column C. I thought your point there should not be a load table option because tables like yours could not be loaded because of the duplicate keys (or multiple values for some keys, which is the same thing).

My response was that if the Dictionary object was implemented properly (IMO), there would be a load table option and your data would get an error. Your example data is not a compelling argument against the table load option. All functions have limits, If the limits are exceeded, error messages are generated.

Loading stuff in dictionary is not the same as loading into a variant array. Because when you load into an dictionary you're trying to associate an item with a key, that's why you've to explicitly define everything. Dictionary doesn't assume the column parallel to it is its item, you explicitly tell that to the dictionary. And you can add duplicate keys in the dictionary but the only problem is that you will not be able to retrieve the item, it'll just show up blank in the item.

 

[Fluff]

@shinigamilight You cannot have duplicate keys in a dictionary. Your code is loading the range address into the dictionary not the cell value.