First I'd like to mention that credits for this FAQ post must go to RANDEM - he reminded me about the (long forgotten ) Filter function some time ago but I couldn't find that particular post...
So, anyway, here is a quick wrapper for the Filter function:
Code:
Option Explicit
'declare a dynamic array
Dim arTest() As String
Private Sub Form_Load()
Dim i%
ReDim arTest(25) 'I'm just using upper case characters for demonstration
'----------------------------------------------------------
'NOTE: you may add a listbox to your form
' so you can see what's being loaded into array
' if you do that then uncomment one line in the loop
'----------------------------------------------------------
For i = 0 To UBound(arTest)
arTest(i) = "Item " & i + 1 & " - " & Chr(65 + i)
'List1.AddItem "Item " & i + 1 & " - " & Chr(65 + i)
Next i
End Sub
Private Sub Command1_Click()
Dim arTemp() As String
Dim i As Integer
'get all items that contain "1" character
arTemp = FilterArray(arTest, "1", True, vbTextCompare)
Debug.Print "List of items containing '1' character" & vbNewLine
If Not IsEmpty(arTemp) Then
For i = 0 To UBound(arTemp)
Debug.Print arTemp(i)
Next i
End If
'get all items that don't have a "1" character
arTemp = FilterArray(arTest, "1", False, vbTextCompare)
Debug.Print vbNewLine & "List of items that do not have '1' character" & vbNewLine
If Not IsEmpty(arTemp) Then
For i = 0 To UBound(arTemp)
Debug.Print arTemp(i)
Next i
End If
End Sub
Public Function FilterArray(sourcearray() As String, _
searchvalue As String, _
include As Boolean, _
compare As Integer) As String()
'=================================================================
'Returns a zero-based array containing subset of a
'string array based on a specified filter criteria
'----
'compare argument can be one of the following:
' vbBinaryCompare = 0, vbTextCompare = 1, vbDatabaseCompare = 2
'but most of time you'd probably need the vbTextCompare
'=================================================================
Dim arTemp() As String
arTemp = Filter(sourcearray, searchvalue, include)
FilterArray = arTemp
End Function
edit: changed title to indicate the "classic vb".