Find size of array passed to user-defined function

  • Thread starter Thread starter Cliff
  • Start date Start date
C

Cliff

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!
 
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.
 
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
 
Back
Top