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

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

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

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