VBA中有没有NotIn("A"，"B")函数？

Question

我正在编写一个需要输入的函数，我的数据验证看起来非常笨拙。如果InputFld不是"A“、"B”或"C"，那么它就是一个错误：

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

这在我看来太难看了。有没有更优雅的解决方案？我只想写一些像这样的东西：

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

看见?以这种方式阅读和维护要容易得多。但我不知道该怎么做。

Answer 1

至少有两种方法可以做到这一点：

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

用法：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

用法：If not IsInSet(val, array("A", "B", "C")) then ...

Answer 2

这样如何：

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

Answer 3

Eval()应该允许您执行类似的操作。此表达式返回-1 (True)：

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

不过，我不会称之为优雅。

考虑使用Like运算符。此表达式返回True：

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