Can i quicken this Function or use diff code to speed it Up

mahmed1 · Oct 9, 2014

Hi

I have this function that is used on several sheets and the The range to lookin is in B2:I200 therefore it loops several of times when this function is called or when a cell changes within that range on that sheet

I double on a cell to change its colour and add a value and then this function is called to sum the colour

This works fine but can be clunky at times

Need help speeding it up or using different method

Here is the working Code:

Code:

Function SumColour(MatchColour As Range, MatchColourRange) As Double
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In MatchColourRange
    If Cell.Interior.Color = MatchColour.Interior.Color Then
        On Error Resume Next
        SumColour = SumColour + Cell.Value
        On Error GoTo 0
    End If
Next Cell
Application.ScreenUpdating = True
End Function

mahmed1 · Oct 9, 2014

Any luck

thank you all

Rick Rothstein · Oct 9, 2014

The only thing I can think of is to get rid of some unnecessary code lines...

Code:

Function SumColour(MatchColour As Range, MatchColourRange As Range) As Double
  Dim Cell As Range
  For Each Cell In MatchColourRange
    If Cell.Interior.Color = MatchColour.Interior.Color Then
      SumColour = SumColour + Cell.Value
    End If
  Next Cell
End Function

mahmed1 · Oct 9, 2014

Hi Rick,

I thought the application.screen updating would speed it up? and the error part was just there encase the code had incurred an error (Ie cell value was incorrect) etc

I also tried to do it via the find method (I have been told this is the way better and quicker than loops)

but i am stuck with the what part as i am searching to match the colour which can not be found

The range to lookin is in B1:I200

This is my attempt

Code:

Function SumColour(MatchColour As Range, MatchColourRange As Range) As Double
Dim Found As Range
Dim FirstCell As String
Application.ScreenUpdating = False
Set Found = MatchColourRange.Find(What:=MatchColour.Interior.Color, _
                             After:=Range("B1"), _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlPrevious)
If Found Is Nothing Then
Else
    FirstCell = Found.Address
    Do
       SumColour = SumColour + Found.Value
       Found = Found.FindNext(Found)
    Loop Until Found.Address <> FirstCell
End If
Application.ScreenUpdating = True
End Function

mahmed1 · Oct 9, 2014

Hi Rick... I tried to use the findformat and set the searchformat to true but i cant seem to figure out how i could reference the MatchColour.Interior.color for the find format

My Attempt

Code:

Function SumColour(MatchColour As Range, MatchColourRange As Range) As Double
Dim Found As Range
Dim FirstCell As String
Application.ScreenUpdating = False
[U][B]'Application.FindFormat<<<MatchColour[/B][/U]
Set Found = MatchColourRange.Find(What:="", _
                             After:=Range("B1"), _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlPrevious, _
                             [U][B]SearchFormat:=True) 'This is set to true[/B][/U]
If Found Is Nothing Then
Else
    FirstCell = Found.Address
    Do
       SumColour = SumColour + Found.Value
       Found = Found.FindNext(Found)
    Loop Until Found IS NOTHING and Found.Address = FirstCell
End If
Application.ScreenUpdating = True
End Function

Rick Rothstein · Oct 9, 2014

Lesceline Gibson said:
I thought the application.screen updating would speed it up?

The only way turning off screen updating off speeds things up is when a lot of data is being written to the workbook... with screen updating off, the computer does not have to show you (on screen) each and every change that it makes one-at-a-time, rather, it makes the changes in memory and then shows you all the changes that were made all at once. With a UDF, only one cell is being changed, so turning screen updating off cannot help.

Lesceline Gibson said:
and the error part was just there encase the code had incurred an error (Ie cell value was incorrect) etc

What error do you think might happen? Do you have non-numbers in the cells that are colored? I assumed not which means no errors will occur which, in turn, means error trapping is not needed. If it were, though, there is no need to execute the On Error Return Next statement with each iteration of the loop... just put it before the loop... it will persist until turned off by the On Error GoTo 0 statement (which should be put after the Next statement).

Lesceline Gibson said:
I also tried to do it via the find method (I have been told this is the way better and quicker than loops)

The Find function will not work in a function used as a UDF inside a worksheet formula (that function would still work, however, if called by normal VB code). I cannot tell you why the Excel programmers decided to make that the case, especially since it used to work fine that way back in XL2003.

RoryA · Oct 9, 2014

Find will work in a UDF (since 2002); FindNext will not. I can't recall if you can use FindFormat though.

However, it would be much better to simply stop using colours as data. (I've lost count of how many times I've said that this week) If you apply the colours based on data, you can use that data in built-in functions like SUMIF without having to use UDFs that are slow and must be volatile - even then they won't recalculate when you change the colour of a cell.

Rick Rothstein · Oct 9, 2014

RoryA said:
Find will work in a UDF (since 2002); FindNext will not. I can't recall if you can use FindFormat though.

You are right, of course, Find work fine for normal searches... it is FindFormat that apparently is blocked. Thanks for the correction.

RoryA said:
However, it would be much better to simply stop using colours as data.

Agreed.

mahmed1 · Oct 9, 2014

Hi Rick and Rory

thank you both for your help

unfortunately the spreadsheet is set up by colour and i needed to add by colour

i thought rather than having a function, have it is a macro that is called by the user (say refresh balance) to avoid it beung volatile and called all the time?

Now that i wont be using the function, can i have the find colour work now without looping ?

would you advise this is the best to do it and i also thought is it a good a idea to call this macro as soon as something changes within the matchlookup range? This way it will only call the macro if i manually call it or if someone inputs an amount in that cell
how can i do this via the find method?
thank you

mahmed1 · Oct 9, 2014

Im trying to do the same thing as the function but replace with a sub so its called but with the find method to make it more quicker

Can i quicken this Function or use diff code to speed it Up

mahmed1

Well-known Member

Excel Facts

mahmed1

Well-known Member

Rick Rothstein

MrExcel MVP

mahmed1

Well-known Member

mahmed1

Well-known Member

Rick Rothstein

MrExcel MVP

RoryA

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

mahmed1

Well-known Member

mahmed1

Well-known Member

Similar threads

Forum statistics

Share this page

Can i quicken this Function or use diff code to speed it Up

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock