Solved: searching in an array

[eed]

Hi, all,

I'm working with arrays for the first time. I have an array (string data type) that has successfully collected a set of values (names of hardware pieces). Now, given any specific hardware name, I want to refer to my array and determine if the name is an element in the array. How would I do this?

I thought I could treat the array as a normal string and use something like an InStr function to see if strSampleName was contained inside arrHW... maybe I just had the syntax wrong, but I had no success doing InStr on my array.

It seems like there should be an easy way to ask the database, "Is strSampleName an element in arrHW?" A simple boolean response is all I need.

Thanks so much!!!!

[mdmackillop]

Hi,
Have a look here.
MD

http://www.vbaexpress.com/forum/showthread.php?t=1148

Or
[VBA]
Sub TestArray()
Dim YourStr as string
Dim X
X = Range("TestArea")
YourStr = InputBox("Enter text")
MsgBox Belongs(YourStr, X)
End Sub

Function Belongs(Item, MyArray As Variant) As Boolean
Dim NumPos As Long
Belongs = False
On Error Resume Next
NumPos = Application.WorksheetFunction.Match(Item, MyArray, 0)
If NumPos > 0 Then Belongs = True
End Function
[/VBA]

[eed]

mdmackillop,

That sounds like the direction I need to go, but what references need to be set to use your Belongs function? Because right now Access doesn't have a clue what Application.WorksheetFunction.Match is... Thanks for the help!!!!!

~ Erin

[mdmackillop]

Aplogies,
I never noticed this was an Access question .

Try the following
[VBA]
Function Belongs(Item, MyArray As Variant) As Boolean
Dim Member
Belongs = False
For Each Member In MyArray
If Item = Member Then Belongs = True: Exit Sub
Next
End Function
[/VBA]

[eed]

AHA!!! Now that did it. Thanks a billion. I was starting to wonder if I was going to have to create a collection instead of an array, and I have even less experience doing that. But this achieves my aim beautifully, thanks again!!