Solved: I added a function and now it's slow and nonfunctional

[belly0fdesir]

I have this PTO calendar that I've been designing for my office, and everything on it was working smoothly, until I added a function that I found to Sum based on font color.

But now it isn't working correctly. I added a couple of buttons for users to click to change the color and then put the function in and called on it in a formula in column Summary!N6:N121. At first I was displeased because my entire workbook seemed to slow down and on top of that, the formula would not calculate unless I hit F9. But now, the buttons that I added to ease the user in changing colors aren't functional either. Can someone look at this to see what I did wrong?

The function that I added is in Module 2.

I have uploaded a copy of this workbook here (http://s59.yousendit.com/d.aspx?id=1AKKGZXVP1AJ71FN4843TZBB88).

If you can help in any way it would be very appreciated. Thank you.

-Belly

[belly0fdesir]

or do you think I should just remove that feature?

[matthewspatrick]

Belly,

The biggest drawback of this kind of function is that it will not recalculate on its own, because recalc only gets triggered when the values of range arguments change, and not formats.

To combat this, you might try adding this line at the beginning of the function:

Application.Volatile

Of course, if your workbook is already calculating slowly, the cure may be worse than the disease, as that will force every instance of the function to recalculate every time the workbook itself does a recalc.

In addition, I see you have numerous instances of the INDIRECT function. INDIRECT is a volatile function and so always recalcs whenever the workbook itself recalcs. Combining a large number of INDIRECT calls with a UDF is basically asking for slow performance.

Patrick

[belly0fdesir]

Alright. The workbook is already complicated enough without the UDF. I'll remove it. Thanks for the advice.