Excel's Macro Execution is too slow.

[AlexHarley666]

Hello friends...

I have a excel's macro that use any for-next cicles for process information in 1 sheet.

I execute macro through 1 command button in another sheet.

But, if I try again to execute macro, this execution is too slow.

For example: The sales information in 1 year

- Time for Execution Number 1: 1 minute
- Time for Execution Number 2: 5 minutes

I think that problem is many memory busy, but I don't know the way to get free memory.

Please, I need your suggestions!!!

Thanks in advance, ALEX

 

[VBAjedi]

Post your code. That's about the only way we can help you speed it up. . .

VBAjedi

 

[BrainB]

To start with make sure that you have the first line of code:-
Application.Calculation = xlCalculationManual

and reset at the end with with :-
Application.Calculation = xlCalculationAutomatic


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[AlexHarley666]

@BrainB: I try with your suggestion, but still I don't get results. I will try again and I will write to you. Thanks for your suggestion.

@VBAjedi: Please, copy-paste the next code in Notepad for a best understanding. Thanks in advance for your help:


'--------------------------------------------------------
Fila=2
CantidadRealContinentes = 0
Pais = ""
IndiceGrafico = 0
For Recorre = 1 To CantidadContinentes
BloqueGrafico(IndiceGrafico) = ColumnaDetalle

While Sheets("REPOSITORIO&quot.Range("N" & Fila).Value = Recorre
If Sheets(&quot;REPOSITORIO&quot.Range(&quot;F&quot; & Fila).Text <> Pais And _
((Sheets(&quot;REPOSITORIO&quot.Range(ColumnaFecha & Fila).Value >= DateValue(FechaI) And _
Sheets(&quot;REPOSITORIO&quot.Range(ColumnaFecha & Fila).Value <= DateValue(FechaT)) Or _
(Sheets(&quot;REPOSITORIO&quot.Range(ColumnaFecha & Fila).Value >= DateValue(FechaInicio) And _
Sheets(&quot;REPOSITORIO&quot.Range(ColumnaFecha & Fila).Value <= DateValue(FechaTermino))) _
Then
CodPais = Sheets(&quot;REPOSITORIO&quot.Range(&quot;E&quot; & Fila).Text
Pais = Sheets(&quot;REPOSITORIO&quot.Range(&quot;F&quot; & Fila).Text
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FilaPais).FormulaR1C1 = Pais
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FilaPais + 1).FormulaR1C1 = CodPais

'--- 02A - Formatea TOTALES por cada nuevo PAIS y por DEPARTAMENTOS ---
Cadena = &quot;&quot;
For TotalPais = 0 To CantidadDivisiones
If FinBloque(TotalPais + 1) <> 0 Then 'Si el índice de bloque es distinto de cero, realiza sumarización PAIS por DEPARTAMENTO
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FinBloque(TotalPais + 1) + 1).FormulaLocal = &quot;=SUMA(&quot; & Columna(ColumnaDetalle) & FinBloque(TotalPais) + 3 & &quot;:&quot; & Columna(ColumnaDetalle) & FinBloque(TotalPais + 1) & &quot&quot;
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FinBloque(TotalPais + 1) + 1).NumberFormat = &quot;#,##0&quot;
Cadena = Cadena & &quot;+&quot; & Columna(ColumnaDetalle) & FinBloque(TotalPais + 1) + 1
Else 'Sino, realiza la sumarización en la línea correspondiente a totales
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FinBloque(TotalPais) + 3).FormulaLocal = &quot;=&quot; & Cadena
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FinBloque(TotalPais) + 3).NumberFormat = &quot;#,##0&quot;
End If
Next
ColumnaDetalle = ColumnaDetalle + 1 'Avanza al siguiente índice de columna
ExisteContinente = True 'Si existe al menos un país para el continente, EXISTECONTINENTE=TRUE
'--- 02A - Fin Formato TOTALES por cada nuevo PAIS y por DEPARTAMENTOS ---
End If
Fila = Fila + 1
Wend

If ExisteContinente Then 'EXISTECONTINENTE=TRUE, es decir, realiza este bloque de instrucciones si existen países para el continente
CantidadRealContinentes = CantidadRealContinentes + 1
CodContinente = Sheets(&quot;REPOSITORIO&quot.Range(&quot;N&quot; & Fila - 1).Text
Continente = Sheets(&quot;REPOSITORIO&quot.Range(&quot;O&quot; & Fila - 1).Text
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle - 1) & FilaPais - 2).FormulaR1C1 = CodContinente
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle - 1) & FilaPais - 1).FormulaR1C1 = Continente
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & FilaPais).FormulaR1C1 = &quot;TOTAL&quot;

'--- 02B - Formateo de TOTALES y COLORES de cada CONTINENTE por DEPARTAMENTO
For TempDivisiones = 0 To CantidadDivisiones - 1
For TempTotal = FinBloque(TempDivisiones) + 3 To FinBloque(TempDivisiones + 1) + 1 'Formatea TOTALES por CONTINENTES
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & TempTotal).FormulaLocal = &quot;=SUMA(&quot; & Columna(ColumnaInicio) & TempTotal & &quot;:&quot; & Columna(ColumnaDetalle - 1) & TempTotal & &quot&quot;
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & TempTotal).NumberFormat = &quot;#,##0&quot;
Next
Next
'--- 02B - FIN Formateo de TOTALES y COLORES de cada CONTINENTE por DEPARTAMENTO

'--- 02C - Formatea TOTAL de cada CONTINENTE por TOTAL de DIVISIONES
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & TempTotal + 1).FormulaLocal = &quot;=SUMA(&quot; & Columna(ColumnaInicio) & TempTotal + 1 & &quot;:&quot; & Columna(ColumnaDetalle - 1) & TempTotal + 1 & &quot&quot;
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & TempTotal + 1).NumberFormat = &quot;#,##0&quot;
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle + 1) & TempTotal + 1).FormulaLocal = &quot;=SI(TOTAL&quot; & Veces & &quot;=0; 0; &quot; & Columna(ColumnaDetalle) & TempTotal + 1 & &quot;/TOTAL&quot; & Veces & &quot&quot;
Sheets(&quot;CONSOLIDADO-PAISES&quot.Range(Columna(ColumnaDetalle) & TempTotal + 2).FormulaLocal = &quot;=SI(TOTAL&quot; & Veces & &quot;=0; 0; &quot; & Columna(ColumnaDetalle) & TempTotal + 1 & &quot;/TOTAL&quot; & Veces & &quot&quot;
'--- 02C - FIN Formatea TOTAL de cada CONTINENTE por TOTAL de DIVISIONES

DepartamentosColor = True 'Esta asignación asegura que este bloque se utilice solo una vez, al no permitir que se cumpla
'condición DEPARTAMENTOCOLOR=FALSE
ColumnaTotalContinente(CantidadRealContinentes - 1) = ColumnaDetalle 'Recorre - 1) = ColumnaDetalle
ColumnaDetalle = ColumnaDetalle + 2
ColumnaInicio = ColumnaDetalle
ExisteContinente = False
IndiceGrafico = IndiceGrafico + 1
End If
Next

'--------------------------------------------------------



THANKS YOU (in advance)!!!

 

[Zathras]

You can get some speed-up by not expressing

   Sheets("CONSOLIDADO-PAISES").

on each line the way you are.

Instead, structure the code like this:

With Sheets("CONSOLIDADO-PAISES")
  While .Range("N" & Fila).Value = Recorre
    If .Range(&quot;F&quot; & Fila).Text <> Pais And _
                   ((.Range(ColumnaFecha & Fila).Value >= DateValue(FechaI) And _
                   .Range(ColumnaFecha & Fila).Value <= DateValue(FechaT)) Or _
                   (.Range(ColumnaFecha & Fila).Value >= DateValue(FechaInicio) And _
                   .Range(ColumnaFecha & Fila).Value <= DateValue(FechaTermino))) _
                Then
    'etc.
'
'
End With

Another quick increase can be obtained by putting

    .Range(ColumnaFecha & Fila).Value

into a variable and then testing the variable instead of requiring Excel to evaluate the expression every time. This only applies to cases where you are using the exact same expression multiple times.

There may be a way to restructure your code so that you can use With for

 Sheets("CONSOLIDADO-PAISES")

at the point where you are working with that sheet. Of course you can't use &quot;With Sheets...&quot; within &quot;With Sheets...&quot; because Excel wouldn't know which sheet you are referring to inside a nested structure.

Alternatively, you could also declare Worksheet object variables and use the object variables in place of the &quot;Sheet(...&quot; expressions. That would speed things up a bit too.

 

[AlexHarley666]

@Zathras:

I did the change, I put &quot;WITH Sheets...&quot;, but the process still is slow (

I don't know how more to do...