PDA

View Full Version : How to get to the single dictionary entry without knowing its key ro value?



p45cal
10-07-2009, 03:35 AM
I'm exploring the Dictionary object and have an array of them in myArray(x,y).
Some of them have only 1 entry - that is, their .Count is 1. How do I extract its value/or key?
At the moment I'm using a loop:
If myArray(1, 3).Count = 1 Then
For Each Key In myArray(1, 3)
zz = Key
Next Key
End If
Why? because when I try the likes of.. see picture for the rest.
1969
Any ideas?

ps.
Some code to set up the scenario above (have a single digit in cell A3 of the active sheet):
Sub sample()
Dim myArray(1 To 4, 1 To 4)
For i = 1 To 4
For j = 1 To 4
Set x = CreateObject("Scripting.Dictionary")
If Len(Cells(i, j).Value) = 1 Then
x.Add CInt(Cells(i, j).Value), CInt(Cells(i, j).Value)
Else
For k = 1 To 4
x.Add k, k
Next k
End If
Set myArray(i, j) = x
Next j
Next i
Stop
End Sub

p45cal
10-09-2009, 01:42 PM
Has no-one any suggestions?

Bob Phillips
10-09-2009, 02:13 PM
Pascal,

I have to admit that I am not understanding what the question is, what problem you are seeing?

p45cal
10-09-2009, 02:34 PM
xld,

a dictionary object might typically be used, well, as a dictionary; you know the word (key) you want to look up and then you use it so that you can see its definition.
What I've done is set up a dictionary (an array of them actually, but that's irrelevant to the question) with several entries, and elsewhere the code removes the entries in the dictionaries, one by one until just one is left in some or all of the dictionaries (that's why I suggest running that snippet of code I provided with just a single digit in cell A3 of the active sheet to have the dictionary in myArray(1,3) preloaded with just one entry).

Now in reality, I don't know the key or the value of that single dictionary entry that's left - but I'd like to!

In collections of things (sheets, cells, querytables and lots more) you can refer to a member using an index number (Sheets(3), Querytables(2) and so on) and if you know there's only one item in the collection, you can always refer to it with an index of 1 (Sheets(1), Querytables(1)).

I'm trying to do the same with the dictionary object.

Currently I loop through all the items in the dictionary (even though there's only one!) to get my information - which seems a bit silly.

Bob Phillips
10-09-2009, 05:46 PM
Okay I see what you mean.

Just a couple of points, you need to put 1 in the cell for your code, not just a single digit, and 1,3 is C1, not A3 (not that that alters the problem, just for anyone elese reading it).

So, doesn't this do it for you



If myArray(1, 3).Count = 1 Then MsgBox myArray(1, 3).Item(1)

p45cal
10-09-2009, 06:39 PM
Well, I did try
?myArray(1, 3).Item(1)
as mentoned in my original post, but it added an item. In your scenario it doesn't add an item because the 1 already exists (I'm guessing - because what business has it adding items when I'm asking it to print something?). In my case I won't know what the key/value will be, and if it's not a 1 it'll just add the 1 as an entry (key will be 1, value will be "" or empty).
So I'd like it to work, regardless of what the single digit in C1 is, 'cos at the moment I think it just seems to work if the value in C1 is 1.

I'm beginning to think I'll have to stick with looping through the single entry in the dictionary.

some code that may help:
Sub blah()
Set x = CreateObject("Scripting.Dictionary")
'set up a dictionary with 9 entries:
For k = 1 To 9
x.Add k, k
Next k
'remove all but 1 entry at random:
Do While x.Count > 1
y = Int(9 * Rnd + 1)
If x.exists(y) Then x.Remove y
Loop
Stop
MsgBox x.Item(1)
End Sub

Bob Phillips
10-10-2009, 03:44 AM
Now you are losing me again Pascal.

I fail to see the relevance of the value of C1 other that (as I assumed) it was just an artificial value required to force the scenario that you described.

If there is only one item in the collection, that fact that you do not know what the key is ireelevant is it not, you can interrogate the Count.

p45cal
10-10-2009, 04:39 AM
Let's forget C1. The code I supplied in post #6 was to simplify the question (by taking out the array of dictionaries aspect and forcing the dictionary to contain only one remaining entry - an unknown entry).
You will see that most times (when the remaining entry is not 1) that the line

MsgBox x.Item(1)

adds an item to the dictionary! (and doesn't reveal the single entry) (Watch the the Locals pane while stepping through after the Stop instruction.)

That oddity aside, I'm just looking for a way to find the value of the one remaining dictionary item (value or key, it doesn't matter, they'll be the same) without looping.