I know that I've read somewhere that there are some things UDF's cannot do.
Where can I find an explanation of those limitations? Using Excel 2000, I'm
trying to change the numerical format of the calling cell. Is this
possible?

UDF's return values. They can not change formatting nor can the effect the
values of cells other than the one that they are in.

Functions called from within code can do anything that they want however if
converted to a UDF then they are bound by the above rules.

A UDF has all of the abilities of any other function in XL like sum or
average. They operate within a single cell and just return a value to that
cell...
--
HTH...

Jim Thomlinson


"George B" wrote:

I know that I've read somewhere that there are some things UDF's cannot do.
Where can I find an explanation of those limitations? Using Excel 2000, I'm
trying to change the numerical format of the calling cell. Is this
possible?

Array functions can return multiple values to a range rather than a single
cell
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim Thomlinson" wrote in message
...
UDF's return values. They can not change formatting nor can the effect the
values of cells other than the one that they are in.

Functions called from within code can do anything that they want however
if
converted to a UDF then they are bound by the above rules.

A UDF has all of the abilities of any other function in XL like sum or
average. They operate within a single cell and just return a value to that
cell...
--
HTH...

Jim Thomlinson


"George B" wrote:

I know that I've read somewhere that there are some things UDF's cannot
do.
Where can I find an explanation of those limitations? Using Excel 2000,
I'm
trying to change the numerical format of the calling cell. Is this
possible?

Show me a function that if I type it into Cell A1 it will return values into
both Cells A1 and Cell B1 when I did not type anything into Cell B1...
Ultimately whether it is an array function or not a formula in one cell can
not return a value into another cell.

Th ereturn value of a function in one cell can vary depending on the
contents of other cells but it can not directly write a value into those
other cells.
--
HTH...

Jim Thomlinson


"Bernard Liengme" wrote:

Array functions can return multiple values to a range rather than a single
cell
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim Thomlinson" wrote in message
...
UDF's return values. They can not change formatting nor can the effect the
values of cells other than the one that they are in.

Functions called from within code can do anything that they want however
if
converted to a UDF then they are bound by the above rules.

A UDF has all of the abilities of any other function in XL like sum or
average. They operate within a single cell and just return a value to that
cell...
--
HTH...

Jim Thomlinson


"George B" wrote:

I know that I've read somewhere that there are some things UDF's cannot
do.
Where can I find an explanation of those limitations? Using Excel 2000,
I'm
trying to change the numerical format of the calling cell. Is this
possible?

Hi Jim,

This might be one of those "two ways of looking at it" type things. (As I
know you know!), when a formula is array entered, only one call is made to
the function (UDF or worksheet function) so the one return of the function
populates the array into however many cells the formula is array-entered
into.

So, the way I see it, both you and Bernard are right. His statement is
correct, as indeed are your comments.

Just to be contrary and FWIW, there are ways of tricking a UDF into
eventually doing more besides merely returning value(s) !

Regards,
Peter T

"Jim Thomlinson" wrote in message
...
Show me a function that if I type it into Cell A1 it will return values
into
both Cells A1 and Cell B1 when I did not type anything into Cell B1...
Ultimately whether it is an array function or not a formula in one cell
can
not return a value into another cell.

Th ereturn value of a function in one cell can vary depending on the
contents of other cells but it can not directly write a value into those
other cells.
--
HTH...

Jim Thomlinson


"Bernard Liengme" wrote:

Array functions can return multiple values to a range rather than a
single
cell
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim Thomlinson" wrote in
message
...
UDF's return values. They can not change formatting nor can the effect
the
values of cells other than the one that they are in.

Functions called from within code can do anything that they want
however
if
converted to a UDF then they are bound by the above rules.

A UDF has all of the abilities of any other function in XL like sum or
average. They operate within a single cell and just return a value to
that
cell...
--
HTH...

Jim Thomlinson


"George B" wrote:

I know that I've read somewhere that there are some things UDF's
cannot
do.
Where can I find an explanation of those limitations? Using Excel
2000,
I'm
trying to change the numerical format of the calling cell. Is this
possible?

Peter,
Just to be contrary and FWIW, there are ways of tricking a UDF into
eventually doing more besides merely returning value(s) !

Care to elaborate ?

NickHK

"Peter T" <peter_t@discussions wrote in message
...
Hi Jim,

This might be one of those "two ways of looking at it" type things. (As I
know you know!), when a formula is array entered, only one call is made to
the function (UDF or worksheet function) so the one return of the function
populates the array into however many cells the formula is array-entered
into.

So, the way I see it, both you and Bernard are right. His statement is
correct, as indeed are your comments.

Just to be contrary and FWIW, there are ways of tricking a UDF into
eventually doing more besides merely returning value(s) !

Regards,
Peter T

"Jim Thomlinson" wrote in
message
...
Show me a function that if I type it into Cell A1 it will return values
into
both Cells A1 and Cell B1 when I did not type anything into Cell B1...
Ultimately whether it is an array function or not a formula in one cell
can
not return a value into another cell.

Th ereturn value of a function in one cell can vary depending on the
contents of other cells but it can not directly write a value into those
other cells.
--
HTH...

Jim Thomlinson


"Bernard Liengme" wrote:

Array functions can return multiple values to a range rather than a
single
cell
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jim Thomlinson" wrote in
message
...
UDF's return values. They can not change formatting nor can the
effect
the
values of cells other than the one that they are in.

Functions called from within code can do anything that they want
however
if
converted to a UDF then they are bound by the above rules.

A UDF has all of the abilities of any other function in XL like sum
or
average. They operate within a single cell and just return a value
to
that
cell...
--
HTH...

Jim Thomlinson


"George B" wrote:

I know that I've read somewhere that there are some things UDF's
cannot
do.
Where can I find an explanation of those limitations? Using Excel
2000,
I'm
trying to change the numerical format of the calling cell. Is this
possible?

On Thu, 17 May 2007 11:11:07 -0400, "George B" wrote:

I know that I've read somewhere that there are some things UDF's cannot do.
Where can I find an explanation of those limitations? Using Excel 2000, I'm
trying to change the numerical format of the calling cell. Is this
possible?


A Function, whether it is native or user defined, can only return a value. By
definition, it is a predefined formula that performs **calculations**.

If you want to change the numberformat property of a cell, you will need to
execute a VBA macro, and this cannot be done within a Function, as to do so
would violate the rule above.

You could, for example, use an event triggered macro.
--ron