Excel Named Function Parameters or UDF without Macros

Question

It seems like a bit of an omission that there's no easy way to create a user-defined declarative function in Excel without defining a macro. I can't use XSLM with the uphill battle that will entail in the Enterprise, but I want to be able to define a function with intent thus.

I want to do this;

=BreakEven(C1:C20)

But I can't use a macro, although I can use a "named formula". The trouble is how to pass parameters to that? I've seen a couple of tricks (kludgy workarounds) but not for xslx.

I'd like to be able to define a Breakeven() function in another tab and reference it here passing in MORE THAN one parameter, two ranges in fact. I'm sure there's some way using string parsing but I can't see it.

I don't mind if the function doesn't look exactly like that, as long as it evaluates within the cell and I can parse it for 'intent'. For instance, this example (http://www.jkp-ads.com/articles/ExcelNames09.asp) which I was unable to get to work in xlsx uses this syntax;

=IF(ROW(D3),CellColor)

Where "cellcolor" is the name of the function and D3 is the range parameter. The other solution I'm toying with is to define a function in column format with a variable argument list (this is two rows of an excel spreadsheet);

[Value][function][parameter1][parameter2][parameter3]
 24050  BreakEven C1:C20      A1:A20

It's not pretty, but the benefit of the latter is that it describes the function to an external reader. We know it's a breakeven function, whereas if we put the actual formula "OFFSET,INDIRECT,SUM()()()()etc" it would not be readable/parseable. Of course, in that case, I'd have to construct the value field by parsing the cells to the right in Excel, which would make the Value formula messy but at least it would be a self-describing row.

Can anyone suggest a better method?

Answer 1

Poor-man's UDF

So I think what we're going to have to do is this;

      A      B         C           D           E
1    [Value][function][parameter1][parameter2][parameter3]
2     24050  BreakEven     C1:C20      A1:A20
3       111      mySum          1          10         100

Where "BreakEven" is a "named function". Here's the formula for "mySum";

=sum(C1:E1)

To evaluate functions listed in B, we just put this in column A (transposing the same value for all rows in column A;

=value(B)

This works because A2 and A3 both evaluate column B as a value, which causes BreakEven and Sum to run (as poor-man's UDFs) in the context of A2 and A3. The range (C1:E1) is relative of course.

So in effect, we can write any function name in column B (as long as there's a corresponding named function defined in the workbook which can be as complex as you like). Columns C, D and E act as the parameters for the function on the same row.

I would have loved to just be able to write the following in column A instead;

=mySum(1,10,100)

But in the absence of that support, the mechanism above serves to provide a readable parameterised function that would be understandable by a user, that's also machine readable (works in CSV too) and allows us to offload our re-usable functions to a library sheet somewhere in the workbook for maintenance.

Not perfect, but an acceptable compromise, unless anyone has a clever way of doing this in a single cell?