Formula adds time to Calculation

[foxguy]

I'm just getting started trying to track this down and looking for advise on places to look.

I have a workbook that has been in use for several years with no significant problems.
On Sheet1 I discovered that the Worksheet_SelectionChange() runs faster if I Set Calculation=Manual then set it back to Automatic when it's done.

I now want to add a new feature.
On Sheet2 I want to add over 1000 cells with formulas like this:
=SumIf(...)+SumIf(...)+SumIf(...)
The results of the 1000 cells will be used on Sheet1.
There are no dependent cells on the added cells yet.

When I added the formulas, the time for Sheet1 SelectionChange() increased over 4 seconds (which is WAY too unacceptable).

I have already tracked it down to the Calculation=Automatic statement in Sheet1 SelectionChange(). If I take out the Calculation=Manual then there is 18 seconds added because it changes 4 cells and triggers Calculation 4 times.

I have removed all the added cells except 1 with just 1 SumIf(...) in it and the time is just barely on the edge of acceptable.

I don't understand why the new SumIf(...) would be so slow.

There are already over 2000 cells with SumIf(...) in them on Sheet2 that sum the same cells that the new SumIf(...) is summing and I have not seen any slowdown before now.

Obviously there is something different about this particular SumIf(...).
The only difference is which cells this SumIf(...) is using for the other parameters.

Any ideas on what to look for and where to look?

[Winon]

Hi Foxguy,

This is an interesting one. Besides "Bump" the only thing I can assume is that it is a conditional calculation to be done with the SumIf(...) which could square the amount of calculations - let alone if it is presented in multiple instances.Translating it into a massive calculation process.

It is just a wild geuss, but please do let me know how you finally got it sorted out.

[foxguy]

Winon;

I hadn't thought of conditional formulas. I haven't checked all the precedents to my formula yet, but there are no conditional formulas in the 1 layer of precedents.
I will post the solution if I ever find it.

[Whizbang]

Please upload a sample workbook. Without seeing your data structure, formulas and VBA, it is hard to make any sort of suggestion.

[foxguy]

I can't upload the entire file. It doesn't belong to me.
I tried duplicating the problem in a sample workbook, but haven't been able to duplicate the problem.

[Whizbang]

Do you mind pasting the SelectChange code? Just the fact that it doesn't automatically set calculation to manual before running tells me it is not optimized.

[foxguy]

WhizBang;

That may be new to me. What would set the Calculation to Manual if I don't put it in myself?

There is more to it, but this is the Relative Part:

Please Login or Register  to view this content.

[snb]

This doesn't seem to me to be an obvious way to store some target properties.

1. what is the benefit of storing those properties of every changed cell ?
2. isn't there a condition of which changed cells these properties have to be stored and of which not ?

I would store these properties (if necessary at all) in an array.

Please Login or Register  to view this content.

[foxguy]

I store the Target.Row and Column primarily for conditional formatting. Some columns in the ActiveCell's row are highlighted. The ActiveCell is highlighted a different color in some columns.
I don't think I can use conditional formatting to check values of arrays in the VBA. If you can think of a better way, please let me know

I also have some macros that need to know if the cursor moves Up or Down as opposed to Left or Right (compare SelectionCol to SelectionLastCol).
I also have a macro that automatically looks for a cell that is not empty, but only if the cursor moved down 1 row. If it moves up or more that 1 row down or left or right it doesn't do anything.
The macros could store the properties in an array, but since I want the conditional formatting, I don't see the point.

If I'm doing this the hard way, I'm open to suggestions.

But this doesn't help figure out why my SumIf() adds time.

[Whizbang]

I don't think I can use conditional formatting to check values of arrays in the VBA. If you can think of a better way, please let me know

You can use a UDF to evaluate the array and return some value to be tested in conditional formatting. I can't really go into details atm, 'cause I am at home, but I am sure there is a way to do that.

I'll post a more thorough comment tomorow.

[foxguy]

I don't want to use UDF()s. There are so many cells that would use it that it would slow things down when it needs to recalculate. At least that is what has happened previously in this workbook when I used UDF()s.

[snb]

As long as you don't provide a sample workbook we have no idea what you are doing, why you are doing this and what is your aim.

[romperstomper]

One thing we can say is that using a lot of CF will slow down your workbook.

[Whizbang]

UDF's won't necessarily slow things down. With some knowledge and effort, they can be written to be non-volitile and efficient.

In this case, however, I can understand your hesitation, but the true culprit of your slowness is Conditional Formatting. Conditional formatting calculates every time there is a change to the worksheet, even if the CF formula doesn't reference or is dependant on the changed cell. (or at least I read that this is what happens somewhere or other).

According to this site (do a search for "Using UDFs in Conditional Formats"), UDF's in Conditional Formatting are calculated for every screen refresh.

So, anyway, that is why you experienced slowness when you used UDF's before. But, like I said, there are ways to make your UDF more efficient.

[Whizbang]

This article explained that CF's calculate at cell repaint, whether or not a UDF is used. So as long as your UDF is efficient, it will not behave any different than another function.

[foxguy]

I don't believe CFs would solve my problem. I believe this is off topic.
Someone wanted to see my SelectionChange() event and commented that I could use arrays instead of putting the values into the worksheet which would eliminate the need to turn Calculation off and back on (triggering a Calculation). (S)he implied that SelectionChange() should be turning calculation off automatically. I responded to that, saying that I needed to put the values in the worksheet for my CFs. Then someone responded that I could use UDFs for my CFs.

I will admit that I might be able to go around the problem by using UDF()s in my CFs which would then not require my SelectionChange() to turn off Calculation, but that doesn't tell me why my Calcuation time increased with just one additional formula added.

I didn't have a problem until I added 1 cell with a SumIf(...) (nothing else) and Calculation time increase dramatically. There are no CFs that use the new formula cell.
The SumIf() sums the exact same cells as a 1000 other SumIf()s, so there is nothing about the data that can cause the time increase and there is nothing about my CFs that can cause the time increase.

I haven't tried any further to find my problem, because I was hoping someone would have an idea where to look. Since no one has, I will start working to find the problem. I assume that the formula is somehow triggering another formula to calculate 1000s of times. I believe I need to find that other formula and figure out a way to have it only calculate once.

Unless someone else can think of something else that would cause my Calculation time to increase.

[Whizbang]

Trace the precendents of the cell in question. Follow it back to the beginning. See what the Dependency Tree looks like.

Really, without a sample workbook or sample code, there isn't much help to provide, aside from discussing theory and taking shots in the dark.

[foxguy]

That's what I will do. But there are several formulas and range names and CFs. I have already looked at them and didn't find any obvious volatility.
I didn't want to go to the trouble of posting all the formulas in case there was something I didn't know about SumIf() (which was what I was hoping for, but didn't want to assume what the answer would be (forum rules)).

Now I will try and create a sample workbook that has all the formulas in it. It won't be slow because I'm not going to duplicate 10000+ of pieces of data and 1000+ of formulas but hopefully someone will see something that I'm not seeing (like maybe some sort of indirect circular reference or something obscure like that). It will probably take me 2 or 3 days with my schedule to create the workbook, but I'll start on it.

[shg]

But there are several formulas and range names and CFs. I have already looked at them and didn't find any obvious volatility.

CF is intrinsically volatile.

[foxguy]

I know CFs are volatile which is why I will include them in my sample workbook.
They aren't slowing the workbook down now, but maybe my new formula is somehow causing one to keep calculating 1000+ times. I'm just guessing wildly. But I won't exclude them just in case.