Why is this code slow?

For some reason my cursor takes more time to move from cell to cell than I think it should. It is a noticeable time delay.
I have narrowed it down to the following code.
It runs from every Worksheet_SelectionChange().

Please Login or Register  to view this content.

I only notice the slowdown on one worksheet. I'm guessing that it has something to do with my Conditional Formatting. I have lots of cells that change their cell color when the cursor is on their row.

Is there a faster way to accomplish this?

What is supposed to happen?

Have you tried toggling Calculation ?

Conditional Formatting is (super) Volatile.

Please Login or Register  to view this content.

It's not clear how this routine is being invoked and whether or not the above is already taking place c/o the "parent" routine(s).

Originally Posted by foxguy

It just stores the current location in 2 cells, so that conditional formatting can change formatting based on where the cursor is.

If you're using the SelectionChange Event why the need for Conditional Formatting rules based on active cell ?

Would it not be better to use the VBA event to format as required and thus remove the inherent Volatility ?

Perhaps post a sample.

Originally Posted by foxguy

I don't like putting things in the VBA that Excel can do. I read somewhere that VBA is slower than Excel.

In Excel everything depends upon context.
I'd also say that if efficiency is of paramount concern then avoiding Volatility should be pretty high up in your list of "things to avoid in your model".

UDFs are generally speaking slower than native functions, however, a single UDF might be preferable to a hideously complex Array.

Equally, and more pertinent here perhaps, VBA is often preferable to volatile alternatives.

In the most basic of terms consider 1000 cells that utilise the current date c/o a link to A1 in which we have: =TODAY()
(we assume that this date must be updated on an automated basis)

Each of those 1000 dependants is Volatile by virtue of the Volatile precedent.

If VBA were used to insert today's date as a Constant in A1 then though this may be slower to action than =TODAY() it removes all Volatility.
By removing the Volatility those 1000 dependants now calculate only when necessary.
As a result the calc overhead in the model is reduced and long term performance is improved (from an end users perspective [calc delay etc...]).

Again though I'd reiterate the earlier point that it's context, context, context. If those 1000 dependent formulae are lightweight the impact of the Volatility would be negligible - were they Arrays or some such it would be an altogether different matter.

Originally Posted by fox_guy

Currently one of my Conditional Formatting formulas is "=Row()=Selection_Row". If I were to instead put a formula in some hidden column Z1 : =Row()=Selection_Row, then my Conditional Formatting formula would be "=$Z1".
My thinking is that it takes Excel longer to calculate "=Row()=Selection_Row" than it takes to calculate "=$Z1", and since the longer calculation time is now inside a cell instead of a Conditional Format it won't get calculated so much. Especially since I have as many as 5 cells in the same row that change formatting based on Selection_Row.

Avoiding repetitive calcs is always a good idea, however, again it's all context. The impact of avoiding the repetition unsurprisingly will come down to the nature of the repetition itself. If the repetition is a lightweight calc then removing it is unlikely to make a significant impact (but is good practice if nothing else)

Remember also that Conditional Formatting formulae are processed as though they are an Array and as such are likely to perform a little slower than you might otherwise expect
(this issue is not significant here, however, worth being aware of for future ref. I would say)

I guess I'd sum up by maintaining the point that improving the efficiency of your CF calcs might help but I suspect the real issue is the number of the CF calcs taking place with each selection.

If you can post a sample we might be able to help on specifics (assuming you do not resolve to your satisfaction in the meantime) - else I'm afraid it's a case of generic chit chat from me... I'm generally prejudiced against all things Volatile so am not perhaps best placed to offer advice

I tripled the row count and increased column count to 12 and I have to say that I didn't really notice anything adverse performance wise- ie no noticeable lag.

Are any of your CF formulae more complex than those in your sample ?

Are the underlying values themselves generated by formulae and if so - what are they ?

If the underlying values are formula driven what is the performance impact of your Event on a full blown values & formats only replica of this sheet ?

Basically I am just trying to ascertain that is indeed the CF logic that is the underlying problem and not the formulae used to generate the underlying values in the first instance.
If they themselves are Volatile then that will also have an impact - and depending upon their complexity - that impact could be significant.

In VBA terms (replacing the CF) - it would seem to me that you realistically you need only format two cells at any given time - ie reset prior and set current. In each case the logic used will seemingly need to vary based on column. Does that sound correct to you ?