Finding the location of a function programatically

LoopCost

New Member
Joined
Sep 8, 2002
Messages
8
How do I find the current location of a function? I am creating a new function to be used in excel like Sumif, Count, etc.

I want to evaluate a named range but I dont want to evaluate previous rows. Say I had a named 1 x 10 range (myRange) to search. In the next column I use myFunction to evaluate the named range. On the first instance I want to evaluate all 10 rows. On the next occurence of myFunction I want to evaluate 9 rows and so on. I could do this if I knew from what cell my function was running
from. I need to do this without adjusting the named range. Any ideas??

Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Nope, I don't understand... I think you need an array formula, for the UDF to "know" what has happened to its "surroundings", but, not sure what you want.
 

Swamp Thing

Active Member
Joined
Aug 16, 2002
Messages
313
Check out Application.Caller. This will return a range object if the function has been called from a cell. The range obj. will then refer to the cell containing the function call.

E.g. Application.Caller.Address will return the address of the cell from which the function is called.
 

Forum statistics

Threads
1,144,741
Messages
5,726,010
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top