UDfs to react similarly to built-in functions pertaining to spreadsheet usability

I'm trying to figure out a template for my functions so that they may be handled (stretched and expanded on spreadsheet) the same as built-in functions such as "sin()" for "Enter" and "Ctrl+Shift+Enter" to handle array outputs to pre-highlighted cells.

I've only ever created functions where it outputted only to that specific cell that it was called in. Such as when I have a column of x values A1 through A5. I put f(x) in column B and calculate f(A1), f(A2) ... f(A5). Works no problem. But, if I name array spam = [A1 A2 A3 A4 A5] and enter f(spam) in column B, it does not work. Instead I get error of type #VALUE. This works for built in Excel functions like sin, cos, log, etc but I would like to know how to get it to work in my user defined function.

I borrowed the following from another post on this forum... http://www.excelforum.com/excel-prog...-function.html

It's close to what I want to achieve, but...
1) I'd rather not have to pass 2 variables, 1 for the length of the input array and 1 for the range of the input array.
2) The function operation can't be expanded/stretched down its column to calculate the output of its respective input column for "Enter"

Please Login or Register  to view this content.

Also, one thing I don't understand in Excel is how "sin(A1:A9) + Enter" is only able to carry out the operation within same rows as the input, 1 to 9 in this case. Otherwise, #VALUE will be returned if sin(A1:A9) + Enter is carried out beyond row 9.

Thanks a lot for your time.

Oh I figured out to use list.Rows.Count to get 'N' the number of elements in the input range, so that eliminates my 2 problems.

However!
"=Fjunk2(spam)" still won't work the same as "sin(spam)"

=Fjunk2(spam) will only output the calculated answer for the first element from the input column in all the rows 1-9.
=sin(spam) will output the calculated answer for its respective element from the input column for the rows 1-9.
*spam = A1:A9

I think if I get past this hurdle I'll have a basic template for functions to handle simple algebraic calculations that can be applied to the spreadsheet the same as the built-in functions.

Please Login or Register  to view this content.

I figured out that I have to return a range instead of an array.
However, I keep getting the circular reference notification and am not getting the results that I want.
Is the problem stemming from how I am initializing the range object x?
I think "set x = Selection" is causing the problem.
Therefore, right now I'm trying to figure out other ways to "set x".
I'd appreciate any comments.

Thank you.

Please Login or Register  to view this content.

I also tried the following, but it runs into the circular reference notification as well.

Please Login or Register  to view this content.

Thanks for your replies SuitedAces!

I see that I was misunderstanding the use of range variable by treating it as an array that I could load.
If functions can't change cell values, and functions can't call subs that change cell values... are there any ways to change cell values in a manner that can be called from a cell in a worksheet?

I tried your code and was only able to make it work in the following fashion. Does arr = rng work? Nice use of Lbound and Ubound. I didn't know it could be used to get the dimensions of a multidimensional array.

It's just that, rng(A1:A9) + Enter <> sin(A1:A9) + Enter, in terms of which element in A1:A9 that function processes.
Although, rng(A1:A9) + CSE == sin(A1:A9) + CSE, in that both process and spit out arrays properly.

I mean, it's not a big deal. Normally one wouldn't use a function by putting inputting an array without CSE.
I just feel like my function would be more complete if it handled exactly as a built-in function.

Please Login or Register  to view this content.

It's been a while, sorry I haven't responded sooner. I took time off to focus on my studies.
Thanks a lot SuitedAces for all your input and time. However, it wasn't quite what I was looking for.
Since I finally got back to working on this, I got help through other means and was finally able to solve this problem.
The following is coded to mimic the response of built-in functions such as sin().
If anyone knows of a simpler way to do this, perhaps by utilizing the nature of excel better. Please let me know.
Thank you.

Please Login or Register  to view this content.