在Excel中选择案例（切换）公式

问题描述：

在Office16（https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e）之前没有Switch的公式。我曾经使用多个IF公式或VLOOKUP。所以我为这些案例创建了一个UDF，它在答案中。这是我的第一个UDF，我已经多次测试过它。如果你不喜欢，请看看，并告诉我，如果有什么要修改的话。在Excel中选择案例（切换）公式

谢谢减去没有评论:) – donmichael

这不是一个问题，因此下/接近的投票。如果你想检查一些工作代码 - 然后张贴在[代码评论]（http://codereview.stackexchange.com），而不是堆栈溢出 –

答

Function MySwitch(ParamArray a() As Variant) 
Dim d As Integer 
Dim result As Variant 

d = UBound(a) 


myexp = a(0) 
On Error GoTo ErrHandler 
If d Mod 2 <> 0 Then 
    For i = 1 To d - 1 
     If a(i) Like ">#*" Then 
      a(i) = CInt(Replace(a(i), ">", "")) 
      Select Case myexp 
       Case Is > a(i) 
        result = a(i + 1) 
       End Select 
     ElseIf a(i) Like "<#*" Then 
      a(i) = CInt(Replace(a(i), "<", "")) 
      Select Case myexp 
       Case Is < a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "=#*" Then 
      a(i) = CInt(Replace(a(i), "=", "")) 
      Select Case myexp 
       Case a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "<>#*" Then 
      a(i) = CInt(Replace(a(i), "<>", "")) 
      Select Case myexp 
       Case Is <> a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "><#*" Then 
      a(i) = CInt(Replace(a(i), "><", "")) 
      Select Case myexp 
       Case Is <> a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "=>#*" Then 
      a(i) = CInt(Replace(a(i), "=>", "")) 
      Select Case myexp 
       Case Is >= a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like ">=#*" Then 
      a(i) = CInt(Replace(a(i), ">=", "")) 
      Select Case myexp 
       Case Is >= a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "=<#*" Then 
      a(i) = CInt(Replace(a(i), "=<", "")) 
      Select Case myexp 
       Case Is <= a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "<=#*" Then 
      a(i) = CInt(Replace(a(i), "<=", "")) 
      Select Case myexp 
       Case Is <= a(i) 
        result = a(i + 1) 
      End Select 
     Else 
      Select Case myexp 
       Case a(i) 
        result = a(i + 1) 
      End Select 
     End If 
    If Not result = vbNullString Then 
     MySwitch = result 
     Exit Function 
    End If 
    i = i + 1 
    Next i 
    result = a(d) 
ElseIf d Mod 2 = 0 Then 
    For i = 1 To d 
     If a(i) Like ">#*" Then 
      a(i) = CInt(Replace(a(i), ">", "")) 
      Select Case myexp 
       Case Is > a(i) 
        result = a(i + 1) 
       End Select 
     ElseIf a(i) Like "<#*" Then 
      a(i) = CInt(Replace(a(i), "<", "")) 
      Select Case myexp 
       Case Is < a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "=#*" Then 
      a(i) = CInt(Replace(a(i), "=", "")) 
      Select Case myexp 
       Case a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "<>#*" Then 
      a(i) = CInt(Replace(a(i), "<>", "")) 
      Select Case myexp 
       Case Is <> a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "><#*" Then 
      a(i) = CInt(Replace(a(i), "><", "")) 
      Select Case myexp 
       Case Is <> a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "=>#*" Then 
      a(i) = CInt(Replace(a(i), "=>", "")) 
      Select Case myexp 
       Case Is >= a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like ">=#*" Then 
      a(i) = CInt(Replace(a(i), ">=", "")) 
      Select Case myexp 
       Case Is >= a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "=<#*" Then 
      a(i) = CInt(Replace(a(i), "=<", "")) 
      Select Case myexp 
       Case Is <= a(i) 
        result = a(i + 1) 
      End Select 
     ElseIf a(i) Like "<=#*" Then 
      a(i) = CInt(Replace(a(i), "<=", "")) 
      Select Case myexp 
       Case Is <= a(i) 
        result = a(i + 1) 
      End Select 
     Else 
      Select Case myexp 
       Case a(i) 
        result = a(i + 1) 
      End Select 
     End If 
    If Not result = vbNullString Then 
     MySwitch = result 
     Exit Function 
    End If 
    i = i + 1 
    Next i 
End If 

MySwitch = result 
Exit Function 
ErrHandler: 
If Err.Number <> 0 Then 
    Msg = "Error # " & Str(Err.Number) & " was generated by " _ 
     & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description 
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext 
MySwitch = Err.Description 
End If 
End Function

在Excel中选择案例（切换）公式

相关推荐