Select Case(Switch) formula in Excel

Question

Before Office16 (https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e) there is no formula for Switch. I used to use multiple IF formulas, or VLOOKUPs. So I have created an UDF for these cases, it is in the answer. This is my first UDF, I have tested it multiple times. If you are interesed please take a look, and tell me, if there is anything to modify.

It isn't a question, hence the down/close vote. If you want some working code reviewed - then post it on [Code Review](http://codereview.stackexchange.com), not Stack Overflow — SierraOscar, Mar 23 '17 at 11:37

donmichael · Answer 1 · 2017-03-23T10:57:42.237

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

Select Case(Switch) formula in Excel

1 Answers1