Excel user defined functions.

[Guest]

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.

 

[Jason Morin]

Try:

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

 

[KL]

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

 

[Myrna Larson]

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.

 

[Bernard Liengme]

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.