PDA

View Full Version : Solved: passing cells to a function



philfer
10-30-2008, 06:50 AM
Hello,

I am trying to write a custom function that I intend to distribute as an add-in.

However I need to know whether the user clicked on one cell or ten cells before I can get the function to process them. How do I get the function to detect this. Do I pass the target range as a parameter to the function. How can I then process each individual cell.

I hope I am making some sense!

Thanks for your help in advance
Phil

Bob Phillips
10-30-2008, 06:52 AM
A simple example


Public Function myFunc(rng As Range)
Dim cell As Range

For Each cell In rng

'... do stuff
Next cell

'set return value(s)
End Function

Krishna Kumar
10-30-2008, 06:53 AM
May be..

MsgBox Selection.Cells.Count

philfer
10-30-2008, 06:55 AM
just a follow up question :-

if the user entered =myfunc(

and then clicked a few non contiguous cells would these cells be automatically passed as the parameter to the function?

Cheers

Thanks for all the other repliers too

Bob Phillips
10-30-2008, 07:22 AM
No, that would be many parameters. Best to handle that as an array



Public Function myFunc(ParamArray rng())
Dim cell As Range
Dim i As Long

For i = LBound(rng) To UBound(rng)

For Each cell In rng(i)

'... do stuff
Debug.Print cell.Address
Next cell
Next i

'set return value(s)
End Function