Counting colors and updating formula

  • I've been looking for a macro which can count specific colors in a range, making a calculation with this sum and putting the output in a specific cell. So far I've found this:



    But this function slows down my macro's a lot (and seems to slow even more over time). I thought that the Application.Volatile was slowing it down but removing it didn't solve it.


    Does anyone have a good idea for a workaround that forces a recalculation but does not slow down the progress ?
    For instance: Count all vbRed in Range C3:L44 and output in P2
    And count all Interior.ColorIndex = 33 in Range C3:L44 and output in P5


    I've also used this macro: http://www.ozgrid.com/VBA/Sum.htm but this also seems to slow down alot ?


    Thanks in advance !


    Maarten

  • Re: Counting colors and updating formula


    Assuming you have this formula in P2 and P5 (I have used P1 here but you would use whatever cells aref your reference cells for desired colors to be counted)


    =CountCellsByColor(C3:L44, P1)


    If you add

    Code
    Application.Calculation = xlCalculationManual


    at the start of your other macros, and

    Code
    Application.Calculation = xlCalculationAutomatic


    at the end of the other macros, then it should not slow those other macros down.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Counting colors and updating formula


    Thanks for your answer !
    I've added the statements to all my macros and it indeed fastens the whole procedure alot (from 30sec to 7sec).
    I still find 7 sec to be alot because i'm not running that much macros and skipping the 'colorsearch' reduces the runtime to less than a second. Is that macro so memory-intensive ?


    Still, I'll take the 7 seconds :)


    Thank you,



    Maarten

  • Re: Counting colors and updating formula


    Your welcome.


    It is not the UDF code that slows things down, rather the fact that a UDF is being used. It needs to calculate for 410 cell interior colors and a UDF is much slower than an Excel built-in worksheet Function.


    If only Excel had a COUNTIF Function that worked on cell color as well as cell value or text!!!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Counting colors and updating formula


    Note that if you are using a procedure that calls other procedure(s), something like

    Code
    Sub MySub()
        ' mycode
        Call MyOtherSub
        'maybe more of my code
        Call AnotherSub
        'maybe more of my code
    End Sub


    Then you should turn off Screen Updating, Enabling Events and Automatic Calculation, and turn them on again, in that first Sub and NOT in each of the Subs that get called.


    That will stop each getting turned off and on again each time another sub is called, that will speed things up more.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Counting colors and updating formula


    Well that explains alot. Even more because I am looking for 4 different colors in two worksheets ...
    I didn't know about the slowness of UDF, I'm used to just working with subs :)


    I had been looking for the COUNTIF-version too ! :)

  • Re: Counting colors and updating formula


    Well that tip shaved off another 5 seconds !
    Actually all of my subs are being called through an overarching sub, so that works out fine for me now ;)


    Thanks alot !
    :yourock:

  • Re: Counting colors and updating formula


    You're welcome.


    Quote

    I had been looking for the COUNTIF-version too !


    Don't hold your breath on that!!! :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Counting colors and updating formula


    To expand on what KjBox has already said.


    I have a module contained on a stand-alone add-in workbook that I can either reference (for users that have the same drive access), or just pickup and drop into the project I'm working on (for users that don't have access to the same drives).


    It contains a number of different functions that I use all the time (GetLastPopulatedRow, GetLastPopulatedColumn, GetUserID etc), but it also has two public subroutines just called ProcessStart and ProcessStop.


    The process start basically switches automatic calculations to manual, sets EnableEvents, ScreenUpdating and DisplayWarnings to False.


    The process end switches all these things back (I do have a little bit more going on depending on whether I want to force certain application settings or I return the settings to the users application settings as they were pre-processing.


    Saves me the hassle of having to do this every time though.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!