Using the In() Function

[Ajayi32]

apparently doesn't work in VBA. Because it doesn't work this is the code I am using is below. My question: Is there a function similar to In() that I could use without having to Or arg = *** everytime?

Thanks,
/uh

Function DivCSR(arg As String, arg2 As Double) As Double
If arg = "CCM" Or arg = "CHT" Or arg = "CIN" Or arg = "CLE" Or arg = "CMI" _
Or arg = "COS" Or arg = "DAY" Or arg = "DET" Or arg = "EWK" Or arg = "GRP" _
Or arg = "IND" Or arg = "KNX" Or arg = "KZO" Or arg = "LEX" Or arg = "LVL" _
Or arg = "MEM" Or arg = "NAS" Or arg = "NIN" Or arg = "NKC" Or arg = "OHI" _
Or arg = "SBN" Or arg = "TOL" Or arg = "TRC" Or arg = "ZCL" Or arg = "ZIN" _
Or arg = "ZMG" Or arg = "ZOH" Or arg = "ZTN" Then
DivCSR = arg2
End If
End Function

 

[Atomic Shrimp]

You could use InStr(), like this:

Function DivCSR(arg As String, arg2 As Double) As Double
Dim strTest as string
strTest = "CCM CHT CIN CLE CMI COS DAY DET EWK GRP IND KNX KZO LEX LVL MEM NAS NIN NKC OHI SBN TOL TRC ZCL ZIN ZMG ZOH ZTN"
if (Not(Isnull(InStr(strTest,Arg)))) and (Len(Arg) = 3) and (IsNull(InStr(arg," "))) Then
DivCSR = arg2
End If

'NB the test that the ARG is 3 chars and contains no space may be necessary, in case it was ever passed to the function as just "Z" or, for example "ZCL ZI" or "L Z" - any of which combinations also occur in the test string
End Function


Hope this helps, I've not tested it.

Mike

(PS: alternatively, you could define all of the possible values of ARG in a table and use DLookup to see if the one you want is there, this way, if there is a need for the user to be able to edit the list of possible ARGs, it would be easier to set up)

[This message has been edited by Mike Gurman (edited 02-22-2001).]

 

[Ajayi32]

thanks, i will try it

/uh