piggyback cell from formula function

  • Thread starter Thread starter Conceptor
  • Start date Start date
C

Conceptor

Hi,
Situation: In every one of a workbook's cells, there is
a formula using a VBA function I created. When a formula
is being recalculated, my function is being called.
Problem: When mass cells recalculation occurs, my
function needs to know from which cell it is being called.
Is there a way for my function to know this?

Thanx
C.
 
Application.Caller will return a range reference to the calling cell (or
cells if it is a multicell array formula).
 
Thanks, worked like a charm.

C.
-----Original Message-----
Application.Caller will return a range reference to the calling cell (or
cells if it is a multicell array formula).

--
Regards,
Tom Ogilvy




.
Click to expand...
 
Well, almost like a charm.

When you use the cell.text property, it works like a
charm, but not in all cases (formatting). When I use the
proper cell.value property, I get error messages
concerning circular references and that Excel cannot
recalculate nor display these circular references. This
error appears only when an automatic recalculation due to
XLA linkage triggers before the workbook_open event.

Any idea what this circular reference message means?
C.
 
I have never had any problem with circular reference errors unless my UDF
was actually doing a circular reference. I doubt it has anything to do
with application.Caller.

If you are trying to build some kind of running total where a cell adds a
value to its current value, that isn't allowed in regular formulas or in
UDF's - unless you enable iteration.
 
Hi again,

Your solution, thought inspired, does not apply to my
case, sadly. This whole problem originated from my
inability to cancel an automatic recalculation, triggered
on workook load, caused when a XLA file is linked to the
XLS workbook. If there could be a way to do so, I would
not be trying to put back the value of the cell into
itself, thus the circular reference. Complicated story.
I guess what I fondamentally need is a way to cancel the
XLA automatic recalculation or to get a Cell_Recalculation
event that would allow me to cancel it. Is there some
object I could reference withevents that would have such
an event? Or is there a way to shut down XLA automatic
load recalculation?
 
There is minimal control over calculation.

You can look at Charles Williams site for information on calculation

http://www.decisionmodels.com

Possibly you need to replace the cells with their values when you close the
book/XLA and put the formulas back at the appropriate time after you open
the workbook.
 
Great idea.

I will try it out. Thanks!
C.

-----Original Message-----
There is minimal control over calculation.

You can look at Charles Williams site for information on calculation

http://www.decisionmodels.com

Possibly you need to replace the cells with their values when you close the
book/XLA and put the formulas back at the appropriate time after you open
the workbook.

--
Regards,
Tom Ogilvy




.
Click to expand...
 
Back
Top