Dictionary Items

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
Hi...

I have built several items in my dictionary called Tdic.

TDic("MSL")
TDic("MS")
.
.
etc

If I wanted to know how many iems are in Tdic, I can do:
DicCount = Tdic.count

I'm having problems determining what name I gave at each item in Tdic.

I've tried:

TName = Tdic.Item(1) the result in Tname is Empty

There are a number of iterations that I tried to no avail receiving Complie erros and such.

Is there a way to extract the name of the item? Meaning can I loop through Tdic and load TName with whatever name is first in Tdic for the first loop, Whatever name is in Tdic for the second loop, etc.

Thanks
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Are you using late-binding? If so, then I believe you'll want to assign the .Keys or .Items collection to an array and retrieve specific elements from the array.
 

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
Thanks for responding, again, GTO...

Here's how I build the items:
Code:
Public Sub BuildTabArray(TabANameCol, TabAIndex)
 
    TabTag = Sheets("VBA Definitions").Cells(RowNumber, TabANameCol)
    TabIndex = Sheets("VBA Definitions").Cells(RowNumber, TabAIndex)
 
    If Tdic.Exists(TabTag) Then
        Tdic(TabTag) = TabIndex
       Else
        Tdic.Add TabTag, 0
        Tdic(TabTag) = TabIndex
    End If
End Sub

Later in my Code I want to loop through the items I defined and compare the name to a value in a series of cells. Once I have a match, I can then isue the index I have stored in a cell in the same row to build my various arrays.

Thanks again...
 

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
What I am looking to do after I have built my items is something like:
Code:
ii = 2
 
For I = 1 to Tdic.Count
[INDENT]If The name of Item 1 of Tdic = Sheets("Sheet1").cells(ii,3) then
(meaning, if the name of Item 1 (Value) i.e "MSL" = a the value in my cell)
[/INDENT][INDENT][INDENT]MyArray(Tdic(The Name I am pointed to)) = Values from Sheet1
(i.e. Tdic("MSL") would contain the Index number that I built earlier)
[/INDENT][/INDENT][INDENT]ii = ii + 1
[/INDENT]Next I
I hope this makes sense. Please let me know if not.

Thanks so very much...
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

Hi Rocky,

I am not sure that I am following, nor am I sure whether your are now getting to retrieve the various Keys/Items from the dictionary. By way of short example, in a new wb, setup like:
JunkTest

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">HDR</TD><TD style="TEXT-ALIGN: center">HDR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Sally</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Pedro</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Mark</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">0</TD><TD>Mike</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Rocky</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Bob</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">10</TD><TD>Peter</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Jon</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Anne</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">9</TD><TD>Tricia</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Rich (BB code):
Option Explicit
 
Sub CallIt()
Dim DIC As Dictionary
Dim RowNum As Long
Dim aryKeys As Variant
Dim aryItems As Variant
 
    Set DIC = New Dictionary
    For RowNum = 2 To 11
        Call BuildTabArray(1, 2, RowNum, DIC)
    Next
 
    aryKeys = DIC.Keys
    aryItems = DIC.Items
 
    For RowNum = LBound(aryKeys) To UBound(aryKeys)
        Debug.Print "Key: " & aryKeys(RowNum) & Space(4 - Len(aryKeys(RowNum))) & _
                    "contains the item: " & aryItems(RowNum)
    Next
End Sub
 
Public Sub BuildTabArray(TabANameCol, TabAIndex, RowNumber, Tdic)
Dim TabTag As Variant
Dim TabIndex As Variant
 
    TabTag = ThisWorkbook.Worksheets("JunkTest").Cells(RowNumber, TabANameCol).Value
    TabIndex = ThisWorkbook.Worksheets("JunkTest").Cells(RowNumber, TabAIndex).Value
 
    If Tdic.Exists(TabTag) Then
        Tdic(TabTag) = TabIndex
       Else
        Tdic.Add TabTag, 0
        Tdic(TabTag) = TabIndex
    End If
End Sub

Now if I am reading correctly, you are populating something like the above? If you run the above, you'll note that certain keys' items get overwritten. Is that what we want?
 

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
I think you got it GTO!!!!

Here's what I was looking for (I think):

For Each ItemKey In Tdic.Keys

MyTableArray(Tdic(ItemKey)) = Sheets("Sheet1").Cells(ii,4)
ii = ii +1
Next ItemKey

This is just and example not the actual code withing the For loop. The Value at Tdic(ItemKey) is the index into the array where I will load my data.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top