Como optimizar tus fórmulas, hojas y libros Excel

1. Abrir el libro Excel deseado.

2. Pulsar la tecla Alt y mantener pulsada Pulsar la tecla F11. Mostrará el editor de Visual Basic.

3. Ir a: Barra de Menú Insertar Módulo. Mostrará la ventana del módulo donde escribimos la macro.

   

4. Escribir el código de la macro:

   Private Declare Function getFrequency Lib "kernel32" _
   Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
   Private Declare Function getTickCount Lib "kernel32" _
   Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

   Function MicroTimer() As Double
   Dim cyTicks1 As Currency
   Static cyFrequency As Currency

   MicroTimer = 0
   If cyFrequency = 0 Then getFrequency cyFrequency
   getTickCount cyTicks1
   If cyFrequency Then MicroTimer = cyTicks1 cyFrequency

   End Function

     

   Sub Tiempo_Rango()
   DoCalcTimer 1
   End Sub

   Sub Tiempo_Hoja()
   DoCalcTimer 2
   End Sub

   Sub Tiempo_Libro_Recalcular()
   DoCalcTimer 3
   End Sub

   Sub Tiempo_Libro()
   DoCalcTimer 4
   End Sub

     

   Sub DoCalcTimer(jMethod As Long)
   Dim dTime As Double
   Dim dOvhd As Double
   Dim oRng As Range
   Dim oCell As Range
   Dim oArrRange As Range
   Dim sCalcType As String
   Dim lCalcSave As Long
   Dim bIterSave As Boolean

   On Error GoTo Errhandl

   dTime = MicroTimer
   lCalcSave = Application.Calculation
   bIterSave = Application.Iteration

   If Application.Calculation <> xlCalculationManual Then
   Application.Calculation = xlCalculationManual
   End If

   Select Case jMethod
   Case 1

   If Application.Iteration <> False Then
   Application.Iteration = False
   End If

   If Selection.Count > 1000 Then
   Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
   Else
   Set oRng = Selection
   End If

   For Each oCell In oRng

   If oCell.HasArray Then
   If oArrRange Is Nothing Then
   Set oArrRange = oCell.CurrentArray
   End If

   If Intersect(oCell, oArrRange) Is Nothing Then
   Set oArrRange = oCell.CurrentArray
   Set oRng = Union(oRng, oArrRange)
   End If

   End If

   Next oCell

   sCalcType = "Tiempo en calcular " & CStr(oRng.Count) & _
   " Celdas del rango seleccionado: "

   Case 2
   sCalcType = "Tiempo en abrir la hoja activa " & ActiveSheet.Name & ": "

   Case 3
   sCalcType = "Recálculo tiempo en abrir el libro: "

   Case 4
   sCalcType = "Tiempo en abrir el libro: "

   End Select

   dTime = MicroTimer
   Select Case jMethod

   Case 1
   If Val(Application.Version) >= 12 Then
   oRng.CalculateRowMajorOrder
   Else
   oRng.Calculate
   End If

   Case 2
   ActiveSheet.Calculate

   Case 3
   Application.Calculate

   Case 4
   Application.CalculateFull

   End Select

   dTime = MicroTimer - dTime
   On Error GoTo 0
   dTime = Round(dTime, 5)
   MsgBox sCalcType & " " & CStr(dTime) & " Segundos.", _
   vbOKOnly + vbInformation, "Test optimización Excel"

   Finish:

   If Application.Calculation <> lCalcSave Then
   Application.Calculation = lCalcSave
   End If

   If Application.Iteration <> bIterSave Then
   Application.Calculation = bIterSave
   End If

   Exit Sub

     

   Errhandl:

   On Error GoTo 0
   MsgBox "Error en el cálculo de tiempo " & sCalcType, _
   vbOKOnly + vbCritical, "Test optimización Excel"
   GoTo Finish

   End Sub

5. Guardar el libro con la extensión *.xlsm. Libro habilitado para macros.

1. Ir a: Cinta de opciones Pestaña: Archivo.

2. Ir a: Categoría Opciones. Mostrará la ventana de opciones de Excel.

3. Ir a: Categoría Fórmulas Opciones de cálculo Activar la casilla: Manual.

4. Pulsar el botón Aceptar. Cierra la ventana y elimina el cálculo automático.

Identifica el tiempo necesario para calcular todas las fórmulas del libro. Será el tiempo más elevado de todas las macros.

1. Ejecutar la macro "Tiempo_Libro".

2. Verificar como muestra mensaje con el tiempo empleado en analizar todo el libro, en segundos.

Volver a calcular inmediatamente después de un cálculo, mostrará un mejor tiempo.

1. Ejecutar la macro "Tiempo_Libro_Recalcular".

2. Verificar como muestra mensaje con el tiempo empleado en analizar todo el libro, por segunda vez, en segundos.

Identifica el tiempo de carga para calcular la hoja activa del libro.

Debido a que sólo se vuelven a calcular el libro, este modo, la hora de volver a calcular para cada hoja de cálculo. Debe habilitar para determinar cuáles son las hojas de cálculo del problema.

1. Abrir la hoja deseada. Para verificar su tiempo de carga.

2. Ejecutar la macro "Tiempo_Hoja".

3. Verificar como muestra mensaje con el tiempo empleado en analizar la hoja activa, en segundos.

4. Repetir el proceso para el resto de la hoja. Para determinar cuáles son las hojas que producen los problemas.

Identifica el tiempo de carga para calcular el rango seleccionado de la hoja activa.

1. Ir a: la hoja que presenta anomalías.

2. Seleccionar un rango, varias filas o varias columnas. Identificando el área que presenta anomalías, para posteriormente revisar las fórmulas que producen el problema.

3. Ejecutar la macro "Tiempo_Rango".

4. Verificar como muestra mensaje con el tiempo empleado en analizar todo el libro, en segundos.

5. Repetir el proceso para el resto de rangos. Hasta analizar toda la hoja.