I am still testing this but thought I would ask in case someone...all right, Colin...knows the answer off pat.
I have a worksheet. It has lots of tables of nested worksheet functions looking at numerous other worksheets, the results of which populate a couple of charts. I have some code that writes about a dozen or so values to this worksheet and all these functions hang off those. Write a new value to the worksheet, all the values of the tables change and so do the charts.
The very first VBA statement after writing the last of these values to the worksheet is to Sort all the data based on the values in a range. This sometimes fails. I suspect this is because the Sort executes before the tables reflect the changes I have made to the dozen values. As such, the VBA and worksheet functions are asynchronous and there is a lag while the worksheet functions update during which code execution merrily and obliviously continues.
Is this correct? Does it sound plausible?
If so, is there something I can do\ check that all the functions have completed before sorting the data? I could simply pause execution for a set period of time, but this seems rather crude.
When you say the sort fails, do you mean that the items aren't sorted, they are sorted incorrectly, or that the formulas return error values? Are there any UDFs involved and does any of the process take place in Excel object event handlers? An attached example would be great....
Okay. Some range object properties are not available during a sort which might cause an issue if they are called by a UDF - but it doesn't sound like that's the problem here. Looking forward to hearing the results of your testing!
Thank you Colin. I changed my code to unhide all rows and columns before sorting (hence post #5) and it now works fine so it looks like that was the likely candidate!
The reason I was going about things being synchronous was because the problem was intermittent (of course!) and appeared to be related to major changes to these dozen values. Turns out it was related to these, but only because the values I was sorting on would therefore change significantly too.
I hope that makes sense. I can confirm it was failing in 2007 too.