VBA makes Excel slow? Or conditional formatting?

Question

Creating a pseudo Gantt project plan in Excel. For those employees without MS Project, I ran into the following problem. The Excel sheet becomes very sluggish. I am not sure if it is caused by the VBA function I used or the conditional formatting.

The table starts with the task details task, duration, taskStart, taskEnd.

and continues with the calendar part including the fields weekStart and weekEnd. In each calendar cell is a formular

=PseudoGantt(task;duration;taskStart;taskEnd;weekStart;weekEnd)

The macro looks like the following:

Function PseudoGantt(taskName As String, taskLength As Integer, taskStart As Date, taskEnd As Date, weekStart As Date, weekEnd As Date) As String
    PseudoGantt = Switch( _
        taskName = "", "empty", _
        taskStart = taskEnd And taskStart >= weekStart And taskEnd <= weekEnd, "milestone", _
        taskLength = 0 And taskStart >= weekStart And taskStart < weekEnd, "summarytask", _
        taskLength = 0 And taskEnd <= weekEnd And taskEnd > weekStart, "summarytask", _
        taskLength = 0 And taskStart < weekStart And taskEnd > weekEnd, "summarytask", _
        taskStart >= weekStart And taskStart < weekEnd, "start", _
        taskEnd <= weekEnd And taskEnd > weekStart, "end", _
        taskStart < weekStart And taskEnd > weekEnd, "continue", _
        True, "empty" _
    )
End Function

This will fill the cell with a Gantt state the week is in, which I use as condition for the conditional formatting to color the cell appropriately.

This works, but as I said, it makes Excel very slow.

I thought about replacing the conditional formatting within the VBA custom function, but they do only return values and cannot change the cell color or the like.

Question:

is it the custom function or the conditional formatting what makes Excel slow?
How can I color the cells from VBA?
Should I rather leave the calendar cells empty and let VBA do the coloring without the workaround to write and content into the cells?
If your answer to 3. is "yes". How to do that?

Thanks for listening.

Side note: sorry, can't post the screenshot yet (Anti-Spam-Filter)

You stopped ScreenUpdating already? Application.ScreenUpdating = False and if you do not have to rely on recalculation after every update set EnableCalculation = False — rene
Have a try and delete all your custom formating without altering the rest of your code and see if it works better. If it does, think about using a vba to change the cell formating instead of the CF. — JMax
Conditional formatting is very efficient so the culprit is likely to be your UDF - how many times are you using it? (JMax suggestion is very logical) — brettdj

Robert Ilbrink Robert Ilbrink · Accepted Answer · 2012-01-03T16:29:45

I once created a dynamic Gantt with a dynamic chart, You take the start date and calculate the duration, then make a dynamic chart. Here are the instructions

VBA makes Excel slow? Or conditional formatting?

1 Answers