Excel UDF implicit range argument


T

Teach1001

I have a UDF that works fine and have distributed it to students on an add-in.
It is of the form fun(one as variant, two as variant) as variant
Now I find that some students do a very clever thing. They are working on
financial tables and name the rows with names like sales, cost, and profit.
Then, to calculate profit they simply enter =sales - cost. Copy the formula
to the right to the other columns and it looks the same, but it actually
grabs the right values of sales and cost in each column. I don't see this
documented, but type sales in any column and your will get the figure out of
the sales row for that column.
My problem is that my UDF will not accept this kind of argument. The work
around is simply to write FUN( rate, 0+sales), forcing Excel to convert the
sales reference to a specific number before calling the function.
Is there a way to avoid this work around? And, what is the name of this
strange referencing method my students are using?
 
Ad

Advertisements

Ad

Advertisements

N

Niek Otten

Hi Tim,

The reference method is called Implicit Intersection. Be careful, it works
correctly only if the function requires a one cell argument. You can't use
it for the MAX function, for example.

Alas, Excel 5 was the last version where implicit intersection worked for
UDFs.

The three workarounds I know are:
- the one you describe (any arithmetic will do)
- explicit intersection using the intersection operator, which is the space
character. Then you would write something like =FUN(rate,sales 1:1). Of
course the reference to the row will adjust automatically when you copy or
drag the formula.
This method works elegantly if you use the R1C1 reference style; the formula
then would be =FUN(rate,sales R), where R stands for "this row". Similarly,
=FUN(rate, sales R[-1]) would mean the row above, very handy for financial
projections.
- wrap the VBA UDF in an Excel4 (XLM, (not XML)) function. Implicit
intersection works fine for XLM functions, if you define the argument as
being just one cell.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top