Input to a user-defined function in exel 1

[Llarian1981]

I am writing a user defined function in exel. I need the user to input the range of cells which contain the data the function will use. If I run my function in exel, I give the input range by writing something like A1:B5. The function interprets this input as the data the cells in the range contain, not as the range itself (instead of A1:B5 the argument contains {1,2,3,...}. I don't want to enter the range as "A1:B5" (then the function works!), because I would like to have the possibility to enter the range by selecting it in the worksheet, as you can do within pre-defined exel functions. How can I change the way the function interprets the input?

Thanks for your help, Llarian1981

 

[AnotherHiggins]

Why not have the user select the desired range first, then activate the the UDF, which will use "Selection." ?

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Llarian1981,

My crystal ball is a tad cloudy this morning and I can't see the function to which you are referring.

A range is a range. I would expect that you function argument would be...

Function MyFunction(rng As Range).....

You treat the argument as you would any range object, process Value property of each element in the collection.

As with any spreadsheet function, the range can be entered with no keystrokes by using the mouse pointer -- point 'n' drag.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Llarian1981]

@ Anotherhiggins
This works, but it's not a good solution, because it would need a kind of popup telling the user that he has to select the range first.

@SkipVought
I tried to set my argument as range, but that doesn't
change the input I get!

 

[SkipVought]

It CAN work -- believe me!

Please post your code.

John,

That ONLY works for a SINGLE occurrence of a function on a sheet. what happens when a selection is made that is NOT related to the function?

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Llarian1981]

@SkipVought

You are right, it does work. My fault, thanks a lot!

 

[SkipVought]

You might need to take a look at...

Application.Volatile

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}