Visual Basic Programming Code Examples

Searching for items in arrays The following routines are useful when trying to locate items within both 1d and 2d arrays: 'Purpose : Finds the position of the first matching item in an 1d array. 'Inputs : avValues The array to evaluate. ' vFindItem The value to look for in the array. ' [lDefault] The value to return if vFindItem is not found 'Outputs : The position of the item with the array ' OR 0/lDefault if the item was not found. 'Notes : Make the module Option Compare Text for case insensative searches Function Array1DFindFirst(avValues As Variant, vFindItem As Variant, Optional lDefault As Long = 0) As Long Dim lThisRow As Long, bFound As Boolean If IsArray(avValues) Then On Error Resume Next For lThisRow = LBound(avValues) To UBound(avValues) If vFindItem = avValues(lThisRow) Then bFound = True Array1DFindFirst = lThisRow Exit For End If Next End If If bFound = False Then Array1DFindFirst = lDefault End If On Error GoTo 0 End Function 'Purpose : Finds the position of the first matching item in a specified row of a 2d array. 'Inputs : avValues The array to evaluate. ' vFindItem The value to look for in the array. ' lSearchCol The column to search in the array avValues for vFindItem ' lDefault The value to return if vFindItem is not found in 'Outputs : The row number of of the item with the array ' OR lDefault if the item was not found. 'Notes : Make the module Option Compare Text for case insensative searches Function Array2DFindFirstRow(avValues As Variant, vFindItem As Variant, lSearchCol As Long, Optional lDefault As Long = 0) As Long Dim lThisRow As Long, bFound As Boolean If IsArray(avValues) Then On Error Resume Next For lThisRow = LBound(avValues, 2) To UBound(avValues, 2) If vFindItem = avValues(lSearchCol, lThisRow) Then bFound = True Array2DFindFirstRow = lThisRow Exit For End If Next End If If bFound = False Then Array2DFindFirstRow = lDefault End If On Error GoTo 0 End Function 'Purpose : Finds the position of the first matching item in a specified column of a 2d array. 'Inputs : avValues The array to evaluate. ' vFindItem The value to look for in the array. ' lSearchRow The row to search in the array avValues for vFindItem ' [lDefault] The value to return if vFindItem is not found 'Outputs : The column number of of the item with the array ' OR lDefault if the item was not found. 'Notes : Make the module Option Compare Text for case insensative searches Function Array2DFindFirstCol(avValues As Variant, vFindItem As Variant, lSearchRow As Long, Optional lDefault As Long = 0) As Long Dim lThisCol As Long, bFound As Boolean If IsArray(avValues) Then On Error Resume Next For lThisCol = LBound(avValues) To UBound(avValues) If vFindItem = avValues(lThisCol, lSearchRow) Then bFound = True Array2DFindFirstCol = lThisCol Exit For End If Next End If If bFound = False Then Array2DFindFirstCol = lDefault End If On Error GoTo 0 End Function Sub Test() Dim lThisCol As Long Dim asValues(1 To 5, 1 To 2) '5 Cols 2 Rows For lThisCol = 1 To 5 asValues(lThisCol, 1) = lThisCol & ":1" asValues(lThisCol, 2) = lThisCol & ":2" Next Debug.Print Array2DFindFirstRow(asValues, "1:2", 1, -1) Debug.Print Array2DFindFirstCol(asValues, "5:1", 1, -1) End Sub