efficiency qn: search collection or use on error?
 

Hi,

Is it more efficient to explicitly search a collection for an entry
i.e. :

function elementExists(myCollection as collection, myName as string) as
boolean
elementExists = false
for each element in myCollection
if element.name = myName
elementExists = true
exit function
end if
next
end function

*OR* whether it is faster/better to use the name as a collection parameter
to call an item and catch any exceptions? (I'm not familiar with the code
used to implement the latter, but surely it must perform the same kind of
operation as the former...?)

Thanks all

efficiency qn: search collection or use on error?
 

a Scripting Dictionary is faster then a collection.

using a test on key (with on error) is MUCH faster then for each
(even with the dictionary which has a KeyExists method


For a collection following should work quite efficiently.

Function elementExists(myCollection As Collection, myKey As String) As
Boolean
On Error Resume Next
elementExists = (VarPtr(myCollection(myKey)) 0)
End Function



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


noel mc wrote :

Hi,

Is it more efficient to explicitly search a collection for an entry
i.e. :

function elementExists(myCollection as collection, myName as string)
as boolean
elementExists = false
for each element in myCollection
if element.name = myName
elementExists = true
exit function
end if
next
end function

*OR* whether it is faster/better to use the name as a collection
parameter to call an item and catch any exceptions? (I'm not familiar
with the code used to implement the latter, but surely it must
perform the same kind of operation as the former...?)

Thanks all

efficiency qn: search collection or use on error?
 

Use the key property

MyCollection.Add item:=MyName, key:=MyName

Key must be a string.

then
On error resume next
set obj = MyCollection(MyName)
On error goto 0
if obj is nothing then
msgbox MyName & " not found"
End if

--
Regards,
Tom Ogilvy


"noel mc" <noel wrote in message
...
Hi,

Is it more efficient to explicitly search a collection for an entry
i.e. :

function elementExists(myCollection as collection, myName as string) as
boolean
elementExists = false
for each element in myCollection
if element.name = myName
elementExists = true
exit function
end if
next
end function

*OR* whether it is faster/better to use the name as a collection parameter
to call an item and catch any exceptions? (I'm not familiar with the code
used to implement the latter, but surely it must perform the same kind of
operation as the former...?)

Thanks all

efficiency qn: search collection or use on error?
 

Tom..

your code will only return true
if the collection contains objects..

if the collection's items contains values (or arrays)
it will always return false.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

Use the key property

MyCollection.Add item:=MyName, key:=MyName

Key must be a string.

then
On error resume next
set obj = MyCollection(MyName)
On error goto 0
if obj is nothing then
msgbox MyName & " not found"
End if

efficiency qn: search collection or use on error?
 

OK, here is a better example:

Sub ABCD()
Dim myCollection As New Collection
Dim vArr(1 To 10) As String
Dim sStr As String, sSTr1 As String
For i = 1 To 10
sStr = ""
sSTr1 = ""
For j = 1 To 4
sStr = sStr & Chr(Int(Rnd() * 26 + 65))
sSTr1 = sSTr1 & Chr(Int(Rnd() * 26 + 65))
Next
myCollection.Add sStr, sStr
If Rnd() < 0.5 Then
vArr(i) = sStr
Else
vArr(i) = sSTr1
End If
Next
For i = 1 To 10
Debug.Print i, vArr(i), elementExists(myCollection, vArr(i))
Next
End Sub

Function elementExists(myCollection As Collection, myName As String) As
Boolean
On Error Resume Next
elementExists = (myName = myCollection(myName))
End Function

of course this doesn't do objects, but string seemed to be the type anyway.

--
Regards,
Tom Ogilvy


"keepITcool" wrote in message
ft.com...
Tom..

your code will only return true
if the collection contains objects..

if the collection's items contains values (or arrays)
it will always return false.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

Use the key property

MyCollection.Add item:=MyName, key:=MyName

Key must be a string.

then
On error resume next
set obj = MyCollection(MyName)
On error goto 0
if obj is nothing then
msgbox MyName & " not found"
End if

efficiency qn: search collection or use on error?
 

use the VarPtr method in my example.
afaik that works in all cases.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

OK, here is a better example:

Sub ABCD()
Dim myCollection As New Collection
Dim vArr(1 To 10) As String
Dim sStr As String, sSTr1 As String
For i = 1 To 10
sStr = ""
sSTr1 = ""
For j = 1 To 4
sStr = sStr & Chr(Int(Rnd() * 26 + 65))
sSTr1 = sSTr1 & Chr(Int(Rnd() * 26 + 65))
Next
myCollection.Add sStr, sStr
If Rnd() < 0.5 Then
vArr(i) = sStr
Else
vArr(i) = sSTr1
End If
Next
For i = 1 To 10
Debug.Print i, vArr(i), elementExists(myCollection, vArr(i))
Next
End Sub

Function elementExists(myCollection As Collection, myName As String)
As Boolean
On Error Resume Next
elementExists = (myName = myCollection(myName))
End Function

of course this doesn't do objects, but string seemed to be the type
anyway.

efficiency qn: search collection or use on error?
 

Function elementExists(myCollection As Collection, myName As String) As
Boolean
Dim vArr as Variant
On Error Resume Next
vArr = TypeName(myCollection(myName))
elementExists = (VarType(vArr) < vbEmpty)
End Function

works with both and doesn't use the undocumented VarPtr.

--
Regards,
Tom Ogilvy



"keepITcool" wrote in message
ft.com...
use the VarPtr method in my example.
afaik that works in all cases.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

OK, here is a better example:

Sub ABCD()
Dim myCollection As New Collection
Dim vArr(1 To 10) As String
Dim sStr As String, sSTr1 As String
For i = 1 To 10
sStr = ""
sSTr1 = ""
For j = 1 To 4
sStr = sStr & Chr(Int(Rnd() * 26 + 65))
sSTr1 = sSTr1 & Chr(Int(Rnd() * 26 + 65))
Next
myCollection.Add sStr, sStr
If Rnd() < 0.5 Then
vArr(i) = sStr
Else
vArr(i) = sSTr1
End If
Next
For i = 1 To 10
Debug.Print i, vArr(i), elementExists(myCollection, vArr(i))
Next
End Sub

Function elementExists(myCollection As Collection, myName As String)
As Boolean
On Error Resume Next
elementExists = (myName = myCollection(myName))
End Function

of course this doesn't do objects, but string seemed to be the type
anyway.