IsInArray function speed
 

I was testing a couple of IsInArray functions that I came up with. I wanted
to see which was faster. Their use is for moving items between listboxes,
but can be applied elsewere.
By changing x in the Initialize code below, I found that IsInArray was
relatively faster and faster as the list grows above 50 items. As the list
gets below 50, IsInArray2 gets relatively faster and faster. Yes, I know
this probably doesn't matter because you don't test if something is in a
list 1000 times, but anyway.

To test, I put two listboxes on a form with three buttons in-between them;
Move All, Move Selected, and Clear.

If you're someone who is going to actually try timing these, then I'm sure
you already have a preference for timing code, so I won't include anything
here.

Here are the IsInArray Functions
------------------------------------------------------------------
Function IsInArray(FindItem As Variant, LookInArray As Variant) As Boolean
On Error Resume Next
IsInArray = Application.Match(FindItem, LookInArray, 0)
End Function

Function IsInArray2(FindItem As Variant, LookInArray As Variant) As Boolean
Dim TheArray As String
If Not IsArray(LookInArray) Then Exit Function
FindItem = vbNullChar & FindItem & vbNullChar
TheArray = vbNullChar & Join(LookInArray, vbNullChar) & vbNullChar
IsInArray2 = (InStr(1, TheArray, FindItem) 0)
End Function
------------------------------------------------------------------

Here is the UserForm1 code.
------------------------------------------------------------------
Private Sub UserForm_Initialize()
Dim myArray() As Variant
Dim x As Long, i As Long

x = 50
ReDim myArray(x)
For i = 0 To x
myArray(i) = i
Next i

Me.ListBox1.List = myArray

End Sub

Private Sub CommandButton1_Click()
Me.ListBox2.List = Me.ListBox1.List
End Sub

Private Sub CommandButton2_Click()
Dim i As Long, j As Long
Dim myArray() As Variant

With ListBox2
For i = 0 To .ListCount - 1
ReDim Preserve myArray(i)
myArray(i) = .List(i)
Next i
End With

'StartTimer
For j = 1 To 1000
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
If Not IsInArray(.List(i), myArray) Then
ListBox2.AddItem .List(i)
Else
'MsgBox .List(i) & " is already in the list."
End If
End If
Next i
End With
Next j
'EndTimer

End Sub

Private Sub CommandButton3_Click()
ListBox2.Clear
End Sub
------------------------------------------------------------------

-Jeremy