vba Excel 选择案例？

Excel Select Case?

提问by jechaviz

i want to create the "cases" formula for excel to simulate Select case behavior (with multiple arguments and else optional). If A1 and A2 are excel cells, this is the goal:

我想为excel创建“案例”公式来模拟选择案例行为（带有多个参数，其他可选）。如果 A1 和 A2 是 excel 单元格，则目标是：

A1 Case:     A2 Formula:                                                                   A2 Result
5            cases({A1>5,"greather than 5"}, {A1<5, "less than 5"},{else,"equal to 5"})    equal to 5   
Hi           cases({A1="","there is nothing"},{else,A1})                                   Hi
1024         cases({5<A1<=10,10},{11<=A1<100,100},{A1>100,1000})                           1000
12           cases({A1=1 to 9, "digit"}, {A1=11|22|33|44|55|66|77|88|99, "11 multiple"})   (empty) 
60           cases({A1=1 to 49|51 to 99,"not 50"})                                         not 50

If it could, It must accept excel formulas or vba code, to make an operation over the cell before take a case, i.g.

如果可以，它必须接受 excel 公式或 vba 代码，以便在采取案例之前对单元格进行操作，ig

cases({len(A1)<7, "too short"},{else,"good length"})

If it could, it must accept to or more cells to evaluate, i.g.

如果可以，它必须接受一个或多个单元格进行评估，ig

if A2=A3=A4=A5=1 and A1=2, A6="one", A7="two"

如果 A2=A3=A4=A5=1 且 A1=2，则 A6="一个"，A7="两个"

cases(A1!=A2|A3|A4|A5, A6}, {else,A7}) will produce "two"

By the way, | means or, != means different

顺便说一句，| 表示或，!= 表示不同

Any help?

有什么帮助吗？

---

---

I'm grateful.

我很感激。

What I could write was this:

我能写的是：

Public Function arr(ParamArray args())  'Your function, thanks
    arr = args
End Function

Public Function cases(arg, arg2)  'I don't know how to do it better
    With Application.WorksheetFunction
        cases = .Choose(.Match(True, arg, 0), arg2)
    End With
End Function

I call the function in this way

我这样调用函数

=cases(arr(A1>5, A1<5, A1=5),arr( "gt 5", "lt 5", "eq 5"))

And i can't get the goal, it just works for the first condition, A1>5.

而且我无法达到目标，它仅适用于第一个条件，A1> 5。

I fixed it using a for, but i think it's not elegant like your suggestion:

我使用 for 修复了它，但我认为它不像你的建议那样优雅：

Function selectCases(cases, actions)
    For i = 1 To UBound(cases)
        If cases(i) = True Then
            selectCases = actions(i)
            Exit Function
        End If
    Next
End Function

---

---

When i call the function:

当我调用函数时：

=selectCases(arr(A1>5, A1<5, A1=5),arr( "gt 5", "lt 5", "eq 5"))

It works.

有用。

Thanks for all.

谢谢大家。

---

---

After work a little, finally i get a excel select case, closer what i want at first.

下班后，终于得到了一个 excel 选择案例，一开始更接近我想要的。

Function cases(ParamArray casesList())
    'Check all arguments in list by pairs (case, action),
    'case is 2n element
    'action is 2n+1 element
    'if 2n element is not a test or case, then it's like the "otherwise action"
    For i = 0 To UBound(casesList) Step 2
        'if case checks
        If casesList(i) = True Then
            'then take action
            cases = casesList(i + 1)
            Exit Function
        ElseIf casesList(i) <> False Then
            'when the element is not a case (a boolean value),
            'then take the element.
            'It works like else sentence
            cases = casesList(i)
            Exit Function
        End If
    Next
End Function

When A1=5 and I call:

当 A1=5 并且我打电话时：

=cases(A1>5, "gt 5",A1<5, "lt 5","eq 5")

It can be read in this way: When A1 greater than 5, then choose "gt 5", but when A1 less than 5, then choose "lt 5", otherwise choose "eq 5". After run it, It matches with "eq 5"

可以这样读：当A1大于5时，选择“gt 5”，当A1小于5时，选择“lt 5”，否则选择“eq 5”。运行后，匹配“eq 5”

Thank you, it was exciting and truly educative!

谢谢你，这很令人兴奋，也很有教育意义！

回答by jtolle

O.K., there's no way at all to do exactly what you want. You can't use anything other than Excel syntax within a formula, so stuff like 'A1 = 1 to 9' is just impossible.

好吧，根本没有办法做你想做的事。您不能在公式中使用 Excel 语法以外的任何东西，因此像“A1 = 1 到 9”这样的东西是不可能的。

You couldwrite a pretty elaborate VBA routine that took strings or something and parsed them, but that really amounts to designing and implementing a complete little language. And your "code" wouldn't play well with Excel. For example, if you called something like

您可以编写一个非常复杂的 VBA 例程，它接受字符串或其他东西并解析它们，但这实际上相当于设计和实现一门完整的小语言。而且您的“代码”在 Excel 中不能很好地发挥作用。例如，如果您调用类似

=cases("{A1="""",""there is nothing""},{else,A1}")

(note the escaped quotes), Excel wouldn't update your A1 reference when it moved or the formula got copied. So let's discard the whole "syntax" option.

（注意转义引号），Excel 在移动或公式被复制时不会更新您的 A1 引用。所以让我们放弃整个“语法”选项。

However, it turns out you canget much of the behavior I think you actually want with regular Excel formulas plus one tiny VBA UDF. First the UDF:

然而，事实证明，你能得到多少我想到你居然要与普通Excel公式加一个很小的VBA UDF的行为。首先是UDF：

Public Function arr(ParamArray args())
    arr = args
End Function

This lets us create an array from a set of arguments. Since the arguments can be expressions instead of just constants, we can call it from a formula like this:

这让我们可以从一组参数创建一个数组。由于参数可以是表达式而不仅仅是常量，我们可以从这样的公式中调用它：

=arr(A1=42, A1=99)

and get back an array of boolean values.

并取回一个布尔值数组。

With that small UDF, you can now use regular formulas to "select cases". They would look like this:

使用那个小的 UDF，您现在可以使用常规公式来“选择案例”。它们看起来像这样：

=CHOOSE(MATCH(TRUE, arr(A1>5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5")

What's going on is that 'arr' returns a boolean array, 'MATCH' finds the position of the first TRUE, and 'CHOOSE' returns the corresponding "case".

发生的事情是'arr'返回一个布尔数组，'MATCH'找到第一个TRUE的位置，'CHOOSE'返回相应的“case”。

You can emulate an "else" clause by wrapping the whole thing in 'IFERROR':

您可以通过将整个内容包装在“IFERROR”中来模拟“else”子句：

=IFERROR(CHOOSE(MATCH(TRUE, arr(A1>5, A1<5), 0), "gt 5", "lt 5"), "eq 5")

If that is too verbose for you, you can always write another VBA UDF that would bring the MATCH, CHOOSE, etc. inside, and call it like this:

如果这对您来说太冗长了，您可以随时编写另一个 VBA UDF，将 MATCH、CHOOSE 等内容带入其中，并像这样调用它：

=cases(arr(A1>5, A1<5, A1=5), "gt 5", "lt 5", "eq 5")

That's not far off from your proposed syntax, and much, much simpler.

这与您提出的语法相去甚远，而且要简单得多。

EDIT:

编辑：

I see you've already come up with a (good) solution that is closer to what you really want, but I thought I'd add this anyway, since my statement above about bringing MATCH, CHOOSE, etc. inside the UDF made it look easier thatn it really is.

我看到你已经想出了一个更接近你真正想要的（好的）解决方案，但我想我还是会添加这个，因为我上面关于在 UDF 中引入 MATCH、CHOOSE 等的声明使它看起来更容易，那确实如此。

So, here is a 'cases' UDF:

所以，这是一个“案例”UDF：

Public Function cases(caseCondResults, ParamArray caseValues())
    On Error GoTo EH

    Dim resOfMatch
    resOfMatch = Application.Match(True, caseCondResults, 0)

    If IsError(resOfMatch) Then
        cases = resOfMatch
    Else
        Call assign(cases, caseValues(LBound(caseValues) + resOfMatch - 1))
    End If

    Exit Function

EH:
    cases = CVErr(xlValue)
End Function

It uses a little helper routine, 'assign':

它使用了一个小助手程序，“分配”：

Public Sub assign(ByRef lhs, rhs)
    If IsObject(rhs) Then
        Set lhs = rhs
    Else
        lhs = rhs
    End If
End Sub

The 'assign' routine just makes it easier to deal with the fact that users can call UDFs with either values or range references. Since we want our 'cases' UDF to work like Excel's 'CHOOSE', we'd like to return back references when necessary.

“分配”例程只是更容易处理用户可以使用值或范围引用调用 UDF 的事实。由于我们希望我们的“案例”UDF 像 Excel 的“选择”一样工作，因此我们希望在必要时返回引用。

Basically, within the new 'cases' UDF, we do the "choose" part ourselves by indexing into the param array of case values. I slapped an error handler on there so basic stuff like a mismatch between case condition results and case values will result in a return value of #VALUE!. You would probably add more checks in a real function, like making sure the condition results were booleans, etc.

基本上，在新的“案例”UDF 中，我们通过索引案例值的 param 数组来自己完成“选择”部分。我在那里打了一个错误处理程序，所以基本的东西，比如案例条件结果和案例值之间的不匹配将导致 #VALUE! 的返回值。您可能会在实际函数中添加更多检查，例如确保条件结果是布尔值等。

I'm glad you reached an even better solution for yourself, though! This has been interesting.

不过，我很高兴您为自己找到了更好的解决方案！这很有趣。

MORE ABOUT 'assign':

有关“分配”的更多信息：

In response to your comment, here is more about why that is part of my answer. VBA uses a different syntax for assigning an object to a variable than it does for assigning a plain value. Look at the VBA help or see this stackoverflow question and others like it: What does the keyword Set actually do in VBA?

为了回应您的评论，这里有更多关于为什么这是我的答案的一部分。VBA 将对象分配给变量的语法与分配普通值的语法不同。查看 VBA 帮助或查看此 stackoverflow 问题和其他类似问题：关键字 Set 在 VBA 中实际执行的操作是什么？

This matters because, when you call a VBA function from an Excel formula, the parameters can be objects of type Range, in addition to numbers, strings, booleans, errors, and arrays. (See Can an Excel VBA UDF called from the worksheet ever be passed an instance of any Excel VBA object model class other than 'Range'?)

这很重要，因为当您从 Excel 公式调用 VBA 函数时，除了数字、字符串、布尔值、错误和数组之外，参数还可以是 Range 类型的对象。（请参阅从工作表调用的 Excel VBA UDF 是否可以传递除“Range”以外的任何 Excel VBA 对象模型类的实例？）

Range references are what you describe using Excel syntax like A1:Q42. When you pass one to an Excel UDF as a parameter, it shows up as a Range object. If you want to return a Range object from the UDF, you have to do it explicitly with the VBA 'Set' keyword. If you don't use 'Set', Excel will instead take the valuecontained within the Range and return that. Most of the time this doesn't matter, but sometimes you want the actual range, like when you've got a named formula that must evaluate to a range because it's used as the source for a validation list.

范围引用是您使用 Excel 语法（如 A1:Q42）描述的内容。当您将一个作为参数传递给 Excel UDF 时，它会显示为 Range 对象。如果要从 UDF 返回 Range 对象，则必须使用 VBA 'Set' 关键字显式执行此操作。如果您不使用“设置”，Excel 将取而代之的是范围内包含的值并返回该值。大多数情况下这无关紧要，但有时您需要实际范围，例如当您有一个命名公式时，该公式必须计算为一个范围，因为它被用作验证列表的来源。