Check a collection for objects that may or may not exist within it

[NateBuckley]

Hello,

I'm just wondering if it's possible to check to see if something exists
within a collection before proceeding, I can do it with putting various "On
error goto" statements, but I was just wondering if it's be possible to do
something like the following without having to send the process jumping to
labels.

if aCollection.Item("aKey") <> null then
'do something special
else
Msgbox "Yar! No Object 'ere!"
end if

I know that doesn't work, but perhaps there is a way like it?

Thanks in advance!

 

[Bob Phillips]

'--------------------------------------------------------------------------
Public Function ExistsInCollection(pColl, ByVal pKey As String) As Boolean
'--------------------------------------------------------------------------
On Error GoTo NoSuchKey
If VarType(pColl.Item(pKey)) = vbObject Then
' force an error condition if key does not exist
End If
ExistsInCollection = True
Exit Function

NoSuchKey:
ExistsInCollection = False
End Function


an example of usage.

Check if a value in a column is equal to one of a number of values in a
column in another sheet, if so, delete it

Sub testExistsInCollection()
Dim iLastRow As Long
Dim i As Long
Dim colWords As Collection

Set colWords = New Collection

With Worksheets("Sheet2")
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value <> "" Then
colWords.Add i, .Cells(i, "A").Value
End If
Next i
End With

With Worksheets("Sheet1")
iLastRow = .Cells(Rows.Count, "L").End(xlUp).Row
For i = iLastRow To 1 Step -1
If ExistsInCollection(colWords, .Cells(i, "L").Value) Then
.Rows(i).Delete
End If
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Norman Jones]

Hi Nate,

In addition to Bob's response, you may
wish to consider replacing your Collection
with a scripting Dictionary.

In many cases, a Dictionary is preferable
to a collection and,with reference to your
specific question, it has an Exists method.

For more information on Dictionaries and
Collections. see Chip Pearson at:

http://www.cpearson.com/excel/CollectionsAndDictionaries.htm