Excel VBA：如果 ActiveCell 像“*string*”，则选择大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18409418/
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
Excel VBA: Select Case if ActiveCell like "*string*"
提问by thedeepfield
I'm working on a macro that takes the current value of the ActiveCelland changes that value based on a select case.
我正在处理一个宏，它获取 的当前值ActiveCell并根据选择情况更改该值。
However, I am unable to determine if the ActiveCellcontains a wild card string. I am not sure if my syntax is correct. How can I get my select case to compare?
但是，我无法确定 是否ActiveCell包含通配符字符串。我不确定我的语法是否正确。我怎样才能让我的选择案例进行比较？
Select Case ActiveCell.Value
Case ActiveCell.Value Like "*string*"
ActiveCell.Value = "contains string"
End Select
回答by Nero057
It is possible to use wildcards. Keep these two things in mind: First, string comparison expressions evaluate to a Boolean data type (True/False); Second, per the developer reference for the Select...Case statement, any Case expression(s)must be "implicitly convertible" to the same data type as that of the Select Case test expression. To demonstrate, let's use the code from the original post above.
可以使用通配符。请记住这两件事：首先，字符串比较表达式的计算结果为布尔数据类型 (True/False)；其次，根据 Select...Case 语句的开发人员参考，任何Case 表达式都必须“隐式转换”为与 Select Case测试表达式相同的数据类型。为了演示，让我们使用上面原始帖子中的代码。
Select Case ActiveCell.Value '-->ActiveCell.Value is the test expression
Case ActiveCell.Value Like "*string*" '-->(ActiveCell.Value Like "*string*") is the Case expression.
ActiveCell.Value = "contains string"
End Select
If we selected a cell containing a string value in any worksheet, then used the Immediate Window to test the data type of these two expressions using the TypeName() function, we would get the following:
如果我们在任何工作表中选择一个包含字符串值的单元格，然后使用立即窗口使用 TypeName() 函数测试这两个表达式的数据类型，我们将得到以下结果：
?TypeName(ActiveCell.Value)
String
?TypeName(ActiveCell.Value Like "*string*")
Boolean
As you can see, Select...Case will not work here because the data types are not implicitly the same (a minor exception to this would be if the macro was run on any cells in a worksheet that contained the single-word values of "True" or "False", which Excel automatically converts to Boolean).
如您所见， Select...Case 在这里不起作用，因为数据类型并不隐式相同（如果宏在包含“真”或“假”，Excel 会自动将其转换为布尔值）。
The solution is actually a very simple one. Just change the test expression to True.
解决方法其实很简单。只需将测试表达式更改为True。
Select Case True
Case (ActiveCell.Value Like "*string*")
ActiveCell.Value = "contains string"
End Select
This is essentially the same as writing:
这与编写基本相同：
If (ActiveCell.Value Like "*string*") = True Then ActiveCell.Value = "contains string"
It's mostly a matter of personal preference whether you use If...Then or Select...Case. I personally like the Select...Case construct due to the readability of the code, but also for other benefits (such as the ability to pass each Case a list of expressions separated by commas rather than using an OR operator, making the code more concise).
无论您使用 If...Then 还是 Select...Case，这主要取决于个人喜好。由于代码的可读性，我个人喜欢 Select...Case 结构，但也有其他好处（例如能够向每个 Case 传递以逗号分隔的表达式列表而不是使用 OR 运算符，使代码更简洁的）。
回答by Aaron Thomas
Is and like cannot be used as comparison operators in a select case statement in VBA.
If possible it would be better to substitute in an if-then statement:
Is 和 like 不能在 VBA 的 select case 语句中用作比较运算符。
如果可能，最好替换为 if-then 语句：
If ActiveCell.Value Like "*string*" Then ActiveCell.Value = "string"
From the VBA glossary for comparison operators:
来自比较运算符的 VBA 词汇表：
A character or symbol indicating a relationship between two or more values or expressions. These operators include less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), not equal (<>), and equal (=). Additional comparison operators include Is and Like. Note that Is and Like can't be used as comparison operators in a Select Case statement.
表示两个或多个值或表达式之间关系的字符或符号。这些运算符包括小于 (<)、小于或等于 (<=)、大于 (>)、大于或等于 (>=)、不等于 (<>) 和等于 (=)。其他比较运算符包括 Is 和 Like。请注意 Is 和 Like 不能用作 Select Case 语句中的比较运算符。
