3

I have a collection with several elements in VBA, each of these elements has a key assigned.

I would like to know the position of a given element in the collection using its key. The only way I can think of doing it is to make a loop through the collection until I get the item I want, is there a faster approach?

Thanks

jalopez910
  • 69
  • 1
  • 1
  • 5

4 Answers4

7

Essentially, no. There's not a faster way. Not using a collection at least. Here's the fastest way I know to find the index of an item in a collection.

' returns index of item if found, returns 0 if not found
Public Function IndexOf(ByVal coll As Collection, ByVal item As Variant) As Long
    Dim i As Long
    For i = 1 To coll.Count
        If coll(i) = item Then
            IndexOf = i
            Exit Function
        End If
    Next
End Function

Note that we exit the function as soon as we've found the first matching item. This algorithm has an average and worst case of O(n) where n is the number of items in the collection.

I think there may be another reason you want to be able to find the index though, so I'm going to suggest also taking a look at the Scripting.Dictionary in the Microsoft Scripting Runtime library. While, there is also no way to get an item's index via it's key value (other than looping), you can actually retrieve the key values from the dictionary, not just it's items. I suspect that you may be asking this question because of the lack of this ability in the built in Collection object.

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
  • 2
    This is a fine solution. Please note if you have objects stored, then VBA requires `is`, so the code line becomes `If coll(i) is item Then`. – peter_the_oak Sep 29 '16 at 07:08
  • 1
    Both solution and @peter_the_oak's comment are fine, thank you both. To add 0.02$, it is easily implemented as a: `Select Case VarType(coll(i)) Case vbObject If coll(i) Is item Then ... Case Else ... If coll(i) = item Then ...` – Marcelo Scofano Diniz Apr 02 '20 at 20:19
0

I recently had the same "issue" and used this workaround:

Create two collections: one containing the actual strings of interest and one containing numbers from 1 to n with the actuals strings as keys. This way you can retrieve the index since it's a value in the second collection.

Sub example()

    'imagine we are reading out following excel column

    'one
    'two
    'three

    Dim textcol, numcol As New Collection
    Dim i, index As Integer
    Dim str As String

    For i = 1 To Range.Rows.Count
        textcol.Add Range.Cells(i, 1)       'value, no key
        numcol.Add i, Range.Cells(i, 1)     'i, value as key
    Next i

    'textcol: "one", "two", "three"
    'numcol: 1 ["one"], 2 ["two"], 3 ["three"]

    str = "two"
    index = numcol(str) '~> 2

End Sub

It's not pretty but it worked for me.

Sil
  • 1
0

I liked Sil's answer above. I think he short changed himself by saying 'Its not pretty but it works'. In my experience it is way faster when doing a large number of lookups. Here is another example

Sub UseCollectionWithKey()
Dim Col As New Collection
Dim I As Integer

' create a new collection that is keyed
For I = 1 To ActiveWorkbook.Styles.Count
  Col.Add I, ActiveWorkbook.Styles(I)   ' where "ActiveWorkbook.Styles(I)" is the key
Next I

MsgBox Col(ActiveCell.Style)    ' this gives the index of the style for the active cell

End Sub

See also "Adding Items Using a Key" at this link https://excelmacromastery.com/excel-vba-collections/

cweedon
  • 3
  • 2
  • 1
    Is this an answer? – Vega Mar 29 '18 at 13:31
  • @vega Yes it answers the original question (I would like to know the position of a given element in the collection using its key.) by giving an example where the position of an element can be retrieved using a key without looping through the collection each time. I am new to posting on this forum so I would appreciate any feed back. – cweedon Mar 30 '18 at 14:24
  • Are you sure this works? I get a type mismatch error with an Integer type as the Item. – Colm Bhandal Mar 21 '19 at 19:40
-2

There is no way that I have found to return the index, however if you have a collection of user defined objects that will only be stored in a single collection you can add an index property to the object and set it when the object is added to the collection.

nomad
  • 1
  • 1