Getting max value from a dictionary

[cblake843]

With the below code I am trying to simply get the max value of y and x after they have been added to a dictionary. The y and x values are integers and generated as part of a function which works fine. I had it working and then crashed without saving it , now I can't recall how it happened.
My code :-

When printing the max it simply takes the current value of y or x. When doing dictionary.count it's always 1 so doesn't appear to appending the data correcty to the dictionary


Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dict.Add y, x
For Each i In dict
Debug.Print i
Debug.Print application.max(i)
Next i

Tried the below but the max value is being overwritten can't see how or where


Dim key As Variant
Dim dict As Dictionary
Set dict = New Dictionary
dict.Add y, x
For Each key In dict.Keys
Debug.Print "Value X: " & dict.Item(key), "Max X:" & Application.Max(dict.Item(key))
Debug.Print "Key Y: " & key, "Max Y:" & Application.Max(key)
Next

[Paul_Hossler]

Assuming that the key is not the same as the data something like this



Option Explicit

Sub test()
Dim dict As Object
Dim i As Long, y As Long, x As Long, iMax As Long
Dim v As Variant

Set dict = CreateObject("Scripting.Dictionary")

' i is the Key, 10i is the Data
For i = 1 To 10
dict.Add i, 10 * i
Next

For Each v In dict.items
Debug.Print v
If v > iMax Then iMax = v
Next

MsgBox iMax
End Sub




or this if the key and the data are the same




Sub test2()

Dim dict As Object
Dim i As Long, y As Long, x As Long, iMax As Long
Dim v As Variant

Set dict = CreateObject("Scripting.Dictionary")

' i is the Key and the Data

For i = 1 To 10
dict.Add i, i
Next
For Each v In dict.items
Debug.Print v
Next
MsgBox Application.WorksheetFunction.Max(dict.keys)
End Sub

[cblake843]

Assuming that the key is not the same as the data something like this



Option Explicit

Sub test()
Dim dict As Object
Dim i As Long, y As Long, x As Long, iMax As Long
Dim v As Variant

Set dict = CreateObject("Scripting.Dictionary")

' i is the Key, 10i is the Data
For i = 1 To 10
dict.Add i, 10 * i
Next

For Each v In dict.items
Debug.Print v
If v > iMax Then iMax = v
Next

MsgBox iMax
End Sub




or this if the key and the data are the same




Sub test2()

Dim dict As Object
Dim i As Long, y As Long, x As Long, iMax As Long
Dim v As Variant

Set dict = CreateObject("Scripting.Dictionary")

' i is the Key and the Data

For i = 1 To 10
dict.Add i, i
Next
For Each v In dict.items
Debug.Print v
Next
MsgBox Application.WorksheetFunction.Max(dict.keys)
End Sub









Thanks , one point that does not work is that Y is already declared as an integer , when I replace i for y in your code I still don't get a max value only a current value.
What am I doing wrong ?

[cblake843]

I think I see what is happening now. The dictionary is only 1 line (row) , for some reason it is getting reset therefore it only contains one at a time. Anyway around this ?


BTW y is an integer from another fuction which contains values such as

30 , 60 , 90 , 120 , 200 but then can start again 20 , 40 , 80 etc. I want all this data in a dictionary (or container) and obtain the largest value.



Dim dict As Object
Dim i As Long, iMax As Long
Dim v As Variant


Set dict = CreateObject("Scripting.Dictionary")


For i = 1 To 2
dict.Add i, y
Next
For Each v In dict.Items
Debug.Print v
Next

Debug.Print "MAX :" & Application.WorksheetFunction.Max(dict.Item(1))


Debug.Print "MAX1 :"; dict.Items()(UBound(dict.Items))

[Aflatoon]

Application.Max(dict.Items())
and

Application.Max(dict.Keys())
should work.

[snb]

Schau mal erst: http://www.snb-vba.eu/VBA_Dictionary_en.html


Sub M_snb()
with createobject("scripting.dictionary")
for j=1 to 25
x0=.Item(j*20)
next

msgbox application.max(.keys)
end with
End Sub

[cblake843]

This doesn't work as the dictionary is being overwritten , so at any 1 time it only contains one value. My understanding is the dictionary should contain all values written to it. It's not clear why this is happening.

[cblake843]

The main issue is the dictionary only contains 1 value (row) , it appears to be overwritten on each iteration. I have no idea why this is doing it

[cblake843]

Sub M_snb() with createobject("scripting.dictionary")
for j=1 to 25
x0=.Item(j*20)
next

msgbox application.max(.keys)
end with End Sub.


Assignment to constant not permitted

[snb]

Please copy the code properly into your workbook

[cblake843]

Please copy the code properly into your workbook


which code exactly ?

[Aflatoon]

Given that you haven't posted the full code that actually populates your dictionary, it will be fairly difficult to fix that for you. ;)

[cblake843]

Given that you haven't posted the full code that actually populates your dictionary, it will be fairly difficult to fix that for you. ;)


Thanks for the reply and I see your point I will post the full code Monday! To summarize the posted code is within a sub where ‘y’ gets updated on each pass. Why it is not incrementally added to the dictionary but instead it overwrites the value. Should I set the dict in a spereate function or sub ! This is causing me a big headache!

[Aflatoon]

Yes you should. You're creating a new one at each iteration currently.

[cblake843]

Yes you should. You're creating a new one at each iteration currently.
Any coding hints how to do this? Create the dict in a separate sub or function ? And then add to it from the main routine?

[Paul_Hossler]

If you mean that there could be duplicate Keys and you only want one instance to that Key, with the largest Data then



Option Explicit

Sub test()
Dim dict As Object
Dim K As Long, D As Long, iMax As Long
Dim v As Variant

Set dict = CreateObject("Scripting.Dictionary")

' K is the Key, D is the Data
For K = 1 To 10
D = 1000 * Rnd
If dict.exists(K) Then
If D > dict(K) Then dict(K) = D
Else
dict.Add K, D
End If
Next

For Each v In dict.items
Debug.Print D
If v > iMax Then iMax = v
Next
MsgBox iMax
End Sub





This makes the index number the Key, so you can have the same Data



Sub test1()
Dim dict As Object
Dim K As Long, D As Long, iMax As Long
Dim v As Variant
Set dict = CreateObject("Scripting.Dictionary")
' K is the Key, D is the Data
For K = 1 To 1000
D = 1000 * Rnd
dict.Add CStr(dict.Count), D
'purposely adding duplicate data
dict.Add CStr(dict.Count), D
dict.Add CStr(dict.Count), D
dict.Add CStr(dict.Count), D
Next
For Each v In dict.items
Debug.Print v
If v > iMax Then iMax = v
Next
MsgBox iMax
End Sub




Maybe more details and an example would help

[cblake843]

So i created this function and is called from another sub

Call mydict(inputer, y)


When I try to access it I get errors sub or function not defined.

Debug.Print "KEY" & dict(Key)
Debug.Print "MAX" & Application.Max(Items


Public Function mydict(inputer, y)

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

dict.Add inputer, y

End Function




What's missing ?

[Aflatoon]

That's not going to help you as you're still creating a new dictionary each time, and your variable is local to the function.

I suggest you post your code that is trying to loop and populate the dictionary.

[cblake843]

Here is the main sub. I want to write the values of y on each iteration and store them in a dictionary to be accessed at the end of the sub.



Private Sub CalculateCoordinates(sThread, node As IXMLDOMNode)


Dim parent_node As IXMLDOMNode, x As Integer, y As Integer, y_max_branch As Integer, nList As IXMLDOMNodeList
Dim StepId As String, strXpath As String
Dim inputer, inputer2, inputer3, inputer4 As String
Dim stry As String


















*******************Call dict(inputer, y)


Call AddCoordinates(node, x, y)
End Sub


As mentioned I've tried a separate function but this does not work. Any suggestions are most appreicated

[snb]

Why do you ignore all apparently not so much appreciated suggestions in this thread ?

[cblake843]

Why do you ignore all apparently not so much appreciated suggestions in this thread ?

I haven't ignored any of them , in fact I have tried everything suggested yet I still have a dictionary that is overwritten each line and can't get a max value as a result.

The problem is where to create the dictionary so that I do not create a new one each time I write to it?

[cblake843]

Why do you ignore all apparently not so much appreciated suggestions in this thread ?

I have not ignored the suggestions , in fact I have tried each and everyone extensively without success

[Aflatoon]

There is no looping at all in what you posted - why did you remove it? It makes it much harder to help you if you won't post your code. Declare and instantiate the dictionary before whatever your loop is, then add the items to it inside the loop.

[cblake843]

There is no looping at all in what you posted - why did you remove it? It makes it much harder to help you if you won't post your code. Declare and instantiate the dictionary before whatever your loop is, then add the items to it inside the loop.

Thanks for the reply , where should I declare and insttantiate the dictionary exactly ? If i declare it like this :-




Public Sub collz()
Dim dict As Dictionary
Set dict = New Dictionary
End Sub


The sub where I want to write to it and access



dict.Add inputer, y says object not defined etc

[Aflatoon]

Declare it in the routine with the loop that populates it.

[cblake843]

Why do you ignore all apparently not so much appreciated suggestions in this thread ?

But is the whole problem ,it then creates a new dictionary each time where it only retains 1 line. It is then not possible to get a max value with only one line

[snb]

Da Capo al Fine:


Sub M_snb()
with createobject("scripting.dictionary")
for j=1 to 25
x0=.Item(j*20)
next

msgbox application.max(.keys)
end with
End Sub

[Paul_Hossler]

But is the whole problem ,it then creates a new dictionary each time where it only retains 1 line. It is then not possible to get a max value with only one line

Just taking a wild guess here, I'd suspect that you're doing something wrong


My post 16 has two different approaches depending on what you're trying to do


Both will add new lines to the dictionary and both will print out the max


If you can't get it to work, you'd better post a sample workbook with whatever code you have and a description of what it is you are trying to accomplish

[cblake843]

Da Capo al Fine:


Sub M_snb()
with createobject("scripting.dictionary")
for j=1 to 25
x0=.Item(j*20)
next

msgbox application.max(.keys)
end with
End Sub

I’ve already tried something similar(see previous post) , I’m trying to write the value of y to a dict? Currently it overwrites the dict so there is only 1 line in the dict. All I need to do is create the dict in another sub but can’t quite get the code working. Any suggestions ?

[cblake843]

Just taking a wild guess here, I'd suspect that you're doing something wrong


My post 16 has two different approaches depending on what you're trying to do


Both will add new lines to the dictionary and both will print out the max


If you can't get it to work, you'd better post a sample workbook with whatever code you have and a description of what it is you are trying to accomplish

Yeah thanks, it was very good example by didn’t work for my issue. How can I declare the dict outside of the sub so it doesn’t just write one line overwriting each time?

[Paul_Hossler]

Did you try making it a Module Level variable?

This version keeps a max number of keys = 10

If you want to keep on adding, then use the second approach in my #16


Option Explicit


'module level variable
Dim dict As Object

Sub test()
Dim v As Variant
Dim iMax As Long
If dict Is Nothing Then
Set dict = CreateObject("Scripting.Dictionary")
End If

Call test2(10)
Call test2(12)
Call test2(15)

For Each v In dict.items
If v > iMax Then iMax = v
Next
MsgBox iMax

Set dict = Nothing

End Sub



Private Sub test2(N As Long)

Dim K As Long, D As Long

' K is the Key, D is the Data
For K = 1 To 10
D = N * Rnd
If dict.exists(K) Then
If D > dict(K) Then dict(K) = D
Else
dict.Add K, D
End If
Next
End Sub

[rlv]

Did you try making it a Module Level variable?

fyi, related:

http://www.vbaexpress.com/forum/showthread.php?64151-VBA-save-a-variable-when-criteria-is-met&p=386060&viewfull=1#post386060

[cblake843]

Did you try making it a Module Level variable?

This version keeps a max number of keys = 10

If you want to keep on adding, then use the second approach in my #16


Option Explicit


'module level variable
Dim dict As Object

Sub test()
Dim v As Variant
Dim iMax As Long
If dict Is Nothing Then
Set dict = CreateObject("Scripting.Dictionary")
End If

Call test2(10)
Call test2(12)
Call test2(15)

For Each v In dict.items
If v > iMax Then iMax = v
Next
MsgBox iMax

Set dict = Nothing

End Sub



Private Sub test2(N As Long)

Dim K As Long, D As Long

' K is the Key, D is the Data
For K = 1 To 10
D = N * Rnd
If dict.exists(K) Then
If D > dict(K) Then dict(K) = D
Else
dict.Add K, D
End If
Next
End Sub













Thanks , I can see how it will work but not for my issue. I want the dictionary to be populated by the below sub and also accessed each pass in the same sub. I need it to take the value of y from the callcoordinates sub below




Private Sub CalculateCoordinates(sThread, node As IXMLDOMNode)


Dim parent_node As IXMLDOMNode, x As Integer, y As Integer, y_max_branch As Integer, nList As IXMLDOMNodeList
Dim StepId As String, strXpath As String
Dim inputer, inputer2, inputer3, inputer4 As String
Dim stry As String




Call AddCoordinates(node, x, y)
End Sub

[Paul_Hossler]

Thanks , I can see how it will work but not for my issue. I want the dictionary to be populated by the below sub and also accessed each pass in the same sub. I need it to take the value of y from the callcoordinates sub below


I'm struggling to see why it would NOT work

Again, if you attach a sample workbook showing what you start with and what you want to do it will be easier

[cblake843]

So for the routine I posted in 33 , where should i define the dicitonary (i.e not in the SUB as it creates a new one each time) and where to write to it and where to access it? When I define the dictionary outside of the sub , i get errors like object required etc etc . I can't post the whole workbook.

[Paul_Hossler]

So for the routine I posted in 33 , where should i define the dicitonary (i.e not in the SUB as it creates a new one each time) and where to write to it and where to access it? When I define the dictionary outside of the sub , i get errors like object required etc etc . I can't post the whole workbook.

1. Yes
2. Use CreateObject() in the right place to create a single instance of dict
3. Make a small sample workbook with your macros and some sample data that does show the problem and post that