what is the best way to loop through a 2-column range argument ofnumbers in a UDF?

[Greg]

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

 

[Dave Peterson]

Make sure you pass the range to the function.

Function myFunc(rng as range) as variant 'or as long or as ...

Then use:
=myfunc('sheet 99'!a1:b10)

If you don't pass the range(s) that the function needs, then it won't know when
to recalculate. Making the function volatile will mean that the function could
be one full calculation behind -- so the results should not be trusted until
that recalc.

Function myFunc(rng as range) as variant

dim myCell as range
dim mySum as double

mySum = 0

'some validation
if rng.areas.count > 1 then
myfunc = "just a single area, please"
exit function
end if

if rng.columns.count > 2 then
myfunc = "just two columns, please"
exit function
end if

'then you have a few options.
'you could loop through the cells in the first column
for each mycell in rng.columns(1)
if lcase(mycell.text) = lcase("hi there") then
if isnumeric(mycell.offset(0,1).value) then
mysum = mysum + mycell.offset(0,1).value
end if
end if
next mycell

myfunc = mysum

end function

(untested, uncompiled.)

You could also loop through the rows, then loop through the columns.

dim mycell as range
dim myrow as range
'same checks

for each myrow in rng.rows
for each mycell in myrow.cells
'do something
next mycell
next myrow

 

[Chip Pearson]

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)

 

[Greg]

Chip's example worked perfectly and was exactly what I was looking for.