CodeNinja
01-22-2013, 09:00 AM
Everyone,
I have been using a simple function I built to test whether a string was in an array of strings... very simple function as follows:
Function inArray(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray2 = False
For i = LBound(sArray) To UBound(sArray)
If sArray(i) = sString Then inArray2 = True
Next i
End Function
This returns true if the string is in the array, and false if it is not. Simple.
A few weeks ago, I decided I would try to make this function work faster, and I altered it with the filter function. At first I just used filter, but found out that it would return false positives when a string in the array contained the string I was looking for, but was not exact. As I want exact matches, I altered it a little and came up with this function:
Function inArray2(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray = False
If UBound(Filter(sArray, sString)) >= 0 Then
For i = 0 To UBound(Filter(sArray, sString))
If Filter(sArray, sString)(i) = sString Then
inArray = True
End If
Next i
End If
End Function
Not seeing any real change in my performance, I figured I would test to see which is faster. I am not sure who wrote the SYSTEMTIME code I use, but it wasnt me... so I cannot take credit for it, but I use it frequently when testing the speed of my code... but I ran the following test to see which was faster:
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Function inArray2(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray = False
If UBound(Filter(sArray, sString)) >= 0 Then
For i = 0 To UBound(Filter(sArray, sString))
If Filter(sArray, sString)(i) = sString Then
inArray = True
End If
Next i
End If
End Function
Function inArray(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray2 = False
For i = LBound(sArray) To UBound(sArray)
If sArray(i) = sString Then inArray2 = True
Next i
End Function
Sub testFunction()
Dim arr() As String
Dim time1 As Date
Dim time2 As SYSTEMTIME
Dim s1 As String
Dim s2 As String
Dim l As Long
Dim b As Boolean
ReDim Preserve arr(1 To 8)
arr(1) = "Yes"
arr(2) = "No"
arr(3) = "N/A"
arr(4) = "Nasty"
arr(5) = "Test"
arr(6) = "Nice"
arr(7) = "you"
arr(8) = "NO"
time1 = Now()
GetLocalTime time2
s1 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
For l = 1 To 10000
b = (inArray(arr, "No"))
Next l
GetLocalTime time2
s2 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
time1 = Now()
GetLocalTime time2
s1 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
For l = 1 To 10000
b = (inArray2(arr, "No"))
Next l
GetLocalTime time2
s2 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
Sheets("Constraints").Cells(1, 6) = "InArray2 Start time: " & s1 & vbLf & "End Time: " & s2
End Sub
To my surprise, the filter function did not speed me up at all, instead it slowed me down by a factor of 10. The moral of the story is, sometimes simpler is better.
Hope some of you got a chuckle from my experience. No real question here, just sharing my tale of woe and intrigue.
Codeninja.
I have been using a simple function I built to test whether a string was in an array of strings... very simple function as follows:
Function inArray(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray2 = False
For i = LBound(sArray) To UBound(sArray)
If sArray(i) = sString Then inArray2 = True
Next i
End Function
This returns true if the string is in the array, and false if it is not. Simple.
A few weeks ago, I decided I would try to make this function work faster, and I altered it with the filter function. At first I just used filter, but found out that it would return false positives when a string in the array contained the string I was looking for, but was not exact. As I want exact matches, I altered it a little and came up with this function:
Function inArray2(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray = False
If UBound(Filter(sArray, sString)) >= 0 Then
For i = 0 To UBound(Filter(sArray, sString))
If Filter(sArray, sString)(i) = sString Then
inArray = True
End If
Next i
End If
End Function
Not seeing any real change in my performance, I figured I would test to see which is faster. I am not sure who wrote the SYSTEMTIME code I use, but it wasnt me... so I cannot take credit for it, but I use it frequently when testing the speed of my code... but I ran the following test to see which was faster:
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Function inArray2(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray = False
If UBound(Filter(sArray, sString)) >= 0 Then
For i = 0 To UBound(Filter(sArray, sString))
If Filter(sArray, sString)(i) = sString Then
inArray = True
End If
Next i
End If
End Function
Function inArray(sArray() As String, sString As String) As Boolean
Dim i As Integer
inArray2 = False
For i = LBound(sArray) To UBound(sArray)
If sArray(i) = sString Then inArray2 = True
Next i
End Function
Sub testFunction()
Dim arr() As String
Dim time1 As Date
Dim time2 As SYSTEMTIME
Dim s1 As String
Dim s2 As String
Dim l As Long
Dim b As Boolean
ReDim Preserve arr(1 To 8)
arr(1) = "Yes"
arr(2) = "No"
arr(3) = "N/A"
arr(4) = "Nasty"
arr(5) = "Test"
arr(6) = "Nice"
arr(7) = "you"
arr(8) = "NO"
time1 = Now()
GetLocalTime time2
s1 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
For l = 1 To 10000
b = (inArray(arr, "No"))
Next l
GetLocalTime time2
s2 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
time1 = Now()
GetLocalTime time2
s1 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
For l = 1 To 10000
b = (inArray2(arr, "No"))
Next l
GetLocalTime time2
s2 = time2.wHour & ":" & time2.wMinute & ":" & time2.wSecond & ":" & time2.wMilliseconds
Sheets("Constraints").Cells(1, 6) = "InArray2 Start time: " & s1 & vbLf & "End Time: " & s2
End Sub
To my surprise, the filter function did not speed me up at all, instead it slowed me down by a factor of 10. The moral of the story is, sometimes simpler is better.
Hope some of you got a chuckle from my experience. No real question here, just sharing my tale of woe and intrigue.
Codeninja.