Do I have to delay reading cells after I write to cells that trigger many array formula calculations?

[garsimeon]

I have a fairly complex spreadsheet that has many array formulas and even has formulas the reference external spreadsheets. In VB macros for this spreadsheet, do I have to put a wait into my macro after writing to a cell and before reading a resulting value from all of the resulting calculations from the write? Thanks for any insight.

 

[Artik]

The vast majority of formulas work synchronously, that is, macros can only run after the entire calculation chain has finished recalculating itself.
However, some new functions that use external services can run asynchronously. Then somehow the operation of the macros needs to be delayed so that all the formulas using the services have time to recalculate.

Artik

 

[garsimeon]

The vast majority of formulas work synchronously, that is, macros can only run after the entire calculation chain has finished recalculating itself.
However, some new functions that use external services can run asynchronously. Then somehow the operation of the macros needs to be delayed so that all the formulas using the services have time to recalculate.

Artik

Thanks Artik! Just one follow up question... can you give me a list of the "new functions that use external services can run asynchronously" so I can check my spreadsheet for them?

 

[Artik]

How about another way. Is the question in the first post a theoretical one (before you wrote the code) or a practical one (the code doesn't work properly)? If it's the latter, show the code that doesn't work properly, describe how the processed data looks, and point out the problem.

Artik

 

[garsimeon]

How about another way. Is the question in the first post a theoretical one (before you wrote the code) or a practical one (the code doesn't work properly)? If it's the latter, show the code that doesn't work properly, describe how the processed data looks, and point out the problem.

Artik

It is an existing spreadsheet and an existing macro. I put the delay in the macro provisionally to ensure that all of the formulas are finished updating after writing a value to a cell and before I start reading other cells that are the result of all of the formulas. I can't tell for sure if it fails or not without the delay. I can tell that it mostly does not fail without the delay (through experiments with and without the delay), but I don't think it is possible to know for sure. That is why I am asking if you know a list of the formulas that are asynchronous, so I can check to see if I use any of them.

Here is a significantly over simplified pseudo code for what I am doing...

For valueIter = 1 to 10 Step 1
    Worksheets("MyData").Cells(1,2).Value = valueIter
    myZ = Worksheets("MyData").Cells(2,2).Value
Next valueIter

Here is a significantly over simplified table in the worksheet "MyData"

X	1			Foo1	Foo2	Foo3
Z	=INDEX(F2:F5, MATCH("Item2", D2:D5, 0))		Item1	=fx(X)	=fx(X)	=fx(X)
			Item2	=fx(X)	=fx(X)	=fx(X)
			Item3	=fx(X)	=fx(X)	=fx(X)
			Item4	=fx(X)	=fx(X)	=fx(X)

The problem is that there are 20-30 tables like the Foo/Item table above with up to 10s of thousands of values in them but more importantly the the values in the tables are the result of many chained reaction formulas that are dependent on X but are dependent on several other tables, several other formulas, and other spreadsheets that the value is dependent on. And there are also thousands of "Z"s.

My issue is that I can't tell for sure that all of the "Z"s will always be correct without the delay. I have done experiments with and without the delay and it seems like most of the "Z"s are the same/correct value, but I cannot be sure that all of them updated correctly and I was not sure if every time that I run the macro the timing would be consistent.

So, it would help greatly if you could share with me any formula types that calculate asynchronously so that I can check all of the formulas in my spreadsheet. This would give me confidence that I don't need the delay. Without the delay the macro takes 1 hr to run with the delay it takes 3 hrs to run, so the time savings would be significant.

 

[Artik]

Also important is what is hidden under =fx(X). But I think you are not using asynchronous functions.
Functions that can cause problems related to the VBA macro's failure to wait for a result are primarily functions that retrieve data from the Internet: WEBSERVICE, TRANSLATE, STOCKHISTORY, and Data Types (e.g., Stocks and Geography).
Connections to databases and Power Query can also be a problem.
In summary, if you do not use the above functions, there is no need to delay the macro.

Artik

 

[arthurbr]

Don't know if this helps
. You can write the following at the beginning of your code

Application.Calculation = xlManual

and finish with

Application.Calculation = xlAutomatic

 

[Artik]

I don't think turning off the automatic calculation is helpful. What the OP needs in the loop is to insert a value into a cell, then the formulas depending on that cell will do the calculations, and when they are done, the result is downloaded to a variable. If you turn off the calculation, there is no correct result.

Artik

 

[garsimeon]

Also important is what is hidden under =fx(X). But I think you are not using asynchronous functions.
Functions that can cause problems related to the VBA macro's failure to wait for a result are primarily functions that retrieve data from the Internet: WEBSERVICE, TRANSLATE, STOCKHISTORY, and Data Types (e.g., Stocks and Geography).
Connections to databases and Power Query can also be a problem.
In summary, if you do not use the above functions, there is no need to delay the macro.

Artik

Perfect! Yes. I am not using any of those types of functions.

 

[Artik]

Without the delay the macro takes 1 hr to run with the delay it takes 3 hrs to run.

One hour is like half a life, and three hours is a life imprisonment.
If you make these calculations infrequently and accept such a long waiting time then do nothing more. On the other hand, if you do such calculations often, then you should consider whether all formulas are necessary all the time. Perhaps some of the results of the formulas change infrequently, so you could replace the formulas with fixed values.
The whole thing would certainly require a thorough analysis and thinking about how to build the project anew.

Artik