Off the top of my head, there may be some other options you could explore to
address recalculation:

1) Perhaps set up a relative named range. With cell E5 selected, click
Insert/Name/Define, enter a name (let's say Test) and, in the refers to box,
enter

=OFFSET(E5, -1, -1, 1, 1)

which should refer to the cell 1 row above and 1 column to the left of
whatever cell "Test" is used in. For example, you could enter 12 in cell
B46, then enter =Test*2 in cell C47 and it will return 24. Then you could
use parameter passing in your function to pass Test to your function as a
range argument. Recalc seemed to work okay with this setup.

2) You could make the function volatile so it will recalculate whenever any
cells on the worksheet are calculated. I wondered if you could change a cell
that affects your function, but does not cause any recalculations in any
other cells (therefore not causing your function to recalculate), so I set up
a function that referenced an input cell using Caller and made it volatile,
then set up an input cell used by the function, but not used anywhere else on
the sheet, and the function recalculated every time the input cell changed -
so it seemed to work okay.

Application.Volatile True


3) I doubt it is practical, but if nothing else seems to be an option, maybe
you could use a worksheet event handler (such as worksheet_change event) to
force the worksheet to recalculate. You would have to test to see how much
it would slow things down to see if it would be workable.



"embirath" wrote:


Hi everyone

Thanks for all your input!

In my work, I frequently have to cut and paste (and copy and paste) the
cells, and move them around to rearrange their orders etc. I find that
when I use input parameters for the functions, the parameters get all
mixed up, and I have to go in and "fix" them after a move. I know about
the "relative" vs "absolute" references (A7 vs $A$7 etc), but still, the
parameters are not always copied/cut the way they need to be. This is
why I wanted a function that just always looks at, for example,
Offset(-1,2), no matter where the cell is located or moved to.

But, I didn't realize the problem with the cells not being
recalculated... That might be another problem.. Hm.

I'll look into the "Caller" object. I'll see what I can do with it.

Thanks again all! :-)
Emma


--
embirath
------------------------------------------------------------------------
embirath's Profile: http://www.excelforum.com/member.php...o&userid=37202
View this thread: http://www.excelforum.com/showthread...hreadid=573500