Excel VBA：如果ActiveCell喜欢“* string *”，则选择大小写 [英] Excel VBA: Select Case if ActiveCell like "*string*"

问题描述

我正在处理一个宏，它接收到活动单元的当前值，并根据选择大小写更改该值。但是，我无法确定活动单元是否包含通配符。我不知道我的语法是否正确。如何获得我的选择案例进行比较？

I'm working on a macro that takes the current value of the activecell and changes that value based on a select case. However, I am unable to determine if the activecell contains a wild card string. I am not sure if my syntax is correct. How can I get my select case to compare?

    Select Case ActiveCell.Value

        Case ActiveCell.Value Like "*string*"
            ActiveCell.Value = "contains string"

    End Select

推荐答案

可以使用通配符。记住这两点：首先，字符串比较表达式求值为布尔数据类型（True / False）;第二，根据Select ... Case语句的开发人员参考，任何 Case表达式必须隐式转换为与Select Case 测试表达式相同的数据类型。要演示，请使用上述原始帖子中的代码。

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.

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

如果我们在任何工作表中选择了一个包含字符串值的单元格，则使用立即窗口使用TypeName（）函数测试这两个表达式的数据类型，我们将得到以下内容：

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(ActiveCell.Value)
 String
?TypeName(ActiveCell.Value Like "*string*")
 Boolean

如你所见，选择... Case不会在这里工作，因为数据类型不是隐式的相同（这是一个小例外，如果宏在工作表中的任何单元格上运行，其中包含单字值为True或False，Excel会自动转换为布尔值）。

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).

解决方案实际上是一个非常简单的解决方案。只需将测试表达式更改为 True 。

The solution is actually a very simple one. Just change the test expression to True.

Select Case True

    Case (ActiveCell.Value Like "*string*")
        ActiveCell.Value = "contains string"

End Select

这与写作基本相同：

If (ActiveCell.Value Like "*string*") = True Then ActiveCell.Value = "contains string"

关于个人喜好的问题，无论您是否使用If ... Then或Select ... Case。我个人喜欢Select ... Case构造，因为代码的可读性，而且还有其他好处（例如通过逗号分隔的每个Case都可以传递表达式的列表，而不是使用OR运算符，使代码更多简洁）。

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).

这篇关于Excel VBA：如果ActiveCell喜欢“* string *”，则选择大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！