Does anyone know of a good tutorial on the best way to loop through
the values of a 2-D input range in a UDF?

See my reply to your later post. In short, you should pass a Range as
a parameter and do your calculations based on that range. In this
case, it doesn't matter which might be active when the calculation
takes place -- the Range parameter will always refer to the correct
worksheet. If for some reason you need to get the cell or worksheet on
which the UDF function was entered, you can use Application.Caller.
This will return a reference to the cell containing the formula. From
that, you can get a Worksheet reference and a Workbook reference.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 7 Apr 2009 21:40:00 -0700 (PDT), Greg
wrote:

I did some debugging and I found out that the loop "For Each Cell in
Subset Range" is looping through the cells in the worksheet that is
active at the time the macro is run, instead of the worksheet of the
cell that contains the function. I need to find a way to loop through
the values on the sheet where the function is located instead of the
active sheet at the time when the subroutine is run regardless of
which sheet is active when the workbook is recalculated.

Does anyone know of a good tutorial on the best way to loop through
the values of a 2-D input range in a UDF?