Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative of Vlookup function which improves speed | Excel Worksheet Functions | |||
Speed fill function for MS Ecel 2007 (Beta) | Excel Worksheet Functions | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) | |||
I need mor Speed!!!! | Excel Discussion (Misc queries) | |||
Speed? | Excel Programming |