Hi

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.

Many thanks

pootle flump ur codings are working excelent.

Morning,

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

Thanks Colin

I'm afraid I can't attach an example - there is a lot of propriety IP in there and it is too complicated to abstract.

The problem was that the data was sorted, but incorrectly. There were no errors and all formulas returned the correct data.

However - it might be that this was due to an error on my part. I am currently testing different scenarios. I'll let you know

pootle flump ur codings are working excelent.

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!

Hmmm.
May I ask - do you know if hidden rows in the range being sorted will interfere with the sort or should they make no difference?

pootle flump ur codings are working excelent.

Yes, I can simulate something which may be what you are seeing.

A1:A5 contain the values 1;2;3;4;5.

If I sort descending then I get 5;4;3;2;1. That's okay.


However, if I hide a row - say row 2 - and then sort descending, the hidden row is excluded from the sort. I get 5;2;4;3;1 and the 2nd row remains hidden.


I see this in both XL 2002 and XL 2003.

This behaviour is confirmed on this MS Help & Support article:
XL: Sorting Feature Ignores Hidden Rows


I don't have XL 2007 at work so I can't check it. The behaviour could be different in XL 2007 because sorting was overhauled (a new sort object was introduced).

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.

Thanks again for you help

pootle flump ur codings are working excelent.