User-defined function that mimics the behavior of case select

I'm looking to create my own case select function that behaves basically like the =IF built-in function but can be used without nesting. It's hard to come by detailed tutorials on function building, especially for what I'm trying to do since it's more complicated than calculating the area of a rectangle, for example.

The code below is what I have so far. I know it's not correct, but I don't know how to how to put it together. I'd like to have a larger set of optional arguments too, maybe 5 or 6.

Public Function CaseSelect(Case1 As Variant, Do1 As Variant, Optional Case2 As Variant, Optional Do2 As Variant)

    Dim MyCase As Variant
    
    Select Case MyCase
        Case Case1
            Do1
        Case Case2
            Do2
        Case Else
    End Select

End Function

This looks like a good example to start from: http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx Essentially all you are doing differently from this example is that you appear to want to put some of the function arguments as "case" comparisons. Here's another example of a simple UDF that uses some of the function's arguments as cases in the select case statement.

Function testcase(value, case1, case2, case3)
Select Case value
     Case "a"
        testcase = "a"
    Case case1
        testcase = case1
    Case case2
        testcase = case2
    Case case3
        testcase = case3
End Select
End Function

I think this will do what you want.
But its kind of weird.
Im not really sure why haha.
Also, you can add more cases, I just did 1.
And I just changed it to Sub instead of Function since it doesnt actually return anything right now.

Public Sub CaseSelect(Case1 As Variant, Macro1 As String, Macro2 As String)
Dim MyCase As Variant
MyCase = 0   ' Just set it as 0 as an example
Select Case MyCase
    Case Case1
        Application.Run Macro1
    Case Else
        Application.Run Macro2
End Select
End Sub

Sub MyMainSub()
Application.Run "CaseSelect", 0, "Macro1", "Macro2"
End Sub

Sub Macro1()
MsgBox ("Case1")
End Sub

Sub Macro2()
MsgBox ("Case2")
End Sub

I think he means Do1 and Do2 as a series of commands.

Except that he is passing Do1 and Do2 to the function through the argument list, so I don't think they can be a series of commands. He could certainly do more to do1 and do2 before returning the value.

I haven't got time right now to look at this in more detail. Looking at data types in this calling "statement," it looks like each case argument will be a boolean, and each Do argument will be a value (double or long depending on the calculation performed). The function's overall purpose is to determine which "case" argument is true, and return the corresponding "Do" value. Assuming you decide a Select Case statement is still the way to go, it seems like it should be as simple as

Select case TRUE
case case1
caseselect=do1
etc.

I think what you want would look more like this:

Function mySwitch(ParamArray avArg() As Variant) As Variant
    Dim i As Long
    
    If UBound(avArg) And 1 Then
        For i = 0 To UBound(avArg) Step 2
            If avArg(i) Then
                mySwitch = avArg(i + 1)
                Exit For
            End If
        Next i
    Else
        mySwitch = "Must have even number of arguments!"
    End If
End Function

E.g.,

=mySwitch(A1>B2, SUM(A1, 5), A1=7, A1^2)