Add Items to late-bound SortedList in VBA

Question

I'm familiar with using Dictionaries in VBA, but I wanted to try out a SortedList. I read through the msdn doc for it and thought it uses the same .Add structure as the Dictionary.

I've been struggling to actually populate the SortedList. I'm not sure how what I'm doing is any different from this answer from How to make a list in VBA (not a dictionary)?. I'd VTC my own question as Duplicate, but somehow it's not the same thing.

I'm using a simple list:

And this is the Code -

Option Explicit

Sub testing()

    Dim sortList As Object
    Dim dict As Object
    Set sortList = CreateObject("System.Collections.SortedList")
    Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long
    Dim key As Variant
    Dim item As Variant

    For i = 1 To 7
        key = Sheet1.Cells(i, 1)
        item = Sheet1.Cells(i, 2)
        dict.Add key, item
        sortList.Add key, item
    Next

End Sub

I even went the extra step (just in case) and made everything Variant as I know the SortedList wants Objects.

I can't seem to retrieve anything from the SortList

What am I missing?

I'm trying to retrieve it the way I'd retrieve a dictionary

For Each key In sortList.keys
    Sheet1.Cells(i, 6) = key
    Sheet1.Cells(i, 7) = sortList.item(key)
Next

Don't use the watch list to check the content - it doesn't show up there... — Tim Williams, May 21 '18 at 05:34
Fair enough, I did try printing it back out which is where it failed, essentially telling me there was no key. Maybe my print method is wrong, I'll post that. — Raystafarian, May 21 '18 at 05:35

Raystafarian · Answer 1 · 2018-05-21T05:52:19.637

0

I'm getting the keys and items the wrong way out of the SortedList

For i = 0 To sortList.Count - 1
    key = sortList.getkey(i)
    item = sortList.item(key)
    Sheet1.Cells(i + 1, 6) = key
    Sheet1.Cells(i + 1, 7) = item
Next

Different than in a Dictionary -

i = 1
For Each key In dict
    Sheet1.Cells(i, 6) = key
    Sheet1.Cells(i, 7) = dict(key)
    i = i + 1
Next

edited May 21 '18 at 05:52

answered May 21 '18 at 05:49

Raystafarian

2,902
2
29
42

1

`For i = 0 To sortList.Count - 1` would be more general usable. – Axel Richter May 21 '18 at 05:51
You are absolutely correct. I have always hated `For Each` loops with dictionaries. – Raystafarian May 21 '18 at 05:53
@Raystafarian It is possible to loop through dictionary using index, though you must use early binding. – AntiDrondert May 21 '18 at 06:52
2

@AntiDrondert You can loop through a late bound dictionary using the Keys index. The trick is to access the dictionary's Keys array. `Key = dict.Keys()(i)` – May 21 '18 at 08:15
@thomas news to me too! – Raystafarian May 21 '18 at 22:01

Add Items to late-bound SortedList in VBA

1 Answers1