Happy Codings - Programming Code Examples
Html Css Web Design Sample Codes CPlusPlus Programming Sample Codes JavaScript Programming Sample Codes C Programming Sample Codes CSharp Programming Sample Codes Java Programming Sample Codes Php Programming Sample Codes Visual Basic Programming Sample Codes


Visual Basic Programming Code Examples

Visual Basic > Applications VBA Code Examples

Searching for items in arrays

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115
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