Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UDF implicit range argument
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UDF implicit range argument
Named ranges are quite normal in Excel - plenty of info on the web and in
Help. Eg: http://www.contextures.com/xlNames01.html http://www.exceltip.com/exceltips.ph...category&ID=24 What does the rest of your UDF look like ? - that might make a difference in how it would work with named ranges as inputs... Tim "Teach1001" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UDF implicit range argument
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 a - 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 "Tim Williams" wrote in message ... Named ranges are quite normal in Excel - plenty of info on the web and in Help. Eg: http://www.contextures.com/xlNames01.html http://www.exceltip.com/exceltips.ph...category&ID=24 What does the rest of your UDF look like ? - that might make a difference in how it would work with named ranges as inputs... Tim "Teach1001" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
implicit file reference | Excel Programming | |||
Possible to chart data for dates implicit within a range? | Charts and Charting in Excel | |||
implicit activation | Excel Programming | |||
Function (array argument, range argument, string argument) vba | Excel Programming | |||
How to pass an Excel range as an argument to a SQL Server stored Procedure | Excel Programming |