¿Qué es una macro de Excel? Si utilizas Excel f recuentemente es posible que en alguna ocasión te hayas encontrado ejecutando una misma serie de acciones una y otra vez. Esas acciones que haces repetidas veces se podrían automatizar con una macro. Una macro es un conjunto de comandos que se almacena en un lugar especial de Excel de manera que están siempre disponibles cuando los necesites ejecutar. Por ejemplo, si todas las mañanas creas un reporte de ventas y en ese reporte siempre das el mismo formato a los textos, se podría crear c rear una macro para que lo haga automáticamente por ti. Las macros se utilizan principalmente para eliminar la necesidad de repetir los pasos de aquellas tareas que realizas una y otra vez.
Lenguaje de Programación Las macros se escriben en un lenguaje de computadora especial que es conocido como Visual Basic for Applications (VBA). Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa.
Si no eres un programador de computadoras, Excel provee de una herramienta especial que permite crear una macro sin necesidad de conocer los detalles del lenguaje de programación.
1
Crear una macro automática Las macros se crean con la Grabadora de macros la c ual irá grabando todas las acciones que realicemos en Excel hasta que detengamos la grabación. Una vez grabada la macro podremos “reproducir” de
nuevo las acciones con tan solo un clic. Existe otro método que es utilizar el Editor de Visual Basic el cual nos permitirá introducir el código con las instrucciones que serán ejecutadas por la macro.
Por ejemplo, si frecuentemente aplicamos los mi smos formatos personalizados a una tabla, podemos crear una macro que lo haga automáticamente. La macro tendría que contener los siguientes procesos, toda macro debe ser previamente planificada: 1. Ubicado en una celda 2. Aplicar Negrita y Centrar 3. Aplicar los formatos de bordes 4. Aplicar los formatos de relleno
Todos estos pasos se guardan con un nombre de macro, los cuales se podrá ejecutar más adelante con un solo clic.
Ficha Programador Para trabajar con las herramientas de macros en Microsoft Excel debemos de activar en primer lugar la Ficha Programador. Para activarla realice los siguientes pasos: 1. Seleccione el menú Archivo 2. Haga clic en Opciones 3. Haga clic en Personalizar la cinta de opciones 4. Active la casilla correspondiente a la ficha Programador 5. Haga clic en Aceptar
2
6. Luego podremos elegir la ficha Programador , tal como se muestra en la siguiente figura
Seguridad de macros Microsoft Excel está configurado por defecto el bloqueo de las macros como medida de precaución de la presencia de alguna macrovirus en el sistema, esta configuración se puede cambiar para especificar las macros que se ejecutarán. Para modificar la seguridad de macros, realice los siguientes pasos: 1. Seleccione la ficha Programador
2.
Haga clic en el botón Seguridad de Macros que se encuentra en el grupo Código
3. En el panel derecho de la siguiente ventana de diálogo, seleccione Habilitar todas las macros y active la casilla Confiar en el acceso al modelo de objetos de proyectos de VBA
3
Grabar una macro Para grabar una macro, realice los siguientes pasos: 1. Seleccione la ficha Programador 2. Seleccione el botón Grabar macro
3. En la siguiente ventana de diálogo, asignar un nombre a la macro, por ejemplo Formatos, una letra para el atajo por ejemplo la letra f (minúscula) y una descripción de la macro (opcional).
4. Realice cuidadosamente todas las operaciones que desee grabar, por ejemplo: Seleccione una celda en la hoja Prueba
4
•
Seleccionar la ficha Inicio, luego active la caja: Formato de celdas y seleccione la ficha
Fuente
•
En la siguiente ventana de diálogo aplique formato de bordes y relleno, según las
indicaciones del profesor.
Nota: Cuando la macro se está ejecutando, guarda todas las acciones que realicemos.
5. Por último para detener la grabación seleccione la ficha Programador , haga clic en el botón Detener grabación.
Ejecutar una macro Para ejecutar una macro, se puede realizar de varias formas:
A través del comando 1. Seleccione la ficha Programador 2. Seleccione el botón Macros
5
3. En la siguiente ventana de diálogo, elija la macro que desee ejecutar 4. Haga clic en Ejecutar
5.
Ver el resultado de la ejecución de la macro
A través de un atajo •
Presione simultáneamente las teclas Ctrl + f
•
Ver el resultado de la ejecución de la macro
Guardar un libro con macros en Excel Para guardar un libro que contenga macros, realice los siguientes pasos: 1. Seleccione el menú Archivo 2. Seleccione la opción Guardar
3. Como es la primera vez que se guarda el libro con macro, aparecerá la siguiente ventana de diálogo, elija el botón No para guardar el libro habilitado para macros
6
4. En la siguiente ventana de diálogo, el nombre del libro será Demo_macros y elija como tipo de archivo Libro de Excel habilitado para macros
Asignar una macro a un botón Una manera más adecuada de ejecutar una macro es a través de botones de comando, para lo cual crearemos: La ficha
: Omega SAC
El grupo
: Mis formatos
El botón de comando
: Bordes personales
Creación de una Ficha y un Grupo Para crear una ficha realice los siguientes pasos: 1. Seleccione el menú Archivo 2. Seleccione Opciones 3. En la siguiente ventana de diálogo seleccione Personalizar la cinta de opciones 4. En el panel derecho de la ventana, haga clic en el botón Nueva ficha
7
5. Por último haga clic en Aceptar para ver la ficha creada
6. Luego haga un clic derecho sobre el Nuevo grupo y seleccione Personalizar la cinta de opciones
7. En la siguiente ventana haga clic derecho sobre el elemento Nueva ficha y elija Cambiar nombre
8. Luego escriba el nuevo nombre para la ficha
8
9. En forma similar haga clic derecho sobre elemento Nuevo grupo para cambiarle de nombre
10. Luego cambie el nombre para el nuevo grupo
11. El resultado final debe ser similar al de la siguiente figura
Agregar un botón Una vez creado la ficha Omega SAC y el grupo Mis formatos , procederemos a insertar un botón para la macro Formatos que creamos anteriormente. Realice los siguientes pasos:
9
1. Haga clic derecho sobre el grupo
Mis formatos y
del menú contextual elija Personalizar la cinta
de opciones
2.
En la siguiente ventana de diálogo, seleccione:
a)
El grupo Mis formatos
b)
En comandos disponibles Macros
c)
Seleccione la macro Formatos
d)
Seleccione Agregar
3. Luego cambiaremos la imagen y el nombre del botón insertado tal como se muestra en la siguiente figura.
4.
El resultado final será
10
5. Finalmente pruebe que el botón funcione, seleccione el rango de celdas de la hoja Tabla1 y haga clic en el botón Bordes personales, en forma similar con los rangos de las listas de las hojas Tabla2 y Tabla3
Vista de Códigos de una Macro de Excel Para observar los códigos de una mac ro crearemos en primer lugar una pequeña macro que realice los siguientes procesos: 1.
La macro se llamará Fuente y su atajo será Ctrl + f
2.
Aplicará a la celda activa los formatos de Negrita, Cursiva, Subrayado doble
11
3.
Luego ir a la ficha Programador y Detener la grabación
4.
A continuación probemos la ejecución de la macro, por ejemplo escriba en la celda D3 el texto
ESPECIALISTA EN EXCEL
5.
Luego estando en dicha celda presione simultáneamente las teclas Ctrl +f , el resultado será
6.
Luego iremos al Editor de Visual Basic para visualizar el código generado
12
7.
Que es lo que significa esto nos preguntaremos asombrados, a continuación se da una
explicación de lo que ha hecho Sub y End Sub
Excel:
Indican el inicio y final del procedimiento de la macro Fuente
´
Lo que aparece con apostrofe son solo comentarios que no inciden en la ejecución de la macro y aparecen en color verde.
Selection.Font.Bold = True
Indica que se ha asignado el atributo de negrita (Bold) a la celda activa
Selection.Font.Italic = True
Indica que se ha asignado el atributo de cursiva (Italic) a la celda activa
Selection.Font.Underline xlUnderlineStyleDouble 8.
= Indica que se ha asignado el atributo de subrayado doble (StyleDouble) a la celda activa
Para comprender mejor realice los siguientes cambios dentro del editor de Visual Basic
13
9.
Cierre el Editor de Visual Basic
10. Seleccione la celda B2 y escriba:
11. Ejecute nuevamente la macro con el atajo Ctrl + f, el resultado será
Códigos VBA Básicos
ActiveCell.Offset Desplaza el puntero de celda “F” Filas hacia abajo o hacia arriba y “C” Columnas a la derecha o izquierda, dependiendo si los valores de F y C son positivos o negativos respectivamente. Sintaxis: ActiveCell.Offset(F,C).Select Donde: F
Números de filas a desplazarse y C Número de columnas a desplazarse
Ejemplo: ActiveCell.Offset(0,2).Select
Desplaza el puntero dos columnas a la derecha
ActiveCell.Offset(3,0).Select
Desplaza el puntero tres filas hacia abajo
14
ActiveCell.Offset(1,5).Select
Desplaza el puntero una fila hacia abajo y cinco columnas a la derecha
ActiveCell.Offset(-2,-
Desplaza el puntero dos filas hacia arriba y cuatro columnas a la
4).Select
izquierda
Ejercicio: Veamos ahora como podemos desplazar el puntero de celda, mediante código de VBA. Repetiremos el ejercicio siguiente: deseamos desplazar el puntero de celda al final de la siguiente fila y desplazarnos una fila hacia abajo (ver la siguiente figura)
Solución: 1.
Ir a la ficha Programador
2.
Seleccione el botón Macros
3.
En la siguiente ventana de diálogo, escriba el nombre de la nueva macro, por ejemplo
Desplazamiento y luego haga clic en Crear
15
4.
En la siguiente ventana de códigos, escriba los siguientes códigos para el procedimiento
Desplazamiento
5.
Luego cierre la ventana del editor de Visual Basic
6.
Ahora ubique el puntero de celda en la posición
7.
Por último ejecute la macro
8.
El resultado será el mismo que se logró cuando se grabó las acciones de desplazamiento de
B3
manera manual
16
ActiveCell.Value Nos permite asignar un valor a la celda activa Sintaxis: ActiveCell.Value = Dato Dónde: Dato
: Es el valor que se almacenará en la celda activa
Ejemplo: ActiveCell.Value = 10 ActiveCell.Value = “IDAT”
Ejercicio: Crear una macro que ingrese el nombre de producto Leche, la cantidad a comprar 4 y el precio de 2.5
Solución: 1.
Ir a la ficha Programador
2.
Seleccione el botón Macros
3.
En la siguiente ventana de diálogo, escriba el nombre de la nueva macro, por ejemplo Datos y
luego haga clic en Crear
17
4.
En la siguiente ventana de códigos, escriba lo s siguientes códigos para el procedimiento Datos
Luego cierre la ventana del editor de Visual Basic 5.
Ahora ubique el puntero de celda en la posición B4
6.
Por último ejecute la macro Datos
7.
El resultado será similar al de la siguiente figura
18
A continuación veremos el código para calcular el importe
ActiveCell.Formula Nos permite asignar un valor a la celda activa como resultado de una fórmula Sintaxis: ActiveCell.Formula = Expresión Donde: Expresión: Es la expresión de una fórmula entre comillas, cuyo resultado se almacenará en la celda activa Ejemplo: ActiveCell.Formula = “=C4*D4”
ActiveCell.Formula = "=C[-2]*C[-1]" En el primer ejemplo se están multiplicando los valo res de las celdas C4 y D4 y en el segundo ejemplo el contenido de las celdas que se encuentra a dos y una columna a la izquierda de la celda activa. Ejercicio: Modificar la macro anterior para que calcule el importe de la c ompra realizada
Solución: 1.
Edite la macro Datos para modificarla
19
2.
Ahora agregue las dos últimas líneas de código para el procedimiento Datos
C[n]
Indica la referencia a una celda que está a “n” columnas de la celda activa, por
ejemplo: C[-1]
Indica la celda que se encuentra una columna a la izquierda de la celda activa
R[n]
Indica la referencia a una celda que está a “n” filas de la celda activa, por ejemplo:
R[3]
Indica la celda que se encuentra tres filas debajo de la celda activa
R[1]C[2]
Indica la celda que se encuentra una fila abajo y dos columnas a la derecha de la celda activa
20
Nota: Si
la referencia es en la misma fila (Row) o columna (Column) se podrá omitir 0 en C y R
respectivamente, es decir R[0] es lo mismo que poner solamente R 3.
Luego cierre la ventana del editor de Visual Basic
4.
Ahora ubique el puntero de celda en la posición B4
5.
Por último ejecute la macro Datos
6.
El resultado será similar al de la siguiente figura
InputBox Nos permite ingresar datos a través de una caja de dialogo. Sintaxis: InputBox([prompt],[Title],[Default],{xpos],[ypos] Dónde: Prompt itle Default pos,Ypos
: Es el texto mostrado en el cuadro de diálogo : Texto de título para el cuadro de diálogo : Valor por defecto mostrado en el cuadro de diálogo : La pantalla se ubicar según las coordenadas
Ejemplo:
21
ActiveCell.Value = InputBox ("Que estudias", "IDAT", "escriba el curso", 9000, 3000)
Ejercicio: Empleando InputBox ingrese el nombre del curso en la celda activa de la siguiente hoja de cálculo
Solución: 1.
Ir a la ficha Programador
2.
Seleccione el botón Macros
3.
En la siguiente ventana de diálogo, escriba el nombre de la nueva macro, por ejemplo Ingreso
y luego haga clic en Crear
22
4.
En la siguiente ventana de códigos, escriba los siguientes códigos para el procedimiento
Ingreso
5.
Luego cierre la ventana del editor de Visual Basic
6.
Ahora ubique el puntero de celda en la posición C7
7.
Por último ejecute la macro
8.
Se mostrará la siguiente ventana de diálogo
23
9.
Modifique el texto por defecto y escriba como nombre del curso Macros en Excel y haga clic
en Aceptar
10. El resultado final será similar al de la siguiente figura
Otros códigos VBA Básicos Código
Descripción
Selection.EntireRow.Insert
Inserta una fila en la hoja de cálculo
Selection.EntireColumn.Insert
Inserta una columna en la hoja de cálculo
Selection.EntireRow.Delete
Elimina una fila en la hoja de cálculo
Selection.EntireColumn.Delete
Elimina una columna en la hoja de cálculo
24
Práctica Calificada 01 Resolver: 1.
Crear una macro Ventas, que permita ingresar las ventas de las sucursales tal como se muestra
en la tabla de la siguiente figura.
Se ingresará a través de un InputBox c/u de las ventas (sucursales) del día Lunes
Luego el puntero se debe desplazar a la siguiente fila debajo de la venta de la primera
sucursal (Miraflores).
De forma similar ingresara los datos para el resto de días
Solución:
a)
Solo indicaremos la ventana de códigos, porque los pasos para llegar hasta él ya se han
detallado en los ejercicios anteriores.
b)
En la hoja Ventas, inserte un Botón de comando asociado a la macro Ventas
25
c)
Al ejecutar la macro se mostrará la siguiente ventana de diálogo e ingrese la venta para
la primera sucursal, tal como se muestra en la siguiente figura
d)
El primer resultado mostrara lo siguiente:
e)
En forma similar ingre el valor de las ventas de las demás sucursales, el resultado final
será:
26
2.
En la siguiente Hoja: Notas. Crear una macro llamada Notas, que permita ingresar las notas de
los siguientes alumnos y calcule su promedio. a)
Se ingresará las notas a través de un InputBox c/u para el alumno
b)
Se calculará su promedio
c)
Luego el puntero se debe desplazar a la siguiente fila debajo de la nota del primer curso
(Windows)
Solución: •
Solo indicaremos la ventana de códigos, porque los pasos para llegar hasta él ya se han
detallado en los ejercicios anteriores. (de acuerdo al siguiente modelo, que es solo un ejemplo, usted deberá dar solución a la macro Notas)
Asocie un botón a la macro Notas.
27
Ejecute la macro para ingresar las notas, notaremos que el promedio se calcula automáticamente.
28
