Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In excel how do I protect the format of a cell but let the user c. | Excel Discussion (Misc queries) | |||
user defined function row number | Excel Worksheet Functions | |||
Excel user management | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
how would i change default user name all excel files | Excel Discussion (Misc queries) |