UDF setup for a range of cells
 

I would like to create a user function that performs a series of calculations
on a range of cells that the user selects. I know this is probablly fairly
easy, but how do I designate the range of cells in VBA and perform my calcs
on that range?

UDF setup for a range of cells
 

Your UDF should accept as parameters the range on which it is to
operate. This will ensure that it will be recalculated with a
relevant cell is changed.It is very poor programming practice to
hard code cell references within a UDF. E.g.,

Public Function MyUDF(InputRange As Range) As Variant
' do your work on InputRange
MyUDF = some_result
End Function

Then you can call this from the worksheet with a formula like

=MyUDF(A1:A10)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"John" wrote in message
...
I would like to create a user function that performs a series of
calculations
on a range of cells that the user selects. I know this is
probablly fairly
easy, but how do I designate the range of cells in VBA and
perform my calcs
on that range?

UDF setup for a range of cells
 

Thanks for the help and quick response Chip... I will start and be off an
running I hope.

"Chip Pearson" wrote:

Your UDF should accept as parameters the range on which it is to
operate. This will ensure that it will be recalculated with a
relevant cell is changed.It is very poor programming practice to
hard code cell references within a UDF. E.g.,

Public Function MyUDF(InputRange As Range) As Variant
' do your work on InputRange
MyUDF = some_result
End Function

Then you can call this from the worksheet with a formula like

=MyUDF(A1:A10)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"John" wrote in message
...
I would like to create a user function that performs a series of
calculations
on a range of cells that the user selects. I know this is
probablly fairly
easy, but how do I designate the range of cells in VBA and
perform my calcs
on that range?

UDF setup for a range of cells
 

Here is a simple summer:


Function summer(r As Range) As Double
Dim rr As Range
For Each rr In r
summer = summer + rr.Value
Next
End Function

In the worksheet, use it like:
=summer(A1:C3)
--
Gary''s Student


"John" wrote:

I would like to create a user function that performs a series of calculations
on a range of cells that the user selects. I know this is probablly fairly
easy, but how do I designate the range of cells in VBA and perform my calcs
on that range?

UDF setup for a range of cells
 

thanks, all tips are appreciated :)

"Gary''s Student" wrote:

Here is a simple summer:


Function summer(r As Range) As Double
Dim rr As Range
For Each rr In r
summer = summer + rr.Value
Next
End Function

In the worksheet, use it like:
=summer(A1:C3)
--
Gary''s Student


"John" wrote:

I would like to create a user function that performs a series of calculations
on a range of cells that the user selects. I know this is probablly fairly
easy, but how do I designate the range of cells in VBA and perform my calcs
on that range?