If your UDF accepts a Range object as a parameter, you can use code
like the following:

Function Test(RR As Range) As Double
Dim RNdx As Long
Dim Result As Double

For RNdx = 1 To RR.Rows.Count
Result = Result + (RR(RNdx, 1) / RR(RNdx, 2))
Next RNdx
Test = Result
End Function

This function sums the ratio of each pair of values in the rows of the
range that was passed in. E.g.,

1 2
3 4
5 6

=Test(A1:B4)

returns 2.9583

This function will work regardless of what sheet might be active. If
for some reason you need to know from which cell the function is
called, Application.Caller will reference the Range from which the
function was called. From that, you can get the Worksheet and Workbook
if desired.

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:58:19 -0700 (PDT), Greg
wrote:

What is the best way to loop through a range argument in a User
Defined Function that is used n a worksheet cell when the range is a 2-
column range of cells and I want to do calculations with the numbers
by row for each pair of values? I need it to work in cases when the
worksheet is recalculated and the active sheet may be different from
the sheet that has the cell where the function is located.

Greg