Multiple Apps

Test if ?X? is in array ?A?

Ease of Use


Version tested with


Submitted by:



InArray(X,A) tests if X is in array A. If not, 0 is returned; if so, returned value = i where X = A(i) 


Testing if X = A is pretty trivial, but what if A is an array and you wish to know if any array element is = to X? The solution is not hard: loop through the values of A and test for one that is equal to X. If one is encountered, stop and set a flag (or something). If all values of A are tested and no value = to X is found, then X is not in A. Similar to instrArray (see ) which tests if strX is in array strA, InArray (X , A) tests for X (a numeric) in A (a numeric array). If X is found, InArray returns i where X = A(i). If X is not found, InArray returns 0. X and A need not be the same type, but both must be numeric. InArray can be used in any VBA application. The demo is Excel-based and includes an additional procedure to facilitate use in excel. 


instructions for use


Option Explicit Sub xlInArray() ' '**************************************************************************************** ' Title xlInArray ' Target Application: any ' Function: determines if user-specified X is in the current selection ' Limitations: none ' Passed Values: none ' '**************************************************************************************** ' ' Dim A() Dim I As Long Dim ProcTitle As String Dim Prompt As String Dim strX As String Dim X Dim xlCell As Range ProcTitle = "xlInArray" Prompt = "enter value to be tested against values in selection." & vbCrLf & vbCrLf & _ "click on CANCEL to exit procedure" ReDim A(1 To Selection.Cells.Count) I = 0 For Each xlCell In Selection If IsNumeric(xlCell) = False Then MsgBox "ERROR: one or more values in selection" & vbCrLf & _ "are not numeric. Make a new selection" & vbCrLf & _ "and start again.", vbCritical + vbOKOnly, ProcTitle Exit Sub End If I = I + 1 A(I) = xlCell Next xlCell Selection.Cells.Interior.ColorIndex = 35 GetX: strX = InputBox(Prompt, ProcTitle) If strX = "" Then GoTo CleanUp If IsNumeric(strX) = False Then If Left(Prompt, 5) <> "ERROR" Then _ Prompt = "ERROR: data entered is not numeric" & vbCrLf & vbCrLf & Prompt GoTo GetX End If X = strX I = InArray(X, A) Select Case I Case Is = 0 MsgBox X & " was not found in selection.", vbInformation, ProcTitle Case Is > 0 MsgBox X & " was found in selection at index " & I, vbInformation, ProcTitle End Select GoTo GetX CleanUp: Set xlCell = Nothing Selection.Cells.Interior.ColorIndex = 0 End Sub Function InArray(X, A) As Long ' '**************************************************************************************** ' Title InArray ' Target Application: any ' Function: determines if X is in the array A ' if X is in A, then on return InArray = I where X = A(I) ' if X is not in A, on return InArray = 0 ' Limitations: X and A must be of numeric type ' Passed Values: ' X [in, numeric] ' A [in, numeric array] ' '**************************************************************************************** ' ' Dim I As Long Dim dblX As Double dblX = CDbl(X) For I = LBound(A) To UBound(A) If dblX = CDbl(A(I)) Then InArray = I Exit Function End If Next I InArray = 0 End Function

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below

Test the code:

  1. Open the example
  2. The example/demo spreadsheet contains a single sheet, the InArray procedure and an Excel based procedure that demos InArray?s use.
  3. Select some cells and click on the command button
  4. Enter a value (either in the selection or not)
  5. The demo should return an appropriate answer.
  6. The demo procedure xlInArray, checks that all values in the selection are numeric and that the value to be tested, X, is also numeric.

Sample File: 19.33KB 

Approved by mdmackillop

This entry has been viewed 109 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express