Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Algunos trucos para optimizar macros en Excel

| 7 comentarios

Al trabajar con macros sencillas (generalmente de pocas líneas), su ejecución no es complicada, por lo que puede trabajarse simplemente grabando una macro desde Excel, o creando la macro directamente en Visual Basic, detallando paso a paso lo que hará la macro. Al hacer esto, el código que se genera es bastante detallado y en algunos casos, contiene más líneas de las que realmente son necesarias para hacer que la macro realice aquello que se desea.

Sin embargo, cuando la macro es más complicada, es útil conocer ciertos "trucos" que podrían reducir las líneas en la macro, permitiendo que su ejecución y revisión sea más rápida.

1) Deshabilitar el cálculo automático

Cuando se tienen muchas celdas con fórmulas, cada vez que se realiza un cambio en los valores de una hoja, Excel recalcula los valores  de todas las fórmulas. Para evitar esto, se puede insertar un código, que hace que los cálculos se realicen al final de la ejecución de la macro, acelerando la misma.

Al inicio de la macro, luego del Sub NombreMacro se coloca el siguiente código:   Application.Calculation = xlCalculationManual

Y al final de la macro antes del End Sub, colocamos:

Application.Calculation = xlCalculationAutomatic
Application.Calculate

2) No agregar la selección de la celda como un paso más, de no ser necesario.

Se podría trabajar de la siguiente manera:

Range ("A1").Select
ActiveCell.FormulaR1C1 = "Hola"
Range("A1").Select
Selection.Font.Bold = True

O reducir la cantidad de líneas, trabajando así:

Range("A1").Value = "Hola"
Range("A1").Font Bold = True

3) Utilizar With... End With para no hacer referencia al mismo objeto o comando varias veces.

Esto:

Sheets(Hoja1).Range(“A1″).Font.Bold = True
Sheets(Hoja1).Range(“A1″).Font.Color = RGB(125,125,80)
Sheets(Hoja1).Range(“A1″).Font.Underline= True

Puede ser reemplazado por esto:

With Sheets(Hoja1).Range("A1").Font
.Bold = True
.Color = RGB(125, 125, 80)
.Underline = True
End With

4) No utilizar palabras reservadas: Para esto, lo más conveniente es no utilizar palabras en inglés, así no se corre el riesgo de estar utilizando una palabra reservada.

5) Declarar las variables

Si bien podemos declarar todas las variables como Variant, esto produce una mayor demora al ejecutar la macro. Es preferible declarar la variable según corresponda (si es fecha, usar Date, si es texto, usar String).

6) Comentar de manera adecuada las macros

De esta forma se podrá recordar con claridad que hace cada macro y se podrá contar con los comentarios que facilitarán la utilización de la macro por terceras personas, que podrían no estar tan familiarizadas con su sintaxis.

7) Si se desea que la macro ejecute una gran cantidad de tareas, es preferible crear varias macros más pequeñas y luego mediante una nueva macro, llamarlas para unir todos los procesos. Esto puede ser útil incluso, si en algún momento se desea omitir alguno de los procesos, no es necesario crear una macro enteramente nueva, sino que basta con no llamar a la macro que se desea omitir.

Por ejemplo:

Sub BorrarDatos()
código...
End Sub

Sub InsertarData()
código...
End Sub

Sub DarFormato()
código...
End Sub

Sub ProcesoCompleto()
BorrarDatos
InsertarData
DarFormato
End Sub

En caso se desee un proceso que solo borre datos e inserte data, pero sin dar formato, bastará con omitir la última línea de la macro ProcesoCompleto.

Elaborado por: Verónica Serpa

7 comentarios

  1. Tambien puedes desactivar la actualizacion de la pantalla:
    application.screenupdating = false

  2. quisiera saber como se puede sacar datos exactos sin redondear la cantidades o sifras

  3. Tengo la siguiente macro y es demasiado lenta.

    Sub Control()
    '
    ' Control Macro
    '

    '
    Application.ScreenUpdating = False
    Range("B3").Select
    Selection.Copy
    Sheets("O.T.").Select
    Range("A3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("B3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("C3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("D3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("E3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B8").Select
    Application.WindowState = xlMaximized
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("F3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("G3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("H3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("I3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("J3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("K3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("L3").Select
    Selection.Insert Shift:=xlDown
    Range("B29").Select
    Sheets("Pantalla de Control").Select
    Range("B15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("M3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("N3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("O3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("P3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("Q3").Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll ToRight:=13
    Sheets("Pantalla de Control").Select
    Range("B20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("R3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("S3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("T3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("U3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("V3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("W3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    Range("B26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("X3").Select
    Selection.Insert Shift:=xlDown
    Sheets("Pantalla de Control").Select
    ActiveWindow.SmallScroll Down:=4
    Range("B27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("O.T.").Select
    Range("Y3").Select
    Selection.Insert Shift:=xlDown
    Range("X16").Select
    Sheets("Pantalla de Control").Select
    Range("B27").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("B3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B4").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B5").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B6").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B7").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B8").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B9").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B10").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B11").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B12").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B13").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B14").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B15").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B16").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B17").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B18").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B19").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B20").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B21").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B22").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B23").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B24").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B25").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B26").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B27").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B28").Select
    End Sub

    Saludos y Gracias...

    • Tiene pinta de haber utilizado la grabadora de macros.
      Sigue los consejos, no utilices .Select para luego copiar, da valores directamente a las variables ó a las celdas.
      La última parte de la macro:
      ActiveCell.FormulaR1C1 = “”
      Range(“B3”).Select
      ....
      Range(“B27”).Select
      ActiveCell.FormulaR1C1 = “”
      Range(“B28”).Select
      Lo sustituyese integramente por:
      Range("B3:B27") = VbNullstring
      La última parte que es la selección de la celda B28, no sirve para nada, salvo que quieras que, una vez finalizada la macro, tenga esa celda activa.

  4. Gracias por estos aportes..muchas gracias

  5. crie una macro para iniciar, enviar, ordenar, limpiar, finalizar una macro y esta compuesta por otras submacros, iciamacro, finalmacro, como lo indico a continuacion esto lo genero la grabadora de macros ya le cambie algun codigo para agilizar procedimiento, ero aun asi se sigue tardando unos 20-25 seg en promedio vme podrian ayudar que mas codigo puedo reducir ya hasta trate de anular parte del ordenamiento pero el proceso sigue igual espero su apoyo dejo mi correo gracias

    Sub Compras()
    '
    ' Compras Macro

    ' Prepararacion de la macro Compras
    Call Iniciamacro

    Range("D4:D16").Select
    Selection.Copy
    Sheets("MOV COMPRAS").Select
    Range("A1000000").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

    Range("A6:M1000000").Select
    Range("A1000000").Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("MOV COMPRAS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MOV COMPRAS").Sort.SortFields.Add Key:=Range( _
    "A6:A1000000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

    ' ActiveWorkbook.Worksheets("MOV COMPRAS").Sort.SortFields.Add Key:=Range( _
    ' "B6:B1000000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    ' xlSortNormal

    ' ActiveWorkbook.Worksheets("MOV COMPRAS").Sort.SortFields.Add Key:=Range( _
    ' "C6:C1000000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    ' xlSortNormal

    With ActiveWorkbook.Worksheets("MOV COMPRAS").Sort
    .SetRange Range("A6:M1000000")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    Range("A6").Select

    Sheets("COMPRAS").Select
    Range("D6:D12").ClearContents
    Range("D14:D16").ClearContents

    Range("D6").Select

    ' Finalización de la macro Compras
    Call Finalmacro

    End Sub

    Sub Iniciamacro()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    End Sub

    Sub Finalmacro()

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.CutCopyMode = True
    End Sub

  6. Hola, Verónica,
    encuentro muy buenas las explicaciones... simples para alguien como yo que recién se inicia en VB. Quisiera p/f me ayude con un par de rutinas que necesito establecer:
    La primera es para abrir varias hojas tipo .csv desde una dirección definida, de acuerdo a una relación dada en un libro x. El proceso es iterativo. Luego de ejecutar una macro con los archivos abiertos, debo cerrar todos los archivos tipo .csv sin que guarde las modificaciones hechas.
    La segunda es para descargar archivos tipo .csv de la web ( https://finance.yahoo.com/quote/ADBE/history?period1=1167368400&period2=1543640400&interval=1d&filter=history&frequency=1d ) en donde la parte que cambia de manera iterativa es ADBE. El punto es que una vez que se llama al sitio, debo pulsar el recuadro "Apply", y luego posicionarme sobre "Download Data" esperando por un segundo antes de pulsar, para asegurarme que cargue toda la información solicitada. Finalmente, y antes de empezar a descargar las hojas de acuerdo a una relación el algún archivo de excel, debo ajustar el recuadro "Time Period", con la fecha de inicio "Dec 29, 2006".
    De veras apreciaría su ayuda.

Deja una respuesta

Los campos requeridos estan marcados con *.