Sort collection string keys

[John]

Hi there,

I've got a collection that I want to sort and put into a listbox. The
collection is a series of cell references (delimited with a comma) and each
item has a string key on which I want to sort.

So how can I run through my collection to pull out all the index keys?

Thanks in advance

Best regards

John

[Tom Ogilvy]

to the best of my knowledge, you can't retrieve the index keys.

--
Regards,
Tom Ogilvy

"John" <[email protected]> wrote in message
news:%[email protected]...
> Hi there,
>
> I've got a collection that I want to sort and put into a listbox. The
> collection is a series of cell references (delimited with a comma) and
each
> item has a string key on which I want to sort.
>
> So how can I run through my collection to pull out all the index keys?
>
> Thanks in advance
>
> Best regards
>
> John
>
>

[John]

Thanks Tom. In that case I'd better go back to where the collection gets
built add an duplicate array I suppose.

Thanks again

Best regards

John
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> to the best of my knowledge, you can't retrieve the index keys.
>
> --
> Regards,
> Tom Ogilvy
>
> "John" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi there,
>>
>> I've got a collection that I want to sort and put into a listbox. The
>> collection is a series of cell references (delimited with a comma) and
> each
>> item has a string key on which I want to sort.
>>
>> So how can I run through my collection to pull out all the index keys?
>>
>> Thanks in advance
>>
>> Best regards
>>
>> John
>>
>>
>
>

[Tom Ogilvy]

in tools => references, set a reference to the Microsoft Scripting Runtime
library. this will give you access to the dictionary object which does have
a Key property. By setting the reference, you will be able to look at its
properties in the object browser.


here is sample code from MSDN. The use of createObject is an example of
late binding so you don't have to create the reference to use the
dictionary. My recommendation was to do it during development so you would
have access to the object browser.

Dim d ' Create a variable.
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "Athens" ' Add some keys and items.
d.Add "b", "Belgrade"
d.Add "c", "Cairo"


Check if it exists


If d.Exists("c") Then
msg = "Specified key exists."
Else
msg = "Specified key doesn't exist."
End If


Use items


a = d.Items ' Get the items.
For i = 0 To d.Count -1 ' Iterate the array.
s = s & a(i) & "<BR>" ' Create return string.
Next


Use keys


a = d.Keys ' Get the keys.
For i = 0 To d.Count -1 ' Iterate the array.
s = s & a(i) & "<BR>" ' Return results.
Next


--
Regards,
Tom Ogilvy

"John" <[email protected]> wrote in message
news:%[email protected]...
> Thanks Tom. In that case I'd better go back to where the collection gets
> built add an duplicate array I suppose.
>
> Thanks again
>
> Best regards
>
> John
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > to the best of my knowledge, you can't retrieve the index keys.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "John" <[email protected]> wrote in message
> > news:%[email protected]...
> >> Hi there,
> >>
> >> I've got a collection that I want to sort and put into a listbox. The
> >> collection is a series of cell references (delimited with a comma) and
> > each
> >> item has a string key on which I want to sort.
> >>
> >> So how can I run through my collection to pull out all the index keys?
> >>
> >> Thanks in advance
> >>
> >> Best regards
> >>
> >> John
> >>
> >>
> >
> >
>
>

[John]

Hi Tom,

This is great. I've not used the dictionary object before, so I'll have a
go.

Thanks very much for the help.

Best regards

John
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> in tools => references, set a reference to the Microsoft Scripting Runtime
> library. this will give you access to the dictionary object which does
> have
> a Key property. By setting the reference, you will be able to look at its
> properties in the object browser.
>
>
> here is sample code from MSDN. The use of createObject is an example of
> late binding so you don't have to create the reference to use the
> dictionary. My recommendation was to do it during development so you
> would
> have access to the object browser.
>
> Dim d ' Create a variable.
> Set d = CreateObject("Scripting.Dictionary")
> d.Add "a", "Athens" ' Add some keys and items.
> d.Add "b", "Belgrade"
> d.Add "c", "Cairo"
>
>
> Check if it exists
>
>
> If d.Exists("c") Then
> msg = "Specified key exists."
> Else
> msg = "Specified key doesn't exist."
> End If
>
>
> Use items
>
>
> a = d.Items ' Get the items.
> For i = 0 To d.Count -1 ' Iterate the array.
> s = s & a(i) & "<BR>" ' Create return string.
> Next
>
>
> Use keys
>
>
> a = d.Keys ' Get the keys.
> For i = 0 To d.Count -1 ' Iterate the array.
> s = s & a(i) & "<BR>" ' Return results.
> Next
>
>
> --
> Regards,
> Tom Ogilvy
>
> "John" <[email protected]> wrote in message
> news:%[email protected]...
>> Thanks Tom. In that case I'd better go back to where the collection gets
>> built add an duplicate array I suppose.
>>
>> Thanks again
>>
>> Best regards
>>
>> John
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > to the best of my knowledge, you can't retrieve the index keys.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "John" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> Hi there,
>> >>
>> >> I've got a collection that I want to sort and put into a listbox. The
>> >> collection is a series of cell references (delimited with a comma) and
>> > each
>> >> item has a string key on which I want to sort.
>> >>
>> >> So how can I run through my collection to pull out all the index keys?
>> >>
>> >> Thanks in advance
>> >>
>> >> Best regards
>> >>
>> >> John
>> >>
>> >>
>> >
>> >
>>
>>
>
>