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

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!

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

'--------------------------------------------------------------------------
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)



"NateBuckley" wrote in message
...
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!

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

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/Collec...ctionaries.htm


---
Regards.
Norman


"NateBuckley" wrote in message
...
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!