EXCELeINFO

Curso Excel VBA y Macros – Cap. 4 – Dos proyectos usando usando Referencias absolutas y relativas

by · 12 May, 2019

<<CURSO COMPLETO EN YOUTUBE>>

Como vimos en el capítulo anterior, la Grabadora de macros en Excel nos ayudará a generar macros mediante la grabación de acciones en Excel, y esas acciones generarán una macro con su propio código VBA.

Dos proyectos usando la Grabadora de macros

En este capítulo crearemos dos macros donde aplicaremos las referencias absolutas y relativas, aplicadas en dos casos reales:

  • Formatear reporte. Daremos formato tabular, de filas y columnas bien definidas, para dejar el reporte listo para análisis con filtros o tablas dinámicas.
  • Copiar valores de una hoja a otra. En una hoja tendremos un formulario y con la macro lograremos que los valores se copien a la segunda hoja en forma de una tabla, cada que se ejecute la macro, los valore se guardarán en la última fila.

Ver video Capítulo 4 Excel VBA & Macros

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Grabar macro con Referencias absolutas y relativas

La grabadora de macros de manera predeterminada graba las macros con referencias absolutas, es decir, si haces una modificación en la celda A1, la macro siempre modificará la celda A1, ya que así quedará grabada la acción en el código VBA.

En cambio, cuando grabamos macros con Referencias relativas activadas, las acciones se generarán en relación a la celda activa seleccionada. Por ejemplo, si estamos en la celda A1, y nos movemos a la celda A5, la macro grabará el movimiento de 5 celdas hacia abajo. Si corremos la macro grabada estando en la celda B1, nos recorreremos 5 celdas hacia abajo, y entonces llegaremos a la celda B1.

Grabar macro con Referencia absolutas

En la Figura 1 vemos una rango simple. Grabaremos una macro que añada un total al final de la columna C, primero lo haremos con referencias absolutas y veremos el código.

Figura 1. Rango en Excel donde grabaremos una macro.

Seguimos los siguientes pasos:

  • Nos aseguramos de estar en la celda A1.
  • En la pestaña Programador elegimos Grabar macro.
  • Le ponemos de nombre ReferenciaAbsoluta.
  • Aceptar.
  • Elegimos la celda B12 e ingresamos el texto Total.
  • Elegimos la celda C12 e ingresamos la fórmula =SUMA(C2:C11)
  • En la ficha Programador elegimos Detener grabación.
  • En la misma ficha Programador elegimos Macros.
  • Seleccionamos la macro ReferenciaAbsoluta y damos clic en Modificar.

Se abrirá el IDE de Visual Basic para Aplicaciones y veremos el siguiente código.

Sub ReferenciaAbsoluta()
'
' ReferenciaAbsoluta Macro
'

'
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("C12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Range("C13").Select
End Sub

Si notamos, en la macro vemos referencia a las celdas B12, C12 y C13, lo que significa que si corremos ejecutamos la macro siempre se modificarán esas cedas. Referencias absolutas.

Grabar macro con Referencias relativas

Borramos los valores de la fila 12 y seguimos los mismos pasos de la macro anterior, pero activando referencias relativas.

    • Nos aseguramos de estar en la celda A1.
    • En la pestaña Programador damos clic en Usar referencias relativas.
    • En la pestaña Programador elegimos Grabar macro.
    • Le ponemos de nombre ReferenciaRelativa.
    • Aceptar.
    • Elegimos la celda B12 e ingresamos el texto Total.
    • Elegimos la celda C12 e ingresamos la fórmula =SUMA(C2:C11)
    • En la ficha Programador elegimos Detener grabación.
    • En la misma ficha Programador elegimos Macros.
    • Seleccionamos la macro ReferenciaRelativa y damos clic en Modificar.

Veremos ahora el siguiente código:

Sub ReferenciaRelativa()
'
' ReferenciaRelativa Macro
'

'
    ActiveCell.Offset(11, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Total"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Ahora si miramos con atención, en la primera fila de la macro se hace referencia a ActiveCell, que significa celda activa. Luego se usa el método Offset para moverse 11 filas hacia abajo y 1 columna hacia la derecha, El movimiento se hace en relación a la celda seleccionada. Referencia relativa.

Proyecto 1 – Formatear reporte

Tenemos el siguiente reporte que es generado en una página Web y cuando se pasa a Excel, no es viable trabajar con él. Haremos algunas modificaciones para dejarlo listo para su análisis.

Figura 2. Modificaremos el reporte con una macro para su posterior análisis en forma de tabla.

Grabaremos una macro para transformar los datos en forma de tabla.

  • Elegimos la celda A1.
  • Recomiendo hacer una copia de la hoja Reporte.
  • Ficha Programador > Grabar macro.
  • Nombre de la macro FormatearReporte. Aceptar.
  • Eliminamos la filas 1 a la 6.
  • Eliminamos la fila 2.
  • Con la tecla Control presionada elegimos las columnas, A, B, E, F, I, J, K, O.
  • Clic derecho y elegimos Eliminar columnas.
  • Elegimos todas las celdas de la hoja y vamos a la ficha Inicio > Formato > Autoajustar ancho de columna.
  • Ficha Programador > Detener grabación.
  • Ficha Programador > Macros.
  • Elegimos la macro FormatearReporte y damos clic en Modifcar.

Se abre el IDE de VBA y veremos la siguiente macro generada por la Grabadora de macro. Si ejecutamos la macro sobre la hoja Reporte original, veremos que se aplican los mismos pasos.

Sub FormatearReporte()
'
' FormatearReporte Macro
'

'
    Rows("1:6").Select
    Selection.Delete Shift:=xlUp
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A:B,E:F,I:K,O:O").Select
    Range("O1").Activate
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub

Proyecto 2 – Copiar datos de una hoja a otra

Para esta macro, tenemos una hoja con un formulario sencillo de 3 campos. Lograremos pasar los datos de la primera hoja a la segunda hoja en formato tabular, es decir, cada que ejecutemos la macro los valores se escribirán en la siguiente fila vacía.

Figura 3. Grabamos una macro para pasar los datos de una hoja a otra en forma de base de datos.

Grabaremos una macro de manera normal, y luego, sin pasar la macro, activar las referencias relativas.

  • Primero nos aseguramos de tener datos en la fila 2 de la hoja Base.
  • Ficha Programador > Grabar macro.
  • Nombre de la macro AltaDeRegistros. Aceptar.
  • Elegimos la celda C2 de la hoja Captura. Copiamos.
  • Elegimos la celda A1 de la hoja Base.
  • Ficha Programador > Usar referencias relativas.
  • Estando en la hoja Base, celda A1, presionamos Control + Flecha abajo para ir a la última celda en uso.
  • Ahora la tecla de dirección hacia abajo para movernos a la siguiente fila.
  • Pegar valores.
  • Elegimos la celda C4 de la hoja captura. Copiamos.
  • En la hoja Base nos movemos una columna hacia la derecha. Pegamos valores.
  • Elegimos la celda C6 de la hoja Captura. Copiamos.
  • En la hoja Base nos movemos una columna hacia la derecha. Pegamos valores.
  • Ficha Programador Detener grabación.
  • Ficha Programador > Macros.
  • Elegimos la macro AltaDeRegistros y damos clic en Modifcar.

Se abre el IDE de VBA y veremos la siguiente macro generada por la Grabadora de macro. Si ejecutamos la macro sobre la hoja Captura, veremos cómo se van guardando los valores en la siguientes filas de la hoja Base.

Sub AltaDeRegistros()
'
' AltaDeRegistros Macro
'

'
    Range("C2").Select
    Selection.Copy
    Sheets("Base").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Captura").Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Base").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Captura").Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Base").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Captura").Select
    ActiveCell.Offset(-5, -2).Range("A1").Select
    Application.CutCopyMode = False
End Sub

Descarga el archivo de ejemplo

004 – Dos proyectos con la Grabadora de macros – EXCELeINFO.zip

<<CURSO COMPLETO EN YOUTUBE>>

Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.

Tags:

You may also like...