why cell.Formula is so slow?

[juliodepalma]

Hi all,

In a VBA macro I'm writting I need to go through every cell in a range (that contains a formula), and substitute some part of the them if certain criteria is met.

I've realised that writting the formula as: "theCell.Formula = strNewFormula" is unbearable slow. In my example nearly 85 s. for processing 81 cells. Incidentally, if instead of setting the formula, I write it as a range comment, it tooks only 0,75 s.

Is there a way to speed this operation?
Any idea?

Thanks in advance

-- Julio Garcia

PS: At the begining of the code I've disabled the automatic calculation (as usual):
ActiveSheet.EnableCalculation = False
Application.Calculation = xlCalculationManual
ActiveWorkbook.UpdateRemoteReferences = False

 

[juliodepalma]

Hi,

Right Pablo, the function may change the value of "strResultado" directly. I tried to pass by reference "celda.Formula" to the function in order to avoid reasigning, but it didn't let me, the compiler raised an error.

I didn't send the excel book to Mark or anyone (as he suggested), because without the referenced books it won't work and they are huge. But if this helps...

Thanks,

-- Julio Garcia