比如说,我有一个包含50000个元素的字符串数组。使用 For Next
在这么大的数组中查找是非常慢的。有没有更快速的方法来进行搜索呢?
注意:我们可以使用 join
和 instr
来在数组中搜索字符串,但是这种方法不好用,因为我无法找到元素的编号。
注意:数组未排序且我正在寻找子字符串。
比如说,我有一个包含50000个元素的字符串数组。使用 For Next
在这么大的数组中查找是非常慢的。有没有更快速的方法来进行搜索呢?
注意:我们可以使用 join
和 instr
来在数组中搜索字符串,但是这种方法不好用,因为我无法找到元素的编号。
注意:数组未排序且我正在寻找子字符串。
这是你使用 Join
和 InStr
的想法的扩展:
Sub TestArraySearch()
Dim A(4) As String
A(0) = "First"
A(1) = "Second"
A(2) = "Third"
A(3) = "Fourth"
A(4) = "Fifth"
Debug.Print FastArraySearch(A, "Fi")
Debug.Print FastArraySearch(A, "o")
Debug.Print FastArraySearch(A, "hird")
Debug.Print FastArraySearch(A, "Fou")
Debug.Print FastArraySearch(A, "ndTh")
Debug.Print FastArraySearch(A, "fth")
End Sub
Function FastArraySearch(SearchArray As Variant,SearchPhrase As String) As String
Dim Pos As Long, i As Long, NumCharsProcessed As Long, Txt As String
Pos = InStr(Join(SearchArray, "§"), SearchPhrase)
If Pos > 0 Then
For i = LBound(SearchArray) To UBound(SearchArray)
NumCharsProcessed = NumCharsProcessed + Len(SearchArray(i)) + 1
If NumCharsProcessed >= Pos Then
FastArraySearch = SearchArray(i)
Exit Function
End If
Next i
End If
End Function
Len
函数高度优化。您能展示一下您正在使用的代码以及它需要多长时间吗?另外,多长时间算是过长呢?这段代码读入了50,000个字符串,并在约300毫秒内找到了其中包含子字符串的275个。
Sub testarr()
Dim vaArr As Variant
Dim i As Long
Dim dTime As Double
Dim lCnt As Long
dTime = Timer
vaArr = Sheet1.Range("A1:A50000")
For i = LBound(vaArr, 1) To UBound(vaArr, 1)
If InStr(1, vaArr(i, 1), "erez") > 0 Then
lCnt = lCnt + 1
Debug.Print i, vaArr(i, 1)
End If
Next i
Debug.Print Timer - dTime
Debug.Print lCnt
End Sub
在VB6中加速任何数组索引操作的最佳方法是使用以下选项重新编译组件:
现在,您的数组索引应该与等效的C/C++操作一样快。
唯一的问题是,您应确保您的代码永远不会引用其正常数组边界之外的索引。以前,您会收到VB运行时错误。之后,您可能会收到访问冲突的错误。
我使用了Joins
和Splits
,但没有进行任何benchmark
测试:
Function IndexOf(ByRef arr() As String, ByVal str As String) As Integer
Dim joinedStr As String
Dim strIndex As Integer
joinedStr = "|" & Join(arr, "|")
strIndex = InStr(1, joinedStr, str)
If strIndex = 0 Then
IndexOf = -1
Exit Function
End If
joinedStr = Mid(joinedStr, 1, strIndex - 1)
IndexOf = UBound(Split(joinedStr, "|")) - 1
End Function
这里有一个快速返回子字符串出现次数的方法。希望能帮到你!
Option Explicit
Option Compare Binary
Option Base 0
DefLng A-Z
Sub TestSubStringOccurence()
Dim GrabRangeArray() As Variant
Dim i As Long
Dim L As Long
Dim RunTime As Double
Dim SubStringCounter As Long
Dim J As Long
Dim InStrPosition As Long
Dim Ws As Excel.Worksheet
Set Ws = ThisWorkbook.Sheets("Sheet1")
RunTime = Timer
With Ws
For i = 1 To 50000
If i Mod 2 = 0 Then .Cells(i, 1).Value2 = "1 abcdef 2 abcdef 3 abcdef 4 abcdef 5 abcdef" _
Else .Cells(i, 1).Value2 = i Next i
GrabRangeArray = .Range("a1:a50000").Value
End With
RunTime = Timer
'returns number of substring occurrences
For i = 1 To UBound(GrabRangeArray, 1)
InStrPosition = 1
Do
InStrPosition = InStr(InStrPosition, GrabRangeArray(i, 1), "abcdef", vbBinaryCompare)
If InStrPosition <> 0 Then
SubStringCounter = SubStringCounter + 1
InStrPosition = InStrPosition + 6
End If
Loop Until InStrPosition = 0
Next i
Debug.Print "Runtime: " & Timer - RunTime & ", ""abcdef"" occurences: " & SubStringCounter
End Sub
这里有一种快速的方法来测试子字符串是否存在,但不返回子字符串出现的次数。
Option Explicit
Option Compare Binary
Option Base 0
DefLng A-Z
Sub TestSubStringOccurence()
Dim GrabRangeArray() As Variant
Dim I As Long
Dim L As Long
Dim RunTime As Double
Dim SubStringCounter As Long
Dim J As Long
Dim InStrPosition As Long
Dim Ws As Excel.Worksheet
Const ConstABCDEFString As String = "abcdef"
Dim B As Boolean
Set Ws = ThisWorkbook.Sheets("Sheet1")
RunTime = Timer
ReDim GrabRangeArray(0 To 49999)
With Ws
For I = 1 To 50000
If I Mod 2 = 0 Then GrabRangeArray(I - 1) = "1 abcdef 2 abcdef 3 abcdef 4 abcdef 5 abcdef" _
Else GrabRangeArray(I - 1) = I - 1
Next I
.Range("a1:a50000").Value = Application.Transpose(GrabRangeArray)
End With
RunTime = Timer
For I = 1 To UBound(GrabRangeArray, 1)
If InStrB(1, GrabRangeArray(I), ConstABCDEFString, vbBinaryCompare) Then _
SubStringCounter = SubStringCounter + 1
Next I
Debug.Print "Runtime: " & Timer - RunTime & ", ""abcdef"" occurences: " & SubStringCounter
End Sub
Function IndexOf(ByRef arr() As String, ByVal str As String) As Integer
Dim tuttook As Boolean
Dim joinedStr As String
Dim strIndex As Integer
strIndex = 0
tuttook = False
joinedStr = "|" & Join(arr, "|")
While tuttook = False
strIndex = InStr(strIndex + 1, joinedStr, str)
If strIndex = 0 Then
IndexOf = -1
Exit Function
Else
If Mid(joinedStr, strIndex - 1, 1) = "|" And Mid(joinedStr, strIndex + Len(str), 1) = "|" Then tuttook = True
End If
Wend
joinedStr = Mid(joinedStr, 1, strIndex - 1)
IndexOf = UBound(Split(joinedStr, "|")) - 1
End Function
UBound(Filter(stringArray, itemToFind)) > -1
这个代码可以告诉你该元素是否在数组中。 - JimmyPena