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?

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?

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?

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?

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?