Help with using custum functions

[Guest]

Hi every1!!!

I know that if you create a custum function you can return avalue and take
in some parameters as well. So far i have no problems with this.

My problem is could i return more then one value, and not to the same cell,
that this function is being called from.

Example:
This function i need is used on X number or rows. (1000 so far)
Maybe all the rows will have data and maybe not,its dynamic.

So,lets say we figure this out on the data entered on line 1.

This function is called from column D1, and it takes in parameters A1 & B1.

It does a few calculations, and returns the result to calling cell D1,let
say it returns 34.

so it looks like this now:

A1 B1 C1 D1
30 4 -- 34
Could i also return another value from that function to cell C1 for example
something like 17.

so it looks like this:
A1 B1 C1 D1
30 4 17 34

and so one for the next rows that have values, the same thing would be done
as well.(dynamiclly, as needed)

If this is possible how would you do this from VBa, and if its not, is there
a way to do-it anyways.

Please let me know, i just cant figure-out how to copy data into another
cell from a dynamic custum function...

Any help or suggestion will be very much appreciated.
thx again,
PAtrick

 

[Tom Ogilvy]

In general, the function can only return a value to the cell in which it is
locations. It can not change anything about another cell. It can not
change formatting for any cell. The exception (and it really isn't an
exception) is a multi-cell array formula. You enter the formula in multiple
**contiguous** cells simultaneously and the function returns an array as a
result. The elements of the array are returned, one to each cell. So in
your specific example, you can update C1 and D1 if you array enter you
formula in C11 and it returns an array

Public Function myFunc(rng1 as Range, rng2 as Range)
myFunc = array(rng1(1)*rng2(1),rng1(1)/rng2(1))
End Function

Selection C11, in the formula bar enter
=MYFUNC(A1,B1)

Entered with Ctrl+Shift+Enter

returns values to both C1 and D1.

 

[dgp]

I'm working on something similar.

You'll need to create a user-defined array function. Your function
should return an array containing the values you want to write to C1
and D1.

To enter the function into the spreadsheet, select both C1 and D1, type
in the function, and instead of hitting Enter use Ctrl+Shift+Enter.

Search this group for "User Defined Array Function" for more info.
Search Excel's Help for general info on array functions.

Good Luck,
Dave