Формула выбора регистра (переключателя) в Excel

Question

До Office16 ( https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e) не существует формулы для Switch. Я использовал несколько формул IF или VLOOKUP. Поэтому я создал UDF для этих случаев, это ответ. Это мой первый UDF, я проверял его несколько раз. Если вы заинтересованы, пожалуйста, посмотрите, и скажите мне, если есть что-то изменить.

Answer 1

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