Variants vs ranges

OK, I have looked everywhere, and I cannot find the answer to what must
be a
simple question; how do I support ranges and individual cells with the
same
function? In the way that I can put a function in a cell that is
"=SUM(E10,F10,G10,G14:G21)" I also want to be able to pass the same
type of
data into a user defined function of my own.

So far, I have found two function declarations that work well:
Public Function CellsVar(ParamArray InRange() As Variant)
and
Public Function CellsRange(InRange As Range)

The CellsVar form works as long as individual cells are comma
separated. The
second form, CellsRange, works just fine with a single range delimited
with
a colon. However, I have not been able to find a form that allows both.

Going back to the example above with the sum function, how do I declare
the
function so as to support both? I am hopeful that the result keeps the
simplicity of the code within the function, whic resembles the
following:

For Each cell In InRange
blah
blah
blah
Next cell

Thanks in advance for your help!! I cannot figure out why the answer is
so
elusive. This seems like Excel programming 101, and should appear
somewhere in the docs....

Joe,

I don't have the perfect solution, but you could use

Public Function CellsRange(InRange As String)
Dim i As Integer
Dim myAdds As Variant
Dim myCell As Range
myAdds = Split(InRange, ",")
For i = LBound(myAdds) To UBound(myAdds)
For Each myCell In Range(myAdds(i))
MsgBox "I've been passed cell " & myCell.Address
Next myCell
Next i
End Function

Called like

=CellsRange("E10,F10,G10,G14:G21")

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> OK, I have looked everywhere, and I cannot find the answer to what must
> be a
> simple question; how do I support ranges and individual cells with the
> same
> function? In the way that I can put a function in a cell that is
> "=SUM(E10,F10,G10,G14:G21)" I also want to be able to pass the same
> type of
> data into a user defined function of my own.
>
> So far, I have found two function declarations that work well:
> Public Function CellsVar(ParamArray InRange() As Variant)
> and
> Public Function CellsRange(InRange As Range)
>
> The CellsVar form works as long as individual cells are comma
> separated. The
> second form, CellsRange, works just fine with a single range delimited
> with
> a colon. However, I have not been able to find a form that allows both.
>
> Going back to the example above with the sum function, how do I declare
> the
> function so as to support both? I am hopeful that the result keeps the
> simplicity of the code within the function, whic resembles the
> following:
>
> For Each cell In InRange
> blah
> blah
> blah
> Next cell
>
> Thanks in advance for your help!! I cannot figure out why the answer is
> so
> elusive. This seems like Excel programming 101, and should appear
> somewhere in the docs....
>

Public Function MySum(ParamArray SumRange() As Variant) As Variant
Dim Result As Variant
Dim FRange As Range, FCell As Range
Result = 0
On Error Resume Next
For i = 0 To UBound(SumRange)
If FRange Is Nothing Then Set FRange = SumRange(i) _
Else Set FRange = Union(FRange, SumRange(i))
Next i
On Error GoTo 0
If Not FRange Is Nothing Then
For Each FCell In FRange
Result = Result + FCell.Value
Next FCell
End If
MySum = Result
End Function

--
- K Dales


"[email protected]" wrote:

> OK, I have looked everywhere, and I cannot find the answer to what must
> be a
> simple question; how do I support ranges and individual cells with the
> same
> function? In the way that I can put a function in a cell that is
> "=SUM(E10,F10,G10,G14:G21)" I also want to be able to pass the same
> type of
> data into a user defined function of my own.
>
> So far, I have found two function declarations that work well:
> Public Function CellsVar(ParamArray InRange() As Variant)
> and
> Public Function CellsRange(InRange As Range)
>
> The CellsVar form works as long as individual cells are comma
> separated. The
> second form, CellsRange, works just fine with a single range delimited
> with
> a colon. However, I have not been able to find a form that allows both.
>
> Going back to the example above with the sum function, how do I declare
> the
> function so as to support both? I am hopeful that the result keeps the
> simplicity of the code within the function, whic resembles the
> following:
>
> For Each cell In InRange
> blah
> blah
> blah
> Next cell
>
> Thanks in advance for your help!! I cannot figure out why the answer is
> so
> elusive. This seems like Excel programming 101, and should appear
> somewhere in the docs....
>
>

this is only to sum up the numeric value of the range.

Function testsum(ParamArray rn())
Dim l As Integer, h As Integer
Dim i As Long
Dim s
l = LBound(rn)
h = UBound(rn)
For i = l To h
For Each s In rn(i)
testsum = testsum + s.Value
Next
Next
End Function

keizi

<[email protected]> wrote in message
news:[email protected]...
> OK, I have looked everywhere, and I cannot find the answer to what must
> be a
> simple question; how do I support ranges and individual cells with the
> same
> function? In the way that I can put a function in a cell that is
> "=SUM(E10,F10,G10,G14:G21)" I also want to be able to pass the same
> type of
> data into a user defined function of my own.
>
> So far, I have found two function declarations that work well:
> Public Function CellsVar(ParamArray InRange() As Variant)
> and
> Public Function CellsRange(InRange As Range)
>
> The CellsVar form works as long as individual cells are comma
> separated. The
> second form, CellsRange, works just fine with a single range delimited
> with
> a colon. However, I have not been able to find a form that allows both.
>
> Going back to the example above with the sum function, how do I declare
> the
> function so as to support both? I am hopeful that the result keeps the
> simplicity of the code within the function, whic resembles the
> following:
>
> For Each cell In InRange
> blah
> blah
> blah
> Next cell
>
> Thanks in advance for your help!! I cannot figure out why the answer is
> so
> elusive. This seems like Excel programming 101, and should appear
> somewhere in the docs....
>