VBA way to access of the cell being calculated?
 

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?

VBA way to access of the cell being calculated?
 

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.

--
Regards,
Tom Ogilvy



"Marcus Schöneborn" wrote:

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?

VBA way to access of the cell being calculated?
 

»Tom Ogilvy« wrote:
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.

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?

VBA way to access of the cell being calculated?
 

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.

--
Regards,
Tom Ogilvy



"Marcus Schöneborn" wrote:

»Tom Ogilvy« wrote:
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.

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?