OK, there is no way to do what you want. You cannot use anything but the Excel syntax inside the formula, so things like "A1 = 1 to 9" are simply not possible.
You can write a rather complicated VBA procedure that took strings or something else and analyzed them, but it really comes down to developing and implementing a complete little language. And your "code" will not work well with Excel. For example, if you called something like
=cases("{A1="""",""there is nothing""},{else,A1}")
(note the escaped quotes), Excel will not update your A1 link when you move it or copy the formula. Therefore, discard the entire syntax parameter.
However, it turns out that you can get most of the behavior that I think you really need with the usual Excel formulas plus one tiny VBA UDF. UDF first:
Public Function arr(ParamArray args()) arr = args End Function
This allows us to create an array from a set of arguments. Since arguments can be expressions, not just constants, we can call this from the formula as follows:
=arr(A1=42, A1=99)
and return an array of booleans.
With this little UDF, you can now use regular formulas to "select cases." They would look like this:
=CHOOSE(MATCH(TRUE, arr(A1>5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5")
What happens is that 'arr' returns a boolean array, "MATCH" finds the position of the first TRUE, and "CHOOSE" returns the corresponding "case".
You can emulate the else clause by wrapping it all in IFERROR:
=IFERROR(CHOOSE(MATCH(TRUE, arr(A1>5, A1<5), 0), "gt 5", "lt 5"), "eq 5")
If this is too much for you, you can always write another VBA UDF that will result in MATCH, CHOOSE, etc. inside and will call it like this:
=cases(arr(A1>5, A1<5, A1=5), "gt 5", "lt 5", "eq 5")
This is not far from your suggested syntax and much, much simpler.
EDIT:
I see that you have already come up with a (good) solution that is closer to what you really want, but I thought I'd add this anyway, since my expression above is about casting MATCH, CHOOSE, etc. inside UDF made it look easier than it really is.
So, here are the “UDF” cases:
Public Function cases(caseCondResults, ParamArray caseValues()) On Error GoTo EH Dim resOfMatch resOfMatch = Application.Match(True, caseCondResults, 0) If IsError(resOfMatch) Then cases = resOfMatch Else Call assign(cases, caseValues(LBound(caseValues) + resOfMatch - 1)) End If Exit Function EH: cases = CVErr(xlValue) End Function
It uses a small helper procedure, 'assign':
Public Sub assign(ByRef lhs, rhs) If IsObject(rhs) Then Set lhs = rhs Else lhs = rhs End If End Sub
The assign procedure simply simplifies the consideration of the fact that users can invoke UDFs with any values ​​or range references. Since we want the UDF of our “cases” to work like Excel “CHOOSE”, we would like to return links if necessary.
Basically, in the new UDF “cases”, we ourselves make a “choice” by indexing case values ​​into an array of parameters. I removed the error handler there, so basic things, such as a mismatch between the results of the case condition and the case values, will result in the return value #VALUE !. You would probably add more checks to the real function, for example, to make sure that the results of the condition were logical, etc.
I am glad that you have reached an even better solution for yourself! That was interesting.
MORE ABOUT 'assign':
In response to your comment, here is more about why this is part of my answer. VBA uses a different syntax to assign an object to a variable than to assign a simple value. Look at the VBA help or look at this stackoverflow question, and others will like: What does the Set keyword really do in VBA?
This matters because when you call a VBA function from an Excel formula, the parameters can be objects of type Range, in addition to numbers, strings, booleans, errors, and arrays. (See Can Excel VBA UDF called from a worksheet ever pass an instance of any Excel VBA object model class other than "Range"? )
Range references are what you describe using Excel syntax such as A1: Q42. When you pass one to Excel UDF as a parameter, it displays as a Range object. If you want to return a Range object from UDF, you need to do this explicitly using the VBA keyword “Install”. If you do not use Install, Excel will instead accept the value contained within the range and return it. In most cases, this does not matter, but sometimes you need the actual range, for example, when you have a named formula that needs to be evaluated in the range, because it is used as the source for the check list.