function of a range of cells

[Christopher Brooks]

How do I write a function, like =SUM(B6:B10) so that it
will operate on a range of cells selected in the
worksheet?

Also, is there a way to see the code for the built-in
functions so that I can reverse engineer?

Thanks.

-cnb

 

[Bob Phillips]

How do I write a function, like =SUM(B6:B10) so that it
will operate on a range of cells selected in the
worksheet?

You can't. You could have event code to update a SUM cell on selection.

Also, is there a way to see the code for the built-in
functions so that I can reverse engineer?

Again, you can't, it is company confidential. If you want an algorithm, look
up on the web, there are plenty out there.

 

[Bob Flanagan]

Chris, you can create user defined functions that take a cell range as an
argument. If the function is in a workbook, it can only be used by that
workbook. If it is an add-in, it can be used by all open workbooks. If you
send the workbook using the function to another user, they will need to
authorize macros if it is in the workbooks, or install the add-in if it is
used by the add-in. There are advantages both ways.

The following is a simple user function that returns the number of non-empty
cells in a range

Function NonEmpty(anyR As Range)
Dim cell As Range
Dim I As Long
For Each cell In anyR
If Not IsEmpty(cell) Then I = I + 1
Next
NonEmpty = I
End Function

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel