title: Excel Named Function Parameters or UDF without Macros
body: <p>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.</p>
  
  <p><strong>I want to do this;</strong></p>
  
  <pre><code>=BreakEven(C1:C20)
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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 (<a href="http://www.jkp-ads.com/articles/ExcelNames09.asp" rel="nofollow noreferrer">http://www.jkp-ads.com/articles/ExcelNames09.asp</a>) which I was unable to get to work in xlsx uses this syntax;</p>
  
  <pre><code>=IF(ROW(D3),CellColor)
  </code></pre>
  
  <p>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);</p>
  
  <pre><code>[Value][function][parameter1][parameter2][parameter3]
   24050  BreakEven C1:C20      A1:A20
  </code></pre>
  
  <p>It's not pretty, but the benefit of the latter is that it describes the function to an external reader. We <em>know</em> it's a breakeven function, whereas if we put the <em>actual</em> 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.</p>
  
  <p>Can anyone suggest a better method?</p>