UDF expanding beyond its cell

[Charles]

Hello

I am looking for a relatively safe and stable way for a user defined
function to return some data outside of the cell that called it.
Basically I have in cell A1 a function "=MyFunc()" and I would like it
to return in the cells:
A1: "Row 1 txt"
A2: "Row 2 txt"
A3: "Row 3 txt"

the simple way to do that is to create a user defined function that
returns an array and to use the array formula {=MyFunc()} in range
A1:A3. But is there a way for a function that only sits in cell A1 to
return values in range A2 and A3?

One of the things I am a bit concerned is if I start putting in the
code for MyFunc some access to range A2 and A3, I am going to mess
with the calculation order of excel, i.e. excel didn't expect the
cells A2 and A3 to be affected by the calculation of the cell A1
(since they are originally empty cells). Would that be a source of
unstability?

If you've already tried to doing that, I would be interested in your
feedback

Thanks
Charles

 

[Joel]

I would only have a UDF return one value. If you need it to return more than
one value put the function in all 3 cells. Add a parameter to the UDF which
is a number 1 to 3. In the first cell put in the number 1 to return the 1st
value, in the 2nd cell put 2 to return the second value, in the 3rd cell put
the number 3 to return the 3rd value.

 

[Ron Rosenfeld]

Hello

I am looking for a relatively safe and stable way for a user defined
function to return some data outside of the cell that called it.
Basically I have in cell A1 a function "=MyFunc()" and I would like it
to return in the cells:
A1: "Row 1 txt"
A2: "Row 2 txt"
A3: "Row 3 txt"

the simple way to do that is to create a user defined function that
returns an array and to use the array formula {=MyFunc()} in range
A1:A3. But is there a way for a function that only sits in cell A1 to
return values in range A2 and A3?

NO (see below)

One of the things I am a bit concerned is if I start putting in the
code for MyFunc some access to range A2 and A3, I am going to mess
with the calculation order of excel, i.e. excel didn't expect the
cells A2 and A3 to be affected by the calculation of the cell A1
(since they are originally empty cells). Would that be a source of
unstability?

Since you can't do that with a function, it won't be a problem.

If you've already tried to doing that, I would be interested in your
feedback

Thanks
Charles

A function, whether User Defined or native, can only return a value. It cannot
change the contents of another cell, nor can it even change the properties of
the cell in which it sits.

You can use a macro (Sub) routine which can affect other cells. Or you can
return an array, as you have indicated.

Sometimes, though, it's "messy" to use an array formula, so, especially if
there aren't a lot of recalculations, I will use the INDEX worksheet function
to return different values in my UDF generated array.

e.g.
A1: =INDEX(Myfunc(),columns($A:A))

or some variation, depending on the shape of the array you are returning, and
filling right would return sequential elements in the array.
--ron

 

[Peter T]

A UDF can only return a value to the formula from which it was called. It
can't change the interface in any way (at least not directly) so it couldn't
write values to some other cells.

An alternative that might suit your needs would be for a change event to
write your values. But what's wrong with your own idea of array entering
your UDF and getting your UDF to return an array.

Regards,
Peter T

 

[Charles]

Thanks all for your answers. It looks like the solution I was looking
for does not exist. The reason why I was trying to find something
slightly better than the array formula is because the number of rows
returned by the function can be variable, and it is also easier to
just input a formula in a cell than an array formula in a dozen ones.
But that's only for general convenience, no hard requirement.

I'll stick with the array solution.

Thanks very much!
Charles

 

[Ron Rosenfeld]

Thanks all for your answers. It looks like the solution I was looking
for does not exist. The reason why I was trying to find something
slightly better than the array formula is because the number of rows
returned by the function can be variable, and it is also easier to
just input a formula in a cell than an array formula in a dozen ones.
But that's only for general convenience, no hard requirement.

I'll stick with the array solution.

Thanks very much!
Charles

Charles,

If you use my INDEX function suggestion, you can handle that problem.

You could nest that function within an IF statement (or use conditional
formatting), so as not to display the #REF error when the area you fill in with
the function is larger than the size of the array.
--ron

 

[Charles]

If you use my INDEX function suggestion, you can handle that problem.

You could nest that function within an IF statement (or use conditional
formatting), so as not to display the #REF error when the area you fill in with
the function is larger than the size of the array.
--ron

Thanks ron, I'll try that. But what I was looking for originally is
just a way to write rapidly a function in one cell that would fill up
whatever number of cells it requires. I have seen some functions doing
that, for instance with the Bloomberg UDF, but i have no idea of how
they achive that. To access historical data, you just create a
function with the ticker and date references as arguments in the first
cell, and when you press enter, it creates a whole column below with
all the historical data.

Thanks for your help
Charles

 

[Ron Rosenfeld]

Thanks ron, I'll try that. But what I was looking for originally is
just a way to write rapidly a function in one cell that would fill up
whatever number of cells it requires. I have seen some functions doing
that, for instance with the Bloomberg UDF, but i have no idea of how
they achive that. To access historical data, you just create a
function with the ticker and date references as arguments in the first
cell, and when you press enter, it creates a whole column below with
all the historical data.

Thanks for your help
Charles

You would have to write a macro (Sub) routine to do that, can't be done with a
function.
--ron

 

[zorgath]

Mr. Rosenfeld,
Could you please expand on this? I need to return an array from a UDF, but
the number of rows and columns returned vary. You solutions seems to be the
right way. However, I just do not understand it. Thank you for your help.

 

[Joel]

What you can do cal the function from every cell of your array where the data
is going to be returned. Suppose your array starts at C5 and Range is
C6:E8.Pass Two parameters to the array: the start location of the arry and
the cell the array is called from D7. You can also pass the size of the
array if required.

=myfunction($C$5,D7, other paramters)


Function myfunction(StartCell as Range, CalledCell as Range)

RowNumber = CalledCell.Row - StartCell.Row
ColNumber = CalledCell.Column - StartCell.Column

'your code
end function