Did you run the posted code?

RBS


"Chip Pearson" wrote in message
...

Unless I am overlooking something, using Application.Match looks very slow
to me.

I don't know. I ran it with an array of about 1000 elements and it was
essentially instantaneous.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sat, 10 Apr 2010 17:03:40 +0100, "RB Smissaert"
wrote:

Unless I am overlooking something, using Application.Match looks very slow
to me.
Using the helper code as posted previously.

Sub ArrayTest3()

Dim i As Long
Dim n As Long
Dim x As Long
Dim arrString(1 To 10000) As String
Dim strAdd As String
Dim V As Variant

StartSW

For i = 1 To 10000

strAdd = RandomWord(2)

V = Application.Match(strAdd, arrString, 0)

If IsError(V) Then
x = x + 1
arrString(x) = strAdd
End If

Next i

StopSW

For i = 1 To x
Cells(i, 1) = arrString(i)
Next i

End Sub


RBS


"Chip Pearson" wrote in message
. ..
I think the fastest method is to test for a value's existence in an
array is to use the Match function. For example, examine the
following code:

' <START CODE
Dim Arr(1 To 5) As String
Dim Ndx As Long
Dim B As String
Dim V As Variant

''''''''''''''
' load up some test values
For Ndx = 1 To 3
Arr(Ndx) = Chr(Asc("a") + Ndx - 1)
Next Ndx

''''''''''''''
B = "f" ' doesn't exist in Arr
V = Application.Match(B, Arr, 0)
If IsError(V) Then
' does not exist
Arr(Ndx) = B
Else
' exists, do nothing
End If

''''''''''''''
B = "b" ' exists in Arr
V = Application.Match(B, Arr, 0)
If IsError(V) Then
' does not exist
Arr(Ndx) = B
Else
' exists, do nothing
End If

''''''''''''''
' list content
For Ndx = LBound(Arr) To UBound(Arr)
Debug.Print Ndx, Arr(Ndx)
Next Ndx
' <END CODE

First, part of the array Arr is given some test values, "a", "b", and
"c". Then, B is assigned "f". The value "f" is searched for in Arr by
the Match function. The variant V holds the result of Match. If it is
an error (IsError = True), then "f" does not exist in the array and is
added to the array. It is assumed that at this point in the code, the
variable Ndx points to the first unused element of Arr. Next, the
value "b" is assigned to the variable B and again Match is used to see
if "b" exists in Arr. Since it does already exist, Match assigns its
position to V, and when V is tested for an error, IsError returns
False so we know "b" already exists.

Finally, the code just lists the content of Arr.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Sat, 10 Apr 2010 06:55:41 -0700, jay dean
wrote:

Hi -

B is a string var. In my code, if a certain condition is met, then store
B in th next available index of Arr(). However, before I store B, I need
to check that the current value of B does not already exist in Arr().

Is there a "faster" way to accomplish this, or I need to loop from
lbound(Arr) to Ubound(Arr) every time to check if the new value to be
stored already exists?

Thanks
Jay Dean



*** Sent via Developersdex http://www.developersdex.com ***