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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

I think what you want would look more like this:

Please Login or Register  to view this content.

E.g.,

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