Excel user defined functions.
 

I'd like to be able to define my own functions in Excel... for example: SPAN
could calculate the span of values in a selection (highest minus lowest) with
a single function rather than using MAX(C#R#:C#R#)-MIN(C#R#:C#R#) all the
time.

Try:

Function Span(rng As Range) As Double
With Application
Span = .Max(rng) - .Min(rng)
End With
End Function

---
HTH
Jason
Atlanta, GA

-----Original Message-----
I'd like to be able to define my own functions in
Excel... for example: SPAN
could calculate the span of values in a selection
(highest minus lowest) with
a single function rather than using MAX(C#R#:C#R#)-MIN
(C#R#:C#R#) all the
time.
.

Hi Matt,

Try putting this into a non-class module:

Function SPAN(rng As Range) As Double
With WorksheetFunction
SPAN = .Max(rng) - .Min(rng)
End With
End Function


Regards,
KL

"Matt" wrote in message
...
I'd like to be able to define my own functions in Excel... for example:
SPAN
could calculate the span of values in a selection (highest minus lowest)
with
a single function rather than using MAX(C#R#:C#R#)-MIN(C#R#:C#R#) all the
time.

Be forewarned that if you implement that strategy for simple things like the
example you showed, your spreadsheets will be significantly slower to
calculate. Using your example on a range of 100 cells, the worksheet function
took 16 usecs, the VBA 24 usecs. That's a 50% difference.

To me, that speed penalty isn't worth it. IMO, you should use VBA only when
you can't accomplish the task with reasonable (i.e. understandable and
modifiable) worksheet formulas.

On Tue, 15 Mar 2005 08:55:02 -0800, "Matt"
wrote:

I'd like to be able to define my own functions in Excel... for example: SPAN
could calculate the span of values in a selection (highest minus lowest) with
a single function rather than using MAX(C#R#:C#R#)-MIN(C#R#:C#R#) all the
time.

It is so wonderful being retired and not having to wonder how to save 8
microseconds!
Just think if the program did this 1000 times, it would take 1/60th of a
minute longer to run.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Myrna Larson" wrote in message
...
Be forewarned that if you implement that strategy for simple things like
the
example you showed, your spreadsheets will be significantly slower to
calculate. Using your example on a range of 100 cells, the worksheet
function
took 16 usecs, the VBA 24 usecs. That's a 50% difference.

To me, that speed penalty isn't worth it. IMO, you should use VBA only
when
you can't accomplish the task with reasonable (i.e. understandable and
modifiable) worksheet formulas.

On Tue, 15 Mar 2005 08:55:02 -0800, "Matt"

wrote:

I'd like to be able to define my own functions in Excel... for example:
SPAN
could calculate the span of values in a selection (highest minus lowest)
with
a single function rather than using MAX(C#R#:C#R#)-MIN(C#R#:C#R#) all the
time.