Find size of array passed to user-defined function
 

I'd like to write a function which accepts a list and
executes a for loop with operations on each element in
the list. I can't figure out how to get the size of the
list. For example, my call in an excel cell would be

=myfun(A1:A10)

and the VBA code is

Public Function myfun(data)
Dim idx As Integer

size = ???
For idx = 1 To size
... data(idx) ...
Next idx

How do I get the size of data (10 in this example)? It
would be nice if it worked for both horizontal and
vertical arrays, but I'll take either.

Thanks!

Find size of array passed to user-defined function
 

Hi Cliff:

Public Function myfun(rData As Range)
Dim idx As Integer
For idx = 1 To rData.Cells.Count
' ... data(idx) ...
Next idx
' ...
End Function

Regards,

Vasant.

"Cliff" wrote in message
...
I'd like to write a function which accepts a list and
executes a for loop with operations on each element in
the list. I can't figure out how to get the size of the
list. For example, my call in an excel cell would be

=myfun(A1:A10)

and the VBA code is

Public Function myfun(data)
Dim idx As Integer

size = ???
For idx = 1 To size
... data(idx) ...
Next idx

How do I get the size of data (10 in this example)? It
would be nice if it worked for both horizontal and
vertical arrays, but I'll take either.

Thanks!

Find size of array passed to user-defined function
 

since you are passing a range

Public Function MyFun(rng as Range)
dim cell as Range
for each cell in rng
sStr = sStr & cell.Address(0,0) & ","
Next
sStr = left(sStr,len(sStr)-1)
MyFun = sStr
End Sub

--
Regards,
Tom Ogilvy

Cliff wrote in message
...
I'd like to write a function which accepts a list and
executes a for loop with operations on each element in
the list. I can't figure out how to get the size of the
list. For example, my call in an excel cell would be

=myfun(A1:A10)

and the VBA code is

Public Function myfun(data)
Dim idx As Integer

size = ???
For idx = 1 To size
... data(idx) ...
Next idx

How do I get the size of data (10 in this example)? It
would be nice if it worked for both horizontal and
vertical arrays, but I'll take either.

Thanks!