Is there a NotIn(“A”,“B”) function in VBA?

Question

I'm writing a function that requires input and my data validation looks very awkward. If InputFld isn't "A","B", or "C", then it's an error:

If InputFld <>"A" and InputFld<>"B" and InputFld<>"C" then goto ErrorHandler

This just looks ugly to me. Is there a more elegant solution? I'd like to just write something like:

If InputFld not in ("A","B","C") then goto ErrorHandler

See? Much easier to read and maintain this way. But I don't know how to do it.

Answer 1

How about:

If Instr("ABC",InputFld)=0 Then

Answer 2

Eval() should allow you to do something similar. This expression returns -1 (True):

Debug.Print Eval("""g"" Not In (""a"",""b"",""c"")")

I wouldn't call that elegant, though.

Consider using the Like operator. This expression returns True:

Debug.Print Not "g" Like "[abc]"

Answer 3

At least two ways to do that:

public function IsInSet(byval value as variant, paramarray theset() as variant) as boolean
  dim i as long 

  for i=lbound(theset) to ubound(theset)
    if value = theset(i) then
      isinset = true
      exit function
    end if
  next
end function

Usage: If not IsInSet(val, "A", "B", "C") then ...

---

public function IsInSet(byval value as variant, theset as variant) as boolean
  dim i as long 

  for i=lbound(theset) to ubound(theset)
    if value = theset(i) then
      isinset = true
      exit function
    end if
  next  
end function

Usage: If not IsInSet(val, array("A", "B", "C")) then ...