why cell.Formula is so slow?

juliodepalma

New Member
Joined
Sep 4, 2002
Messages
6
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

MarkHenri

Board Regular
Joined
Aug 2, 2002
Messages
106
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

New Member
Joined
Sep 4, 2002
Messages
6
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Code:
rng.Formula = s

instead of using a For Next loop.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Code:
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

New Member
Joined
Sep 4, 2002
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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.
 

Forum statistics

Threads
1,144,510
Messages
5,724,778
Members
422,577
Latest member
madsifonvdijf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top