Public Function to run faster

[hajiali]

Hello did not know if there was any way to make the Function below faster.

Public Function CC(CellRange As Range, TCL As Range)

Dim TargetCL As Long, CT As Long, CL As Range
Dim Ans As String

Application.Volatile
TargetCL = Evaluate("cfcolour(" & TCL.Address & ")")
Set CL = Range("DV_DAT_FTO")
For Each CL In CellRange
If Evaluate("Cfcolour(" & CL.Address & ")") <> TargetCL And CL.Value = TCL.Value Then CT = CT + 1
Next CL
CC = CT
Sheets("EMPLOYEE LIST").Calculate
End Function

 

[offthelip]

You are looping through a range of cells which is bound to be slow, so the obvious way is to get rid of the the loop through the cells. However I can't see how to do that since I don't know what Cfcolour is. Is this another function you have define and if so what does it do??

 

[hajiali]

the function counts the number of times a date occurs in a range with the same font color. it's similar to an excel function CountIfs but also checks the font color.

 

[offthelip]

I am not sure whether you are aware but one of the main reasons that VBA can be slow is because it takes a long time for VBA to access the worksheet when compared to accessing variables in memory. So looping through a range of cells is always slow when compared to loading the values of a range of cells into a variant array and then looping through the variant array. The latter is often more than a 1000 times faster. Unfortunately in order to detect the color of a range of cells you have access each cell individually so it will be slow. When designing a workbook it is usually advisable to avoid using color as an item of data, it is much faster if you are using VBA to use a “helper” column which holds the data value and then use conditional formatting to present the color to the user. Then VBa can access the “helper” column in one go by loading the range into memory to access the data rather than checking each cell. I don’t know whether you workbook is suitable for this.

Looking at the actual code you have presented it is possible that you might have a double loop through the cells.

Does the CFColour function also have a loop through cells?

Do you use this function in many places?

Do you use this function in places where Cellrange is just one column or one row. If this is case then you could speed it up slightly by loading the values of cell range in to a variant array and thus remove one access the worksheet from you loop by converting:

CL.Value = TCL.Value to use a variant array

Another possibility to save a little time is to recode your function to avoid using the evaluate function . In fact I would suggest recode it to avoid using the cfcolour function entirely because evaluate and calling another function is only going to make it slower.
Also I don't think you need this statement at all:

Set CL = Range("DV_DAT_FTO")

 

[RoryA]

Another possibility to save a little time is to recode your function to avoid using the evaluate function . In fact I would suggest recode it to avoid using the cfcolour function entirely because evaluate and calling another function is only going to make it slower.

I suspect from the name that the function is used to get colours caused by conditional formatting, which requires the use of evaluate (you can't directly use DisplayFormat in a function called from a cell).

If speed is a factor, I'd suggest rethinking the entire workbook setup. Using colours as data is not a good idea in excel at the best of times and combining volatile conditional formatting with a volatile udf is even worse.

 

[hajiali]

Thanks RoryA and Offthelip for the tips. Unfortunately the function is required as each color code of dates represent something. I did figure out that even having CC function on the sheet slow down due to it continuously calculating which I found a work around by converting the results to value and getting rid of it once its done with the if Test in the below of the worksheet tab macro.

  Range("FHLD_FTOD").FormulaR1C1 = "=CC(Elist[[D1]:[F10]],R[" & Target.Row - 4 & "]C[" & Target.Column - 90 & "])"
        Sheets("EMPLOYEE LIST").Calculate
        Range("FHLD_FTOD") = Range("FHLD_FTOD").Value
            If (Cells(DATFTODate.Row, DECLD.Column) = "FHL" And FHLCells Is Nothing And CNT -  Range("FHLD_FTOD") > Range("DF_PERDAY")) Or Then

its just slow when it refers to UDF and was hoping that there was an Application.EnableEvents = False or Application.ScreenUpdating = False equivalent to use on UDF.

 

[RoryA]

No, there isn't. I'd still recommend you redesign your workbook.