<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
| 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
|
| | > 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
| >
| > | > > 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
| > >
| > 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
| > > > >
| > > 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?
| > > > > >>
| > > > > >>
| > > > > >>
| > > > >
| > > > >
| > > > >
| > >
| > >
| >
| >
|
|
