VB 數組查找返回所有匹配下標：使用集合收集

VB 中數組可能包含多行完全相同的數據，查找時需返回所有匹配行的下標，而非僅第一個。解決方法：定義 Collection 集合，遍歷數組將所有匹配下標添加進集合返回。

核心代碼

Function GetAllMatchingIndexes(arr As Variant, searchValue As Variant) As Collection
  Dim coll As New Collection
  Dim i As Long
  
  For i = LBound(arr) To UBound(arr)
    If arr(i) = searchValue Then
      coll.Add i, CStr(i)  ' 鍵用下標字符串，值為下標
    End If
  Next i
  
  Set GetAllMatchingIndexes = coll
End Function

若數組作為控件參數傳遞

控件（如 ListBox、ComboBox）的 List 屬性返回二維數組 List(row, col)。查找時需遍歷行：

Function FindInListBox(lst As Control, searchCol As Long, searchValue As Variant) As Collection
  Dim coll As New Collection
  Dim i As Long
  
  For i = 0 To lst.ListCount - 1
    If lst.List(i, searchCol) = searchValue Then
      coll.Add i, CStr(i)
    End If
  Next i
  
  Set FindInListBox = coll
End Function

調用示例

Dim matches As Collection
Set matches = GetAllMatchingIndexes(myArray, "target")

Dim idx As Variant
For Each idx In matches
  Debug.Print "行 " & idx & " 匹配"
Next

' 轉為數組
Dim arr() As Long
ReDim arr(1 To matches.Count)
Dim k As Long
For Each idx In matches
  k = k + 1
  arr(k) = idx
Next

要點

• Collection 可存儲任意類型鍵值對，適合收集動態數量的下標。
• 用 CStr(index) 作鍵，避免重複下標（鍵唯一），值存下標本身。
• 控件查找直接用 List(i, col) 訪問，無需複製數組。