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?

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

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?

Hi Nick,

One way is to store all the data calculated by the UDF into public arrays,
including possibly Application.Caller.Address('s) and .Caller.Parent.Name

After the recalc is done the Calculate event will fire. In the event process
the data, eg colour format changes to cells or shapes, when done clean up
the stored data.

There are also ways to trigger a sheet-change event from a UDF, eg
ClearFormats (only need to do once during a recalc, only if say 'work to do
counter later in the event' = 1

I did qualify with 'trick' and 'eventually' !

FWIW, I have a method to change say 1000 unique colours in shapes by
changing two or three cells. These are linked to formulas to increment HSL
values then convert to RGB's, in turn linked to UDF arguments. The same
routine also adds appropriate named shapes if they don't already exist. All
done via the UDF in multiple cells. Of course similar could be done without
UDF's in a change event. But that means defining the cells to look at in the
event code.

I've also read about some other non-event methods, I haven't used them and
forgotten what they are.

Regards,
Peter T

"NickHK" wrote in message
...
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?

<I've also read about some other non-event methods, I haven't used them and forgotten what they are.

One I remember (and just tested to be sure) is that you can change the comments of a cell from within a function.
I know there are a few more but I can't remember those.

I do think these should be considered bugs so they might be gone in a future release (the one I tested is still present in 2007)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Peter T" <peter_t@discussions wrote in message ...
| Hi Nick,
|
| One way is to store all the data calculated by the UDF into public arrays,
| including possibly Application.Caller.Address('s) and .Caller.Parent.Name
|
| After the recalc is done the Calculate event will fire. In the event process
| the data, eg colour format changes to cells or shapes, when done clean up
| the stored data.
|
| There are also ways to trigger a sheet-change event from a UDF, eg
| ClearFormats (only need to do once during a recalc, only if say 'work to do
| counter later in the event' = 1
|
| I did qualify with 'trick' and 'eventually' !
|
| FWIW, I have a method to change say 1000 unique colours in shapes by
| changing two or three cells. These are linked to formulas to increment HSL
| values then convert to RGB's, in turn linked to UDF arguments. The same
| routine also adds appropriate named shapes if they don't already exist. All
| done via the UDF in multiple cells. Of course similar could be done without
| UDF's in a change event. But that means defining the cells to look at in the
| event code.
|
| I've also read about some other non-event methods, I haven't used them and
| forgotten what they are.
|
| Regards,
| Peter T
|
| "NickHK" wrote in message
| ...
| 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?
|
|
|
|
|
|
|
|
|
|
|
|

"Niek Otten" wrote in message
I've also read about some other non-event methods,
I haven't used them and forgotten what they are.

One I remember (and just tested to be sure) is that you can change
the comments of a cell from within a function.

Oh yes, that was one.

I know there are a few more but I can't remember those.

Vaguely recall reading some way an xll type UDF can change the interface
too, not sure.

I do think these should be considered bugs so they might be
gone in a future release (the one I tested is still present in 2007)

Well, there's good bacteria and there's bad bacteria !

Another FWIW, normally format changes don't trigger a calculation so UDF's
can't respond until the next full calc. But again there are ways, eg define
a named formula using the xl4Macro Get.Cell function with format type
number, add (Now() * 0) to the result to help it update when used in the
UDF.

Regards,
Peter T

John Walkenbach has a list (partial?) at:
http://j-walk.com/ss/excel/odd/odd06.htm

That shows some of those things that UDFs can do.

I'm not sure I'd rely on those oddities being supported in any future
version--but heck, there are non-oddities that disappear, too.

Niek Otten wrote:

<I've also read about some other non-event methods, I haven't used them and forgotten what they are.

One I remember (and just tested to be sure) is that you can change the comments of a cell from within a function.
I know there are a few more but I can't remember those.

I do think these should be considered bugs so they might be gone in a future release (the one I tested is still present in 2007)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Peter T" <peter_t@discussions wrote in message ...
| Hi Nick,
|
| One way is to store all the data calculated by the UDF into public arrays,
| including possibly Application.Caller.Address('s) and .Caller.Parent.Name
|
| After the recalc is done the Calculate event will fire. In the event process
| the data, eg colour format changes to cells or shapes, when done clean up
| the stored data.
|
| There are also ways to trigger a sheet-change event from a UDF, eg
| ClearFormats (only need to do once during a recalc, only if say 'work to do
| counter later in the event' = 1
|
| I did qualify with 'trick' and 'eventually' !
|
| FWIW, I have a method to change say 1000 unique colours in shapes by
| changing two or three cells. These are linked to formulas to increment HSL
| values then convert to RGB's, in turn linked to UDF arguments. The same
| routine also adds appropriate named shapes if they don't already exist. All
| done via the UDF in multiple cells. Of course similar could be done without
| UDF's in a change event. But that means defining the cells to look at in the
| event code.
|
| I've also read about some other non-event methods, I haven't used them and
| forgotten what they are.
|
| Regards,
| Peter T
|
| "NickHK" wrote in message
| ...
| 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?
|
|
|
|
|
|
|
|
|
|
|
|

--

Dave Peterson