VB and VBA Users Source Code: Checking to see if an array contains a value/item
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Checking to see if an array contains a value/item
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, May 05, 2005
Hits:
2611
Category:
Visual Basic General
Article:
The code below checks to see if an array contains a specified value. The code works for all types of arrays including 1d, 2d, xd and jagged arrays. 'Purpose : Checks to see if a value is already in an array. 'Inputs : avInArray. The array to evaluate. ' vValueToCheck. The value to look for in the array. 'Outputs : True. The array contains vValueToCheck ' False. The array does not contain vValueToCheck. 'Author : Andrew Baker 'Date : 25/03/2000 'Notes : The array can be any size or shape as this function Function ArrayHasItem(avInArray As Variant, vValueToCheck As Variant) As Boolean Dim vThisItem As Variant, lThisRow As Long On Error GoTo ErrExit If ArrayNumDimensions(avInArray) = 1 Then 'Faster than for each loop For lThisRow = LBound(avInArray) To UBound(avInArray) vThisItem = avInArray(lThisRow) 'Check for arrays in arrays If IsArray(vThisItem) Then 'Search an array in an array If ArrayHasItem(vThisItem, vValueToCheck) Then 'Found item ArrayHasItem = True Exit For End If ElseIf vThisItem = vValueToCheck Then ArrayHasItem = True Exit For End If Next Else For Each vThisItem In avInArray 'Check for arrays in arrays If IsArray(vThisItem) Then 'Search an array in an array If ArrayHasItem(vThisItem, vValueToCheck) Then 'Found item ArrayHasItem = True Exit For End If ElseIf vThisItem = vValueToCheck Then ArrayHasItem = True Exit For End If Next End If ErrExit: On Error GoTo 0 End Function 'Purpose : Calculates the number of dimensions in an array 'Inputs : avInArray. The array to evaluate. 'Outputs : The number of dimensions the array has. 'Author : Andrew Baker 'Date : 25/03/2000 'Notes : Function ArrayNumDimensions(avInArray As Variant) As Long Dim lNumDims As Long If IsArray(avInArray) Then On Error GoTo ExitSub Do lNumDims = UBound(avInArray, ArrayNumDimensions + 1) ArrayNumDimensions = ArrayNumDimensions + 1 Loop End If ExitSub: On Error GoTo 0 End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
