VBA 查找值是否在值中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15464144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA finding if value is in values
提问by Jaanus
I have a value y, and I want to find out if that value is inside this set of values : x1, x2, ... xn.
我有一个值y，我想知道该值是否在这组值内：x1, x2, ... xn。
I can do it like this:
我可以这样做：
if(y = x1 or y = x2 or .....)
But is there a nicer way? pseudocode:
但是有更好的方法吗？伪代码：
if(y in (x1, x2, ...., xn))
采纳答案by Christian Specht
You could write a helper function like this one:
你可以写一个这样的辅助函数：
Public Function FindValue(ByVal ValueToFind As Variant, ParamArray SearchIn() As Variant)
Dim i As Integer
For i = 0 To UBound(SearchIn)
If SearchIn(i) = ValueToFind Then
FindValue = True
Exit Function
End If
Next
End Function
The second parameter (the ParamArray) is an array, so you can actually pass an indefinite number of parameters.
第二个参数 (the ParamArray) 是一个数组，因此您实际上可以传递无限数量的参数。
So you can just pass all your values to this function - the one you want to find first, and after that all the ones you want to search in:
因此，您可以将所有值传递给此函数 - 您首先要查找的值，然后是您要搜索的所有值：
Dim Found As Boolean
Found = FindValue(y, x1, x2, x3, xn)
回答by Yoram Ariel
You can use Select Case in the same manner of (if then else):
您可以以相同的方式使用 Select Case (if then else)：
Select Case Y
Case X1, X2, X3, ...
Do if True
Case Else
Do if False
End Select
回答by Paolo
Use arrays:
使用数组：
dim x(10)
x(1)=....
x(2)=....
y=....
for i=1 to 10
if x(i)=y then
....
end if
next i
回答by shahkalpesh
dim values as string
values = "1,2,3,4,5," 'Note that comma is a separator and added towards the end as well.
dim lookupValue as string
lookupValue = ",4,"
dim found as Boolean
found = (instr(1, values, lookupValue) <> 0)
if found then
end if
EDIT: Another way
编辑：另一种方式
dim lookupValue as long
lookupValue = 21000
dim values
set values = CreateObject("Scripting.Dictionary")
with values
.Add 1, 1
.Add 10, 1
.Add 100, 1
.Add 1000, 1
.Add 10000, 1
end with
dim found as Boolean
found = values.Exists(lookupValue)
回答by Kazimierz Jawor
Quite easy idea to check if the value exist is to use Matchfunction:
检查值是否存在很容易的想法是使用Match函数：
Dim myTBL As Variant
myTBL = Array(20, 30, 40, 50, 60)
'if value '30' exists in array than the position (which is >0) will be returned
Debug.Print WorksheetFunction.Match(30, myTBL, 0)
The only problem is that if the value doesn't exist the Match function returns an error. Therefore you should use error handling technique.
唯一的问题是，如果该值不存在，Match 函数会返回错误。因此，您应该使用错误处理技术。
That could look like for non existing value '70':
对于不存在的值“70”，这可能看起来像：
'if doesn't exists error would be returned
On Error Resume Next
Debug.Print WorksheetFunction.Match(70, myTBL, 0)
If Err.Number <> 0 Then
Debug.Print "not exists"
Err.Clear
End If
Unfortunately, that will work only in Excel.
不幸的是，这只适用于 Excel。
回答by Michael
Pick a delimiter that won't exist in any of the values (e.g. pipe "|") and you can do it one line:
选择一个在任何值中都不存在的分隔符（例如管道“|”），您可以在一行中完成：
If "|x1|x2|...|xn|" Like "*|" & y & "|*" Then
回答by Mike
Yet another way
又一种方式
Do you have access to MS-Access 2000+? If so add the Access Objects library reference and you will be able to use Eval function:
您可以访问 MS-Access 2000+ 吗？如果是这样，请添加访问对象库引用，您将能够使用 Eval 函数：
result = Eval("'y' IN ('x1', 'x2', '...' 'xn')")
It evaluates string expressions. Some of the SQL operators like the INcan be used.
See documentation
它评估字符串表达式。IN可以使用一些像 的 SQL 运算符。查看文档
