VBA way to access of the cell being calculated?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcus Schöneborn - 19 Sep 2007 13:49 GMT

Basically, what I want to do is to prefill a cell with an automatically
calculated default value, but I want to do the calculation only until it
succeeds.

Idea:

- A1 is a text field
- B1 is some value that's requested based on A1 via HTTP

However, I do not want the HTTP request to be done again when A1 is
changed again, or when all cells get recalculated. So basically, I want
a B1 to "collapse" to a string value without formula as soon as A1 once
was value.

So, the "primitive" I need is:

B1: =RetryUntilNonempty(SomeFunction(A1))

As soon as SomeFunction returns anything other than "", B1 will collapse
to the return value and no longer contain a formula.

Internally (but that's the part of which I know how it works),
SomeFunction will check A1 for validity, if invalid, return "", and as
soon as it is valid, issue the HTTP request and return some nonempty
string.

How to do that?

Reply to this Message

Tom Ogilvy - 19 Sep 2007 14:02 GMT

You would need to use the Worksheet_Calculate event to fire a macro to
convert B1 to a string value.

Right click on the sheet tab and select view code

select Worksheet from the left dropdown of the resulting module and
Calculate from the right dropdown

Private Sub Worksheet_Calculate()

End Sub

now put in the code that checks the conditions and sets conditions to get
the functionality you want.

Signature

Regards,
Tom Ogilvy

> Basically, what I want to do is to prefill a cell with an automatically
> calculated default value, but I want to do the calculation only until it
[quoted text clipped - 23 lines]
>
> How to do that?

Reply to this Message

Marcus Schöneborn - 19 Sep 2007 14:10 GMT

> You would need to use the Worksheet_Calculate event to fire a macro to
> convert B1 to a string value.
[quoted text clipped - 10 lines]
> now put in the code that checks the conditions and sets conditions to get
> the functionality you want.

That's quite tedious, as I want to do this not for one cell, but default
an entire column that way... but I guess I don't have a choice.

If there is nothing better, it will probably be best to hook
Worksheet_Change and check if the change applies to the "source"
column. But is it really not possible to change the cell currently in
calculation and "de-formula" it?

Reply to this Message

Tom Ogilvy - 19 Sep 2007 14:30 GMT

No. Calculate does not pass an argument that indicates which cell triggered
the event.

Change does not fire on a change caused by a calculate.

If you can't pick it up by examining the values in the cells, it would be
difficult to determine.

All that said, the calculate is being stimulated by some action. You can
pick up a cell being manually or programmatically changed with the Change
event. You can pick up a DDE change with the Change event. If you can
translate that to the specific action you need to take, then it should work.

Signature

Regards,
Tom Ogilvy

> > You would need to use the Worksheet_Calculate event to fire a macro to
> > convert B1 to a string value.
[quoted text clipped - 18 lines]
> column. But is it really not possible to change the cell currently in
> calculation and "de-formula" it?

Reply to this Message