Using "IIf" or "In" in VBA

  • Thread starter Thread starter Jim Pockmire
  • Start date Start date
J

Jim Pockmire

I believe there is a way to use these functions in VBA, but I ca't remember
how to do it.
 
Jim Pockmire said:
I believe there is a way to use these functions in VBA, but I ca't remember
how to do it.
Click to expand...

This will work,

somevar = IIF(avar = something, retvalfortrue, retvalforfalse)

but most think it easier to follow, later, if you use

If avar = something Then
somevar = retvalfortrue
Else
somevar = retvalforfalse
End If

or the alternative

IF avar = something Then somevar = retvalfortrue Else somevar =
retvalforfalse

FOR EACH varname IN arrayorgroup

is one valid use of IN in VBA. (Open the code window, type "In" in a module,
put the cursor on it, press F1, and that's the Help topic.)

Larry Linson
Microsoft Access MVP
 
Jim Pockmire said:
I believe there is a way to use these functions in VBA, but I ca't
remember how to do it.
Click to expand...

By "In", are you referring to the SQL In operator, as "WHERE fieldname
In (1, 2, 3, 4)"? AFAIK, that operator doesn't exist in VBA, though
there are several ways to do the equivalent. One is the Select Case
statement; e.g.,

Select Case expression
Case 1, 2, 3, 4
' ... do something ...
Case 5, 6, 7, 8
' ... do something else ...
Case Else
' ... do the default thing ...
End Select
 
Jim said:
I believe there is a way to use these functions in VBA, but I ca't remember
how to do it.
Click to expand...

There is an IIf function in VBA and a very similar, but not
quite identical, function in SQL. Help explains the VBA
function fairly well.

In is an SQL operator, but it is not avaliable in VBA.
However you can use it in the Eval function.

y = Eval( x & "IN (1,2,3,4)" )
 
Back
Top