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

 

[RoryA]

It's actually using the range object as a key, not its address.

 

[shinigamilight]

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

Yea, I know that. That's why I'm not using .value suffix while entering data in dictionary. i was just trying to show you that you can enter duplicate values but it's not feasible to use it that way. If you will use .value suffix it will overwrite the previous value in the dictionary.

 

[Fluff]

Yea, I know that.

Then why did you say

you can add duplicate keys in the dictionary

 

[RoryA]

that you can enter duplicate values

Not as keys though.

 

[shinigamilight]

Then why did you say

Cuz u literally can add duplicate stuff in dictionary that's all my point was. You lose all the dictionary features by doing that but that was my whole point.

 

[RoryA]

No you can’t, that’s the point we are making.

 

[shinigamilight]

No you can’t, that’s the point we are making.

then why when i type dic.keys()(0) the keys show up one by one

 

[RoryA]

The key is the range object, as I said, not its contents. However, if you try and output it without specifying a property, you get its default member - its value. If the value were the key, you would only have two items in your code, and you would be able to access them using the value.

 

[shinigamilight]

The key is the range object, as I said, not its contents. However, if you try and output it without specifying a property, you get its default member - its value. If the value were the key, you would only have two items in your code, and you would be able to access them using the value.

Why are you bringing using .value to obtain a key? Everyone knows that. The duplicate values literally popup by using dic.keys()(0) using my method. that proves that keys are being stored in it. You can argue that they're "range object" but my point still stands.

 

[RoryA]

No, it doesn’t. You cannot have duplicate keys in a dictionary.

The values you are outputting as your “proof” are not the actual keys.