Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variants vs ranges
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 wrote in message ups.com... 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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variants vs ranges
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 " 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
3D Ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Array Parameters as Variants Only | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming |