Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined formula, selecting range
Hello,
Can someone tell me how to make a user defined formula in Excel that ables one to select a (flexibel) range. The formula has to be something similar to the standard VBA statement: Activecell.Select Selection.End(xlDown).Select Thanx a lot for your help!!!! regards, Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined formula, selecting range
Hi
Selecting a range on worksheet does change worksheets state physically - what is not allowed for functions in Excel by definition. Forget it, or use a procedure instead. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) wrote in message ups.com... Hello, Can someone tell me how to make a user defined formula in Excel that ables one to select a (flexibel) range. The formula has to be something similar to the standard VBA statement: Activecell.Select Selection.End(xlDown).Select Thanx a lot for your help!!!! regards, Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined formula, selecting range
Hi Robert,
User-defined functions don't quite work like that I'm afraid. UDF's are simply functions like any other XL function (e.g. AVERAGE) but you can write them yourself to meet your exact requirements. So just like the AVERAGE function, UDF's expect input parameters and are only recalculated when those input parameters change (well there are a couple of exceptions to that rule but probably not relevant to your situation). Perhaps you could describe exactly what it is that you are trying to accomplish? Regards, Peter Beach wrote in message ups.com... Hello, Can someone tell me how to make a user defined formula in Excel that ables one to select a (flexibel) range. The formula has to be something similar to the standard VBA statement: Activecell.Select Selection.End(xlDown).Select Thanx a lot for your help!!!! regards, Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined formula, selecting range
By passing the range to the function as a UDF, you can be flexible. For
instance, say your UDF is Function myUDF(rng as Range) Dim cell As Range For Each cell in rng myUDF=myUDF + cell.Value Next cell End Function You can call it with one cell =myUDF(A1) many cells =myUDF(A1:A10) a multiple row/column range =myUDF(A1:H10) What you cannot do is use the format that you can with some built-in functions, such as =myUDF(A1,B10,C9) although it could be changed to cater for that as well. -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message ups.com... Hello, Can someone tell me how to make a user defined formula in Excel that ables one to select a (flexibel) range. The formula has to be something similar to the standard VBA statement: Activecell.Select Selection.End(xlDown).Select Thanx a lot for your help!!!! regards, Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined range for graph | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
external range in VBA (user defined formula) | Excel Programming | |||
external range in VBA (user defined formula) | Excel Programming |