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

 

[MarkHenri]

I put 123 in cell A1 and put the focus on A3. The following took about 1 second to run on my XL2002/WinXP 1.6ghz machine--
<pre>Sub TestSetFormula()
Dim s
s = "=a1"
For x = 1 To 2000
ActiveCell.Formula = s
Next x
End Sub

</pre>
I didn't even turn off auto calculation.

Describe your system? Can you send me the spreadsheet to test here?

 

[juliodepalma]

Hello Mark,

Thank you for your answer.

I tried your code on my system (PIII @ 600 Mhz., 256MB, win 98 SE) and it tooks nearly 2,85 s. to complete.

Your example clearly shows that my problem is not Excel writing formulas too slow. In fact, after modifying your code to use ranges in a way similar to my faulting subroutine the execution time is good enough.

By now I think that the problem has to do with the complexity of the formula, so here it goes my testbed subroutine which executes in 45 s. on my system.

Sub TestSetFormula2()
Dim t As Single
Dim s As String
Dim r As Range, rng As Range

Set rng = Worksheets(1).Range("b1:b10")
s = Worksheets(2).Range("e13").Formula
' s holds the formula:
' =INDEX(__DATOS_2002_,MATCH(CONCATENATE($A$2,"-",E$4,"-",$A13),DATOS_ID_ExpMesPers,0),MATCH("Cost_ExpMesPers",DATOS_CAMPOS,0))
' where "__DATOS_2002_" points to "='I:Control de proyectos[2002. Análisis.xls]datos'!$1:$65536"
' and $A$2 = "P-0002", E$4 = "7" and $A13 = "AS"
' So its concatenation gives as result the string "P-0002-7-AS"
' which is used as the key to match values in the column labeled "DATOS_ID_ExpMesPers"
' "DATOS_ID_ExpMesPers" points to "='I:Control de proyectos[2002. Análisis.xls]datos'!$AK:$AK"
' and "DATOS_CAMPOS" points to "='I:Control de proyectos[2002. Análisis.xls]datos'!$1:$1"

t = Timer
For Each r In rng
r.Formula = s
Next
MsgBox "Elapsed: " & Timer - t & " s."
End Sub

It seems that after writing the formula in the cell, it has to evaluate it in order to show its value.

What I'm doing wrong?
Do you have a solution?
Is there an alternative way of doing it?

I was wondering if substituting the formula by a wrapper function that simply evals a string, and passing my formula to the function as a parameter will work, but if the formula has named ranges, it returns "#¡VALUE!".

Function MyEval(strExpression As String) As Variant
Application.Volatile
MyEval = Evaluate(strExpression)
End Function

Hope you can help me :wink:

Thanks again and best wishes,

-- Julio Garcia

 

[Andrew Poulsom]

It's slow because you are referencing an external workbook.

I haven't tried it but I think you may be able to speed things up by using:

rng.Formula = s

instead of using a For Next loop.

 

[Juan Pablo González]

Julio, I think Andrew is right. In your post you mentioned that you were changing part of the formula if a condition was met, wich would then force the For Next loop, but, in your code, you're ALWAYS changing that formula...

So, you could use what he suggested (rng.Formula = s)

 

[Andrew Poulsom]

Just to expand a bit.

My example assumed that you had a contiguous range. But your first post mentioned that the formulas were changed only if a certain condition was met - hence you were doing it cell by cell.

You can use the Union method to gather up all the cells and change the formula for the whole range like this:

Sub Test()
    Dim Rng As Range
    Dim c As Range
    Dim WorkRng As Range
    Set Rng = Range("A1:A5")
    For Each c In Rng
'       *** Replace the next line with your test on the cell ***
        If c.Value <> 0 Then
            If WorkRng Is Nothing Then
                Set WorkRng = c
            Else
                Set WorkRng = Union(WorkRng, c)
            End If
        End If
    Next c
'   *** Replace the BlahBlah with your formula and remove the '
'   WorkRng.Formula = BlahBlah
End Sub

 

[juliodepalma]

Hi (and thanks to) all,

For the sake of simplicity in my testbed subroutine I used the same formula in every cell of the range, but in the real macro every cell has a different formula.

This is the real one (well, I've deleted some portions of code not related to the problem):

Sub neoMiActualizaReferenciasT()
Dim rngOrigen As Range, celda As Range
Dim strBusca, strCambia, strResultado As String
Dim valorAnterior As String, valorNuevo As String
Dim nCambios As Integer

Set rngOrigen = Application.Intersect(ActiveCell.Parent.UsedRange, ActiveCell.EntireColumn)

' In the first cell of the range, "Worksheet_SelectionChange" leave the old value of the cell as a comment.
' The event "Worksheet_Change" triggers the execution of the subroutine.
valorAnterior = CStr(rngOrigen.Cells(1).Comment.Text)
valorNuevo = CStr(rngOrigen.Cells(1).Value)
strBusca = "_" & valorAnterior & "_"
strCambia = "_" & valorNuevo & "_"

For Each celda In rngOrigen
With celda
If .HasFormula Then
strResultado = .FormulaLocal
nCambios = neoMiBuscaCambiaAlt2(strResultado, CStr(strBusca), CStr(strCambia))
If nCambios > 0 Then
.FormulaLocal = strResultado
End If
End If
End With
Next
End Sub

This is the signature of the function "neoMiBuscaCambiaAlt2", which do a search and replace over a string, and returns the number of substitutions made.

Function neoMiBuscaCambiaAlt2(ByRef strFormula As String, ByRef strBusca As String, ByRef strCambia As String) As Integer

If the problem is related to using external book references (and I really think it can):

Can I disable refresh value, calculations, evaluation of formulas, or whatever for these cells?

Any better idea?

Best regards,

-- Julio Garcia

 

[Andrew Poulsom]

Julio,

In your posted code you have:

strResultado = .FormulaLocal

Then after calling your function and testing its value you have:

.FormulaLocal = strResultado

What is the purpose of making the formula what it already is?

 

[Juan Pablo González]

Andrew, his function neoMiBuscaCambiaAlt2 is passing ByRef that string, making some changes to it, that's why I believe he's "reassigning" it, in case the number of changes is greater than 0.

 

[Andrew Poulsom]

OOPS!