• Author / Uploaded
• Luis Sanchez Bazan

Citation preview

MACROS EXCEL MACRO: Una macro es un conjunto de instrucciones escritas en lenguaje Visual Basic (*), que nos permite automatizar ciertas tareas que la aplicación no contempla.

Por ejemplo, si necesitamos que se inserten automáticamente 2 hojas nuevas al abrir un libro, recurriremos a una macro. Otros casos: si se debe completar alguna tarea cada vez que ingresamos cierto valor en una celda o rango de celdas, si se debe realizar un cierto control antes de imprimir, guardar o cerrar el libro, son todas tareas que pueden ser automatizadas mediante rutinas o macros. (*) El lenguaje utilizado en Excel es VBA (Visual Basic for Applications)

Cómo se crea una macro: para automatizar una tarea mediante una macro básicamente se necesitan los siguientes elementos: 1- un espacio de trabajo donde escribir las instrucciones o rutinas que harán esas tareas: el Editor de Macros. A este espacio se accede desde menú Herramientas, Macros, Editor o con el atajo de teclado Alt+F11. En versión 2007, activar la opción Programador de la Cinta de Opciones. Una vez en ese 'espacio', escribiremos o copiaremos las rutinas en: a- alguno de los objetos (hoja o libro) que seleccionemos con doble clic en el panel de Objetos que se encuentra a la izquierda del Editor. b- insertando módulos c- o insertando formularios personales o Userforms. 2- una acción que hará que la tarea programada se ejecute. A esto llamamos 'Eventos' que inician una macro y pueden ser: abrir o cerrar un libro, entrar o salir de una hoja, cambios o selección de celdas, antes de imprimir o guardar, el 'clic' en un botón de comando, al presionar un atajo de teclado, y otros más. 3- un lenguaje de programación. En Excel utilizamos VBA (Visual Basic para Aplicaciones) 4- Ocasionalmente un formulario donde trabajar para luego volcar los resultados en las hojas: llamados Userforms.

Dónde colocar o escribir la macro: esto dependerá de lo que deba ejecutar nuestra rutina. Si las instrucciones se deben ejecutar, por ejemplo, al abrir o cerrar un libro, se colocan en el objeto ThisWorkbook (o EsteLibro según la versión).

Si las instrucciones se deben ejecutar al entrar o salir de una hoja o al seleccionar o modificar una celda, se colocan en el objeto Hoja. Estos objetos se encuentran en el margen izquierdo del Editor, en la ventana Proyecto-VBAProyect Si nuestra rutina será llamada con un botón, atajo de teclado o desde otra rutina, se colocará en un módulo (estando en el Editor, menú Insertar, Módulo). Las instrucciones para los controles de un formulario o Userforms se colocan en la sección Código del mismo formulario.

Cómo ejecutar una macro: En el punto anterior se menciona que se necesita una 'acción' que dispare o ejecute una macro grabada. Veamos los distintos casos: 1- Una acción automática resultado de un evento, ya sea a nivel libro, hoja, rango o celda En este grupo se encuentran las rutinas que se ejecutan en la apertura o cierre de libro, al entrar o salir de una hoja, al seleccionar o cambiar valores en celda … ç 2- Al presionar un botón dibujado en la hoja. Podemos utilizar botones de la barra de Herramientas 'Cuadro de controles' o de la barra 'Formularios'. 2- Dibujamos el control en la hoja, ajustamos algunas propiedades como texto, tamaño, ubicación. Al hacer clic derecho sobre el control optamos por 'Ver código' o 'Asignar macro' según qué barra de herramientas hemos utilizado. a-

Cuadro de controles: al optar por 'Ver código' nos llevará al Editor donde se generará automáticamente una rutina con estas 2 instrucciones: Private Sub CommandButton1_click() 'aquí escribiremos nuestras instrucciones End Sub

b-

Formularios: ya debemos tener lista la rutina en un módulo del Editor y la asignaremos cuando optemos por 'Asignar macro'. Las rutinas para este caso son como el siguiente ejemplo: Sub nombre_rutina() 'nuestras instrucciones End Sub

3- Ejecutar un atajo de teclado: En estos casos tendremos una rutina como la del punto anterior (b) en un módulo del Editor. Desde la hoja Excel, menú Herramientas, Macros, Macros seleccionarla de la lista. Presionar el botón 'Avanzadas' e ingresar una letra, por ejemplo 't' (sin las comillas) Nota: Si Excel ya tiene asignada esta letra la reemplazará por Ctrl+Mayúsc +t Esto significa que en el momento que necesitemos que la macro se ejecute presionaremos juntas las teclas Ctrl + t (o Ctrl+Mayusc+t) 4- Desde el menú Herramientas: si no hemos asignado la rutina que se encuentra en un módulo a ningún botón ni a atajo de teclado, la ejecutaremos desde este menú de Excel. En versión 2007, activar la opción Programador de la Cinta de Opciones.

Referencias: Veamos los términos utilizados para referirnos a libros, hojas y celdas:

Workbook

:

Libro de trabajo.

ActiveWorkbook : Libro activo Workbooks("Libro1.xls") : Llamada al libro de nombre Libro1 Workbooks(2) : El segundo libro abierto WorkSheet :

Hoja de trabajo

ActiveSheet : Hoja activa Sheets("Enero") : Hoja de nombre 'Enero' Sheets(3) : Número de hoja del libro según el orden de las pestañas. Range

:

rango o celda

Activecell : la celda activa Range("A2") : la celda A2 Cells(2,1) : la celda A2 . Nótese que mientras en Range se introduce la celda en el orden Col,Fila, en Cells es a la inversa: Cells(fila,col) Range("A5:B10") : rango de celdas desde A5 hasta B10 inclusive Range("E:E") : columna E Range("2:2") : fila 2

Trabajando con Libros En esta sección aparecerán algunas de las instrucciones 'básicas' para utilizar con LIBROS. Una vez copiada en el Editor, colocar el cursor sobre la palabra en negrita y presionar F1 para acceder a la Ayuda Excel. Allí se encontrarán más explicaciones y ejemplos para esa instrucción. 1-Abrir un segundo libro: Application.Workbooks.Open "C:\Mis docu\Libro1.xls" ' o Workbooks.Open "C:\.....\Libro1.xls" 2-Activar un segundo libro: Workbooks("Libro2.xls").Worksheets("Hoja3").Activate Workbooks(2).Sheets(3).Activate 3-Cerrar un libro (sin guardar): Workbooks("Libro1.xls").Close False 'o ActiveWorkbook.Close False 4-Cerrar un libro (guardando los cambios): ActiveWorkbook.Save ActiveWorkbook.Close 5-Guardar un libro con otro nombre:

'o

ActiveWorkbook.SaveAs Filename:="C:\Mis doc\Libro1.xls", Password:="clave", ReadOnlyRecommended:=False

FileFormat:=xlNormal,

Estas son algunas de las opciones. Si se omiten, escribir la coma, como en el siguiente ejemplo: ActiveWorkbook.SaveAs Filename:="C:\Mis doc\Libro.xls",,, ReadOnlyRecommended:=False 6-Guardar un libro cuyo nombre será el valor de una celda: ActiveWorkbook.SaveAs Filename:=Range("A2").Value 7-No mostrar aviso al salir, al eliminar hoja, o cualquier aviso que queremos omitir: Application.DisplayAlerts= False 'volverla a True al finalizar la macro 8-Deshabilitar la opción de actualizar vínculos al abrir un libro: Application.DisplayAlerts= False 'volverla a True al finalizar la macro WorkBooks.Open Filename:= "C:\Mis docu\pruebas.xls", UpdateLinks:= 0 9-No mostrar la ejecución de la macro (movimiento de pantalla): Application.ScreenUpdating = False 'volverla a True al finalizar la macro 10-Ejecutar una macro al abrir un libro: Insertar un módulo y allí escribir una rutina: Ejemplo: Sub Nuevamacro () 'instrucciones, por ej: MsgBox "Bienvenid@" End Sub Y agregar esta otra en el objeto ThisWorkbook: Private Sub Workbook_Open () Nuevamacro 'o también se puede llamar utilizando la expresión Call 'Call Nuevamacro End Sub

Trabajando con Hojas En esta sección aparecerán algunas de las instrucciones 'básicas' para utilizar con HOJAS. Una vez copiada en el Editor, colocar el cursor sobre la palabra en negrita y presionar F1 para acceder a la Ayuda Excel. Allí se encontrarán más explicaciones y ejemplos para esa instrucción. 1- Activar o seleccionar otras hojas: Sheets("Hoja2").Activate 'o Sheets(2).Select 2-Seleccionar la hoja anterior o siguiente: ActiveSheet.Previous.Select 'hoja anterior a la activa ActiveSheet.Next.Select 'hoja posterior a la activa 3-Datos de la hoja:

ActiveSheet.Name ActiveSheet.Index

'nombre de la hoja 'número de hoja

4-Copiar datos de una hoja a la siguiente: Selection.Copy 'previamente se habrá seleccionado algo ActiveSheet.Paste Destination:=ActiveSheet.Next.Range("B2") 5- Ocultar filas o columnas: ActiveCell.EntireRow.Hidden=True ActiveCell.EntireColumn.Hidden= True 6-Proteger o desproteger una hoja: ActiveSheet.Protect "contraseña" ActiveSheet.Unprotect "contraseña"

' filas 'col

'proteger con contraseña 'quitar la protección

7-Insertar filas o columnas: 'reemplazar i por el nro de columna Workbooks("Libro1").Sheets("Hoja2").Column(i).Select Selection.EntireColumn.Insert 'reemplazar Column por Row en caso de filas. 8-Eliminar filas o columnas: ActiveSheet.Row(n).Select 'reemplazar n por el nro de fila Selection.EntireRow.Delete 9-Insertar una imagen en una hoja: ActiveSheet.Pictures.Insert(ruta).Select 'la ruta va entre comillas, por ej: "C:\Mis docu\Foto1.jpg" 10-Imprimir la hoja seleccionada: ActiveWindow.SelectedSheet.PrintOut Copies:=1, Collate:=True 'o ActiveSheet.PrintOut Reemplazar PrintOut por PrintPreview para realizar solo una vista preliminar.

Trabajando con Rangos o Celdas

En esta sección aparecerán algunas de las instrucciones 'básicas' para utilizar con CELDAS o RANGOS. Una vez copiada en el Editor, colocar el cursor sobre la palabra en negrita y presionar F1 para acceder a la Ayuda Excel. Allí se encontrarán más explicaciones y ejemplos para esa instrucción.

1- Formas de seleccionar una celda o un rango de celdas:

Range("B7").Select

'selecciona la celda B7

Range("B:B").Select

'selecciona toda la columna B

Range("A4:A10, D10, B5:B20").Select 'selecciona rangos discontinuos

Range("A"&variable).Select 'selecciona la celda cuya fila será el valor de la variable

Si definimos una variable que contiene la última fila a considerar: rango=("B2:E" & variable) , entonces la siguiente instrucción selecciona el rango B2:E hasta la fila indicada en la variable. Range(rango).Select

De igual manera puede establecerse el rango de inicio: rango = "B" & ini & ":E" & fini Range(rango).Select

Range("A:A, D:F").Select Range("2:2, 4:7").Select

'selecciona las columnas A, D, E y F 'selecciona las filas 2 y desde 4 hasta la 7

2- Seleccionar celdas a cierta distancia de la celda activa:

Sheets(1).Range("A1").Offset(2,3).Select 'selecciona 2 filas hacia abajo y 3 col a derecha de A1 = D3

ActiveCell.Offset(-10,1).Select 'selecciona la celda que se encuentra 10 filas por encima y 1 columna a la derecha de la celda activa.

3- Formato de celdas:

Range("B2:D10").Select 'selecciona el rango y le aplica los siguientes formatos With Selection .Font.Bold=True 'formato negrita .Font.Italic=True 'formato cursiva .Font.Underline=xlUnderlineStyleSingle 'subrayado simple .Font.Color = RGB(255,0,0) 'color de fuente (para estos valores será rojo) .HorizontalAlignment=xlCenter 'alineación central (Right=derecha, Left=izquierda) End With

4- Guardar la dirección de una celda en una variable:

lugar= ActiveCell.Address 'guarda la referencia absoluta, por ej: $E$2

lugar= ActiveCell.Address(False, False) 'guarda la referencia relativa, por ej: E2

5- Copiar un comentario en otra celda:

ActiveCell.Offset(0,1).Value = ActiveCell.Comment.Text 'copia el comentario de la celda activa en la celda que se encuentra en la columna siguiente.

6-Seleccionar celdas y borrarlas:

Range(rango). Select o Selection.ClearContents

Cells.Select

7- Ampliar un rango seleccionado

Selection.Resize(10,4).Select ' (10 filas, 4 columnas) ' si el rango seleccionado fue A1:B5 ahora será: A1:D10

8- Combinar celdas selecccionadas:

Range("B1:E1").Select Selection.Merge

9- Seleccionar el rango donde se encuenra la celda activa:

Range("B2").CurrentRegion.Select

10- Seleccionar hasta la última celda no vacía:

Range("A2", Range("A2").End(xlDown)).Select 'seleciona desde A2 hacia abajo

Range("A2", Range("A2").End(xlToRight)).Select 'selecciona desde A2 hacia la derecha

Range("D2", Range("D2").End(xlToLeft)).Select 'selecciona desde D2 hacia la izquierda

Range("A20", Range("A20").End(xlUp)).Select 'selecciona desde A20 hacia arriba

En cambio, para seleccionar solo la última celda con datos será: Range("A2").End(xlDown).Select

Nota: Esta instrucción dará error si la celda A3 se encuentra vacía. Es preferible en ese caso realizar la búsqueda de 'abajo hacia arriba', con: Range("A65536").End(xlup).Select

11- Obtener la primer celda vacía: Range("A2").End(xlDown).Row + 1

'o

Range("A65536").End(xlup).Row + 1

Trabajando con Colecciones En esta sección aparecerán algunas de las instrucciones 'básicas' para utilizar con COLECCIONES. Una vez copiada en el Editor, colocar el cursor sobre la palabra en negrita y presionar F1 para acceder a la Ayuda Excel. Allí se encontrarán más explicaciones y ejemplos para esa instrucción.

Una colección es un conjunto de objetos del mismo tipo: hojas, celdas, controles o Shapes, imágenes. Para trabajar con una colección se programa un bucle: es decir repetir la misma instrucción para cada elemento de la colección. A continuación algunos ejemplos del bucle: For Each....Next

Ejemplo1: introducir un nombre para cada hoja del libro activo: Dim MiNombre as String Dim hoja as Worksheet For Each hoja in Worksheets MiNombre = InputBox("Ingrese nombre de hoja: ") Next hoja Ejemplo2: introducir valores para cada celda de un rango Dim celdita as Range For Each celdita in ActiveSheet.Range("A1:B10") celdita.Value = InputBox("Ingrese valor: ") Next celdita Ejemplo3: introducir los mismos valores en celdas de todas las hojas Dim hoja as Sheets For Each hoja in Sheets hoja.Range("E3").Value = Date hoja.Range("F3").Value = Time Next hoja

OTRO TIPO DE BUCLES: Ejemplo1: realizar una acción por cada valor que tome una variable i: Dim i as Byte Dim hoja as Worksheet For i=1 to 5 Msgbox WorkSheets(i).Name Next En este ejemplo se muestra el nombre de cada hoja, desde la nro 1 a la 5 Ejemplo2: realizar una acción mientras se cumpla una condición. 'Se recorre la col A a partir de la fila 2 hasta encontrar una celda vacía. 'El valor de cada celda se incrementa en 1 Range("A2").Select While Activecell.Value "" ActiveCell.Value = ActiveCell.Value + 1 Wend

Trabajando con Objetos

A continuación algunos ejemplos de cómo llamar a objetos insertados en hoja, como ser Cuadros de texto, Listas o Cuadros combinados y también un Userform. 1- Llamando a un Userform, desde un botón: En una hoja de Excel, los botones que lanzan una acción, pueden ser colocados con la barra de Formularios o Cuadro de Controles. a. Botón de formulario: se asigna una macro, como la del ejemplo, que previamente se escribió en un módulo en el Editor de Visual Basic. Ejemplo: Sub mostrando () UserForm1.Show End Sub

'nombre del Userform que se desea mostrar

b. Botón del Cuadro de controles: una vez dibujado en la hoja, clic derecho, opción Ver código y escribir la rutina (ésta se habilita en la hoja donde aparecerá el control). Ejemplo: Private Sub CommandButton1_Click() UserForm1.Show End Sub 2- Asignando valores a los Cuadros de texto: Ejemplos de cómo registrar datos en los textbox dibujados en hoja con la barra 'Cuadro de controles': TextBox1.Value = "CANCELADO" TextBox2 = Sheets("Hoja3").Range("A5").Value TextBox2 = Range("C1").Value + Range("C2").Value Notese que en Textbox2 se omitió la expresión 'value' ya que esa es su propiedad predeterminada. Puede colocarse o no. 3- Volcar datos de un control Textbox y Combobox a la hoja: Estos controles han sido dibujados con la barra 'Cuadro de controles' 'pasar datos de un control Textbox Cells(fila, col).Value = TextBox1.Value 'pasar datos de la selección de un combobox Sheets("Hoja1").Cells(fila, col).Offset(1, 0).Value = Combobox1.Value

4- Asignar rango a un control Listbox y Combobox: Estos controles han sido dibujados con la barra 'Formularios' 'asignar rango de entrada al control Lista ActiveSheet.Shapes("List Box 1").Select With Selection .ListFillRange = "$F$1:$F$4" End With 'asignar rango de entrada al control Combobox ActiveSheet.Shapes("Drop Down 1").Select With Selection .ListFillRange = "$K$1:$K$7" End With

5- Cargar datos a un control ListBox de un UserForm: Private Sub UserForm_Activate() Dim item As Variant For Each item In Range("F1:F6") ListBox1.AddItem item.Value Next item End Sub