I forgot, in Excel 2000 a UDF can add a Name, it's an oddity fixed in later
versions. However the next line that attempts to write to another cell will
error.

Adding the Name indirectly triggers one of the "kludges" I mentioned, namely
it triggers a recalc (in 2000). Whilst you can take advantage of it, eg to
write to another cell, the UDF also recalc's multiple times, a long loop. To
escape that include something like the following in your function -

Static bExit as Boolean

on error goto errH
if bExit then
bExit = false
exit sub
end if
bExit = True

do stuff to trigger recalc,
but don't write to other cells !!!

done:
bExit = False
exit function
errH:
resume done
end function

' do other stuff in the "kludged" calculate event

This'll tidy up things for 2000, now forget it and don't use it! It won't
work in later versions and should never have worked in earlier versions.

If the overall objective is to write a Name, use a worksheet change event.

Regards,
Peter T



"Ed Fulda" wrote in message
...
Hi Peter,
It doesn't work in 2003 or XP, but it does work absolutely fine in 2000 -
if
you have it installed check with the code I posted in reply to Joel

Ed

"Peter T" wrote:

I missed what you said about expecting the UDF to do things like add
Names
and the like. That would not have worked in any version (without some
kludge
type workarounds). A UDF called from the worksheet can only return a
value
or an array of values.

Peter T

"Peter T" <peter_t@discussions wrote in message
...
First port of call is Charles Williams' site, this page for starters -
http://www.decisionmodels.com/calcsecretsj.htm

Whilst there are differences in the way Excel versions handle UDFs,
from
the information you have given I suspect the problems are not related
to
version but something else. I could be wrong but post code with
sufficient
information to allow others to reproduce the errors.

Regards,
Peter T



"Ed Fulda" <Ed wrote in message
...
Hello
I have been asked to look at moving a very large set of pricing sheets
from
Excel 2000 to Excel 2003. In the progress of this I have found that
the
functionality for a UDF in a cell to change/recalculate any other
cells
has
been removed in Excel XP, whereas it was available in Excel 2000. The
amount
of work required to refactor the code is very large, and before
starting
on
it it would be good to find out if there is a workaround. The Sheet
works
in
the following way:
-User enters values, clicks a button to run a Macro,
-Macro calculates all the ranges required to return a price
-In many of the cells it is calculating are Functions which go and
look
at a
large number of cells to calculate the price, sometimes writing to
other
Cells and sometimes having to recalculate other cells
-As it is such a large sheet we can't simply calculate all cells
before
we
tell it to price, as they are not all needed and so aren't calculated
unless
needed for performance reasons.

It would be a lot of work to move the functionality from the Functions
to
the initial macro call, though this would solve the problem. Is there
any
other workaround?

Macro security is set to low.

The Errors I receive are the following:

The UDF will hit a line like Range("DataRange").Calculate and this
will
raise a "Calculate method of Range class Failed" error.
If it hits a line like Range("DataRange").Value = 1 this will raise a
"Application-defined or object-defined error"
If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name",
RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined
or
object-defined error".

Any of these run from a UDF in Excel 2000 works.

I have a sample workbook if this isn't clear