Finding the index of an Array Item

[Barry]

I have an ordered list of strings ( in other words, an enumeration). Given a
string I want to find out:
a. is it in the list,
b. what is its position.
Is most languages I would populate an array with strings and apply some sort
of membership function. I have tried for hours now to determine if VBA has
such a function and have been unable to verify whether it does or not. So I
actually have two questions:
a. is it possible to do this, and
b. where is it documented>

Thanks in advance for your help.

 

[Helmut Weber]

Hi Barry,

you have check item after item, IMHO,
I can't remember a builtin function like "Inlist",
which in my example, checks for the first occurence
of a value in a list.

like this:

Public Function InList(aList() As String, strX As String) As Long
Dim lTemp As Long
For lTemp = LBound(aList) To UBound(aList)
If aList(lTemp) = strX Then
InList = lTemp
Exit For
End If
Next
End Function
' -------------------------------------------
Sub Test6009a()
Dim aList(1 To 100) As String
Dim lTemp As Long
Randomize
For lTemp = 1 To 100
aList(lTemp) = CStr(Int((100 * Rnd) + 1))
Next
msgbox InList(aList(), "50")
End Sub


HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Barry]

Thank you, Helmut, for your quick reply. I was afraid that might be the
case.

 

[Steve Yandl]

Barry,

Sticking strictly to VBA, I'd agree with Helmut. However, you might be able
to utilize several other objects that should generally be available on any
PC where you're running VBA.

If each of your strings is unique, you might consider creating an instance
of the "Scripting.Dictionary" object (Google search will give plenty of
examples of its use). It's faster than working with an array and provides
methods that make it very easy to check for the existence of a specific
value and returning an associated index.

The other object you might want to investigate would be "ADODB.RECORDSET".
This allows you to create a database in memory and do all the sorting,
filtering etc. characteristic of a database.

For some VBS examples that might give you a big head start figuring how to
do this in VBA, take a look at
http://www.microsoft.com/technet/scriptcenter/default.mspx. Locate and take
the "Hey Scripting Guy" link, opt to search 'By Category' and go to the
category on text files. You'll find examples in other areas but there are a
couple of good ones in the text section that might do almost exactly what
you want.


Steve Yandl

 

[Barry]

Thank you, Steve. I'll look into these.

For the present, I've decided to use a bunch of constants to implement the
enumeration and a big "Select case" statement to find the correct. Kind of a
brute force method, I know. If one of your suggestions looks promising, I'll
implement it as an enhancement.

 

[Russ]

I agree with Steve. Try the collections object or dictionary object.
<http://www.tutorial-web.com/asp/collections/index.asp>
<http://www.cpearson.com/excel/variables.htm>