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.

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

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


.

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.

--
Regards,
Tom Ogilvy

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

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?


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

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


.

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

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


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

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


.

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

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


-----Original Message-----
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.

--
Regards,
Tom Ogilvy

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


.



.