VBA functional equivalent of "IN" or "AMONG"?

I'd like to avoid coding a long set of IF... OR IF...statements.

I can do a Select Case, but that would only let me case out on
a single value.

What I'm looking for is something like

If thisVal OR thatVal AMONG("X", "Y", "Z")....

The main thing is getting the allowed values in one place where the next guy can
see/update them easily.

Somehow Enum comes to mind...
--
PeteCresswell

re: VBA functional equivalent of "IN" or "AMONG"?

RE/[color=blue]
>I'd like to avoid coding a long set of IF... OR IF...statements.[/color]

The actual statements are:
------------------------------------------------
If !Val_MSD = "COP" _
Or !Val_MSD = "GO" _
Or !Val_MSD = "ETM" _
Or !Val_MSD = "INS" _
Or !Val_MSD = "MISC" _
Or !Val_MSD = "PRE-RE" _
Or !Val_MSD = "REV" _
Or !Val_MLD = "COP" _

Or !Val_MLD = "GO" _
Or !Val_MLD = "ETM" _
Or !Val_MLD = "INS" _
Or !Val_MLD = "MISC" _
Or !Val_MLD = "PRE-RE" _
Or !Val_MLD = "REV" Then
theReason = "When FIMA2 value='BOND' and Fimma-1 value among
('COP', 'GO', 'ETM', 'INS', 'MISC', 'PRE-RE', 'REV') difference is OK."
------------------------------------------------

--
PeteCresswell

re: VBA functional equivalent of "IN" or "AMONG"?

(Pete Cresswell) wrote:[color=blue]
> I'd like to avoid coding a long set of IF... OR IF...statements.
>
> I can do a Select Case, but that would only let me case out on
> a single value.
>
> What I'm looking for is something like
>
> If thisVal OR thatVal AMONG("X", "Y", "Z")....[/color]

Case "X", "Y", "Z" is a valid statement in a Select Case block.

re: VBA functional equivalent of "IN" or "AMONG"?

"(Pete Cresswell)" wrote:
[color=blue]
> I'd like to avoid coding a long set of IF... OR IF...statements.
>
> I can do a Select Case, but that would only let me case out on
> a single value.
>
> What I'm looking for is something like
>
> If thisVal OR thatVal AMONG("X", "Y", "Z")....
>
> The main thing is getting the allowed values in one place where the next guy can
> see/update them easily.
>
> Somehow Enum comes to mind...
> --
> PeteCresswell[/color]

Sometimes I may do something like this
Dim s as string
Dim sSearch as string
s = "*Mike*Sam*Joe*"
sSearch = "Joe"
If instr(s,"*" & strSearch & "*") > 0 then...

I'm using 97 tho.

re: VBA functional equivalent of "IN" or "AMONG"?

Nice one, will keep that in memory! Thanks.

Salad wrote:
[color=blue]
> s = "*Mike*Sam*Joe*"
> If instr(s,"*" & strSearch & "*") > 0 then...
> I'm using 97 tho.[/color]
So? Does InStr get deprecated somewhere?[color=blue]
>
>[/color]

--
Bas Cost Budde

re: VBA functional equivalent of "IN" or "AMONG"?

"(Pete Cresswell)" <x@y.z> wrote in
news:iklo10100ia68j889dvmp5rnoslkj8kgf3@4ax.com:
[color=blue]
> RE/[color=green]
>>I'd like to avoid coding a long set of IF... OR
>>IF...statements.[/color]
>
> The actual statements are:
> ------------------------------------------------
> If !Val_MSD = "COP" _
> Or !Val_MSD = "GO" _
> Or !Val_MSD = "ETM" _
> Or !Val_MSD = "INS" _
> Or !Val_MSD = "MISC" _
> Or !Val_MSD = "PRE-RE" _
> Or !Val_MSD = "REV" _
> Or !Val_MLD = "COP" _
>
> Or !Val_MLD = "GO" _
> Or !Val_MLD = "ETM" _
> Or !Val_MLD = "INS" _
> Or !Val_MLD = "MISC" _
> Or !Val_MLD = "PRE-RE" _
> Or !Val_MLD = "REV" Then
> theReason = "When FIMA2 value='BOND' and Fimma-1
> value among
> ('COP', 'GO', 'ETM', 'INS', 'MISC', 'PRE-RE', 'REV')
> difference is OK."
> ------------------------------------------------[/color]

How about
if isInlist(!val_msd) or isinlist(!val_mld) then ...

Public Function isInList(stFind As String) As Boolean
Dim stItems As String
stItems = "cop,go,etm,ins,misc,pre-re,rev"
isInList = InStr(1, stItems, stFind) > 0
End Function

Bob Q

re: VBA functional equivalent of "IN" or "AMONG"?

Bob Quintal wrote:
[color=blue]
> How about
> if isInlist(!val_msd) or isinlist(!val_mld) then ...
>
> Public Function isInList(stFind As String) As Boolean
> Dim stItems As String
> stItems = "cop,go,etm,ins,misc,pre-re,rev"
> isInList = InStr(1, stItems, stFind) > 0
> End Function
>
> Bob Q[/color]

A nice function, short and sweet.

You might need to adjust that for numbers tho. Ex: you pass the number
1 and the string contains an 11. And you need to consider strings too..
Ex: you are searching for Bob and the string contains "Bobbie". That's
why I put a delimiter around each item in the string and the search
for...I use an * but any character would work.

re: VBA functional equivalent of "IN" or "AMONG"?

RE/[color=blue]
>if isInlist(!val_msd) or isinlist(!val_mld) then ...
>
>Public Function isInList(stFind As String) As Boolean
>Dim stItems As String
>stItems = "cop,go,etm,ins,misc,pre-re,rev"
>isInList = InStr(1, stItems, stFind) > 0
>End Function[/color]

That's the kind of functionality I was looking for.

Thanks!
--
PeteCresswell

re: VBA functional equivalent of "IN" or "AMONG"?

On Sun, 01 Feb 2004 01:21:15 GMT in comp.databases.ms-access, "(Pete
Cresswell)" <x@y.z> wrote:
[color=blue]
>I'd like to avoid coding a long set of IF... OR IF...statements.
>
>I can do a Select Case, but that would only let me case out on
>a single value.
>
>What I'm looking for is something like
>
>If thisVal OR thatVal AMONG("X", "Y", "Z")....
>
>The main thing is getting the allowed values in one place where the next guy can
>see/update them easily.
>
>Somehow Enum comes to mind...[/color]

An alternative to Bob's excellent function is the use of separate
parameters in an array...

Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
Dim i As Long
For i = LBound(pvarAr) To UBound(pvarAr)
If pvarWhat = pvarAr(i) Then
Among = True
Exit For
End If
Next
End Function


--
A)bort, R)etry, I)nfluence with large hammer.

re: VBA functional equivalent of "IN" or "AMONG"?

RE/[color=blue]
>Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
> Dim i As Long
> For i = LBound(pvarAr) To UBound(pvarAr)
> If pvarWhat = pvarAr(i) Then
> Among = True
> Exit For
> End If
> Next
>End Function[/color]

I like that even better - just because it reads better when coded in the calling
routine.
--
PeteCresswell

re: VBA functional equivalent of "IN" or "AMONG"?

"(Pete Cresswell)" <x@y.z> wrote in message
news:83qt101o4dp9tlu434e9lnldkadvgq66ir@4ax.com...[color=blue]
> RE/[color=green]
> >Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
> > Dim i As Long
> > For i = LBound(pvarAr) To UBound(pvarAr)
> > If pvarWhat = pvarAr(i) Then
> > Among = True
> > Exit For
> > End If
> > Next
> >End Function[/color]
>
> I like that even better - just because it reads better when coded in the[/color]
calling[color=blue]
> routine.[/color]

Why not use a table for your static values and a query to see if the
variable
exists in the table? Wasn't management of the static values one of the
reasons
for the question?

re: VBA functional equivalent of "IN" or "AMONG"?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have any of you thought of using the Eval() function? (debug window):

? eval("2 in (2, 3)")
- -1

Eval() returns true if 2 is in the list (2, 3); false if 2 is not in
the list.

Rgds,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCB6oYechKqOuFEgEQK6ZQCgxM99LSJTkZ2J8yK9PytZWR qmO2EAniKi
ZqzZunNsiRdP42ghLJ2t8tot
=AqrY
-----END PGP SIGNATURE-----


(Pete Cresswell) wrote:
[color=blue]
> RE/
>[color=green]
>>Function Among(pvarWhat, ParamArray pvarAr()) As Boolean
>> Dim i As Long
>> For i = LBound(pvarAr) To UBound(pvarAr)
>> If pvarWhat = pvarAr(i) Then
>> Among = True
>> Exit For
>> End If
>> Next
>>End Function[/color]
>
>
> I like that even better - just because it reads better when coded in the calling
> routine.[/color]