View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Nick Nick is offline
external usenet poster
 
Posts: 8
Default VBA Dictionary Trim to N smallest items

On Oct 21, 6:26*pm, "Jim Cone" wrote:
'This loads the 5 smallest numbers from a dictionary object
'back into the dictionary.
'--
Sub MakeSmaller()
'Jim Cone - Portland, Oregon USA - Oct 2008
Dim dic As Scripting.Dictionary
Dim N As Long
Dim M As Long
Dim howMany As Long
Dim arr As Variant

*Set dic = New Scripting.Dictionary
'Desired size of dictionary
*howMany = 5

'Load dictionary with 500 random numbers
*Randomize
*For N = 1 To 500
* * *M = Int(555555 * Rnd + 5000)
* * *dic.Add N, M
*Next

'Store all random numbers from dictionary in variant array.
*arr = dic.Items
*dic.RemoveAll

'Reload the dictionary with the "howmany" smallest numbers.
*For N = 0 To howMany - 1
* * *M = Application.Small(arr, N + 1)
* * *dic.Add N, M
* * 'Used for testing
* * *MsgBox dic.Items(dic.Count - 1)
*Next
*Set dic = Nothing
End Sub
--
Jim Cone
Portland, Oregon *USA



Thanks Jim, but that doesn't totally solve my problem.
For exmaple, say I have a dictionary and I want only the 3 smallest
Key Item
1 9
2 8
3 4
4 2
5 12
6 1
7 7
8 7

Your code would result in:
Key Item
0 1
1 2
2 4

When what I want is
Key Item
6 1
4 2
3 4