|
|
|
|
|
|
|
|
|
Multiple Apps
|
Test if ?X? is in array ?A?
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
MWE
|
|
Description:
|
InArray(X,A) tests if X is in array A. If not, 0 is returned; if so, returned value = i where X = A(i)
|
|
Discussion:
|
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 http://vbaexpress.com/kb/getarticle.php?kb_id=741 ) 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.
|
|
Code:
|
instructions for use
|
Option Explicit
Sub xlInArray()
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
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:
|
- Copy the above code.
- Open any workbook.
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- 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]
- Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
- Paste the code into the right-hand code window.
- Close the VBE, save the file if desired.
- See ?Test The Code? below
|
|
Test the code:
|
- Open the example
- The example/demo spreadsheet contains a single sheet, the InArray procedure and an Excel based procedure that demos InArray?s use.
- Select some cells and click on the command button
- Enter a value (either in the selection or not)
- The demo should return an appropriate answer.
- 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:
|
inArray.zip 19.33KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 110 times.
|
|
|