Dedicatoria
Al Dios Supremo, por haberme dado la vida, el
conocimiento y estar rodeado de las personas que más
queremos.
1
Agradecimientos
El agradecimiento a nuestras madres y padres
quienes con sus sabios consejos supieron hacer un
hombre de bien y vocación de servicio a la
colectividad, a mis alumnos gracias a ellos, aprendí y
aprendo hacer un mejor profesional.
2
Resumen
El objetivo de este manual es enseñarle, mediante ejercicios prácticos, como crear macros
en un entorno Microsoft Excel y permitir reproducirlas automáticamente.
No está dirigido a programadores si no a cualquier usuario habitual, el manual está
organizado en 3 capítulos.
3
Tabla de Contenidos
Introducción
Capitulo I: Grabador de Macros..............................................................................................6
Capitulo II: Creacion de formulario ultilizando Macro........................................................13
Capitulo III: Macro usando codigo VB.................................................................................32
Capitulo IV: Filtros Avanzados Usando Macros..................................................................40
Capitulo V: Sql reportes en excel..........................................................................................49
Bibliografia..........................................................................................................................5
4
Introducción
Las macros de Excel nos permiten automatizar tareas que realizamos cotidianamente de
manera que podamos ser más eficientes en nuestro trabajo. Una macro no es más que una
serie de comandos o instrucciones que permanecen almacenados dentro de Excel y que
podemos ejecutar cuando sea necesario y cuantas veces lo deseemos. (Ortíz, 2011)
El programa Excel dispone de una gran cantidad de objetos. “El automatizar tareas nos
permite en determinado momento, minimizar esfuerzos y recursos”. (Delgado, 2010)
Uno de los principales beneficios de las macros de Excel, es poder automatizar las tareas
que realizamos en nuestro día a día, y los ahorros que logramos por medio de la
automatización nos permitirán ser más eficientes en nuestro trabajo, o en la actividad en la
que nos encontremos.
Sin bien las macros son temas avanzados de Excel, tan pronto como hayas comenzado con
las macros y la programación en VBA, encontrarás que el Excel se puede convertir en una
herramienta muy útil para resolver problemas complejos.
5
Capítulo I: Grabador de Macros
Ejemplo Creación de una búsqueda sencilla usando grabador de Macros
1. Habilitar la opción Desarrollador dentro de nuestro Excel, para eso entramos a
archivos, luego opciones y en personalizar cinta de opciones; marcamos la casilla de
Desarrollador y aceptamos.(Fig1)
6
Fig1.Opciones de Excel.
2. Crear una tabla con las columnas Id producto, Nombre del producto, cantidad y
Marca; rellenar datos como se muestra en la imagen. (Fig2)
Fig2. Datos de la tabla.
3. En la misma hoja de Excel crear una tabla con 2 celdas que nos ayudaran a hacer la
búsqueda y añadir un botón que nos servirá para ejecutar la macros.(Fig3)
7
Fig3. Celdas para búsqueda.
4. Agregar una nueva hoja donde se copiaran los datos de la búsqueda, y crear 4
celdas.(Fig4)
8
Fig4. Resultados.
5. Estando en la Hoja2 , Vamos a la pestaña Desarrollador, Gravar macro y asignamos
un nombre en este caso será Búsqueda y aceptar(Fig5)
9
Fig5. Gibar.
6. Luego vamos a Datos, Filtro y avanzada; con el cursor sobre “Rango de la lista”,
vamos a Hoja1 y seleccionamos toda la tabla de nuestros datos.(Fig6)
Fig6.
10
7. Cambiamos a “Rango de criterios” y seleccionamos nuestras celdas donde
pondremos los parámetros para realizar la búsqueda.(Fig7)
Fig7.
8. Marcamos la opción Copiar a otro lugar, colocamos el cursor en “copiar”; y
seleccionamos las casillas de la segunda Hoja donde aparecerán los datos que
coincidan con la búsqueda. Vamos a la pestaña Desarrollador y presionamos
Detener grabación, nuestra macros ya está lista.(Fig8)
11
Fig8.
9. Finalmente en la primera hoja vamos al botón que creamos que se hace insertando
una Forma hacemos clic derecho asignar macros, y elegimos en nombre dela macro
que acabamos de grabar, en mi caso la macro es Búsqueda.(Fig9)
Fig9.
10. Para probar la macro escribimos un valor o ambos en las celdas de la hoja 1(Fig10)
presionamos Buscar y podemos ver q en la hoja 2 se mostraran el resultado.(Fig11)
Fig10.
12
Capitulo II: Creación de formulario utilizando Macro
1- Primero hacemos el formato que le damos al formulario, en este caso utilizamos
una platilla donde ponemos Producto, cantidad y precio. Eso es para ingresar en la
hoja 2.(Fig12)
Fig12.
14
2- Hoja 2 ingresa los datos de la hoja 1(Fig13).
Fig13.
3- Como se realiza Primero grabar un macro ,ingresamos en la pestaña desarrollador y
le damos grabar macros(Fig14)
Fig13.
15
4- Saldar una pantalla, en nombre ponemos Inventario y le damos aceptar se supone
que ya está grabando el macro (Fig14)
Fig14.
5- Luego en la hoja 2 en eso espacios en blanco pegamos los cuadros q se encuentra
en la primero hoja. (Fig15)
16
7.- Luego vamos a la hoja 2 seleccionamos y le damos pegado especial. (Fig17)
Fig17.
8.- Y luego saldrá ese cuadro y le damos en balastes y aceptar así asenso con los tres
cuadros de producto, cantidad y precio. (Fig18)
18
Fig18.
9.- Por ultimo le damos detener grabación y generamos un botón en herramientas. (Fig19)
Fig19.
19
10.- Luego le damos inventario y aceptar. (Fig20)
Fig20.
11.- Probamos el programa. (Fig21)
Fig21.
20
12.- Y los datos pasaran a la hoja 2 por defecto. (Fig22)
Fig22.
En este ejemplo vamos utilizar macros en un sistema de ventas pequeño para ir mostrando
la lista de los pedidos que realiza el cliente a través de un botón asignándole una macro.
Para este ejemplo hemos diseñado previamente un Excel de la siguiente manera (Fig23):
Fig23.
21
El precio lo hayamos con el método coincidir, el monto multiplicando el precio por la
cantidad el descuento si el monto es mayor a 3000 y el total es el monto menos el
descuento.
Código precio: =INDICE(L6:P8;COINCIDIR(F7;L6:L8;0);L1+1)
Código descuento: =SI(C11>3000;C11*10%;0)
Código Marca: =INDICE(M5:P5;1;L1)
Diseñamos otra hoja en el Excel de la siguiente manera(Fig24):
Fig24.
La idea es pegar los datos de la fila 13 de la Hoja1 al hacer clic en el botón ingresar,
pasarlos a la Hoja 2 a partir de la fila 4 en adelante y al hacer clic en el botón de la Hoja 2
llamado regresar nos mande a la Hoja 1.Para eso usaremos macros.
22
1.- Primero crearemos el macro para pasar de la Hoja 2 a la Hoja 1 para eso vamos a vista,
macro, grabar macro le ponemos el nombre a la macro, método abreviado de teclado,
guardamos en este libro y ponemos una pequeña descripción y aceptamos.(Fig25)
Fig25.
2.- La macro ya empezó a grabarse, damos clic en la Hoja 1 y detenemos la macro ya que
eso será todo lo que hará esa macro. (Fig26)
23
Fig26.
3.- Ahora damos clic derecho en el botón regresar, asignar macro, seleccionamos la macro
que hemos creado para este botón y aceptamos. (Fig27)
Fig27.
4.- Ahora la probamos, damos clic en el botón regresar y veremos que ejecuta la macro
porque nos manda a la Hoja 1. (Fig28)
24
Fig28.
5.- Ahora veremos cómo hacer la macro para que copie la información de la hoja 1 a la hoja
2 como mencionamos anteriormente para esto vamos a la hoja dos, damos ctrl + fecha
hacia abajo y llegamos a la última fila de la hoja 2 y le ponemos de nombre fin. (Fig29)
25
Fig29.
6.- Ahora seleccionamos los datos que queremos pasar desde la hoja 1 y le damos copiar.
(Fig30)
Fig30.
26
7.- Usamos el pegado especial para ponerla en la Hoja 2 en la parte donde queremos. Bien
ahora para ir pegando uno debajo de otro hacemos lo siguiente, copiamos los datos de la
Hoja 1 presionamos F5 seleccionamos fin aceptamos nos pondrá en la última fila de la Hoja
2 ahora presionaremos ctrl + fecha para arriba y nos llevara donde está el ultimo pedido que
hicimos bajaremos una fila y haremos el pegado especial (valores). Eso haremos al grabar
la macro. (Fig31)
Empezamos a grabar la macro.
Fig31.
27
8.- Ahora que ya empezó a grabarse la macro copiamos los datos de la Hoja 1 y seguimos
los pasos dicho anteriormente.Fig (32)
Fig32.
9.- Presionamos F5 damos clic en fin y aceptamos. (Fig33)
28
Fig33.
Al aceptar nos manda a la última fila de la Hoja dos, presionamos Carl + fecha para arriba y
luego bajamos una fila y allí pegamos los datos. Detenemos la grabación de la Macro.
(Fig34)
Fig34
.
10.- Asignamos la macro ingresar al botón ingresar de la Hoja 1. (Fig35)
29
Fig35.
11.- Probamos la Macro y vemos que ingresa los datos en la línea 5 volvemos a ejecutar
con otros datos y se pegan encima de los datos puestos anteriormente en la línea 5 en lugar
de la línea 6. (Fig36)
Fig36.
12.- Para solucionar esto nos vamos a vista, macros, ver macros, ingresar, y damos clic en
paso a paso para modificarla. (Fig37)
30
Fig38.
La primera parte del código quiere decir que estamos copiando de A23 a G23 luego la
referencia fin , el ctrl + fecha hacia arriba
Para solucionar nuestro problema vamos a modificar el siguiente código:
Range("A5").Select
Por este de acá:
Selection.Range("A2").Select
El selection lo que hace es dar una posesión relativa y el Range(“A2”) hace que donde
estuvo el cursor la última vez se lea como celda uno y entonces mande los datos a la celda
2, entonces al pasar al fin luego ctrl + fecha hacia arriba ese casillero se lee como celda 1 y
la siguiente seria la celda 2 donde irán los datos.
14.- Ahora salimos del programa, se guarda el código y lo probamos (Fig39).
32
Fig39.
Pues bien vemos que ya podemos ingresar los datos y se van guardando fila a fila.
Capitulo III: Macro usando código VB
Ejercicio: Crear una calculadora en macros
1.-Crear un nuevo archivo de Excel, ir a desarrollador luego Visual Basic (Fig40).
Fig40.
33
2.- Insertar un UserFrom(Fig41).
Fig41.
3.- Ponerle de nombre frmCalculadora, y darle el siguiente diseño (Fig42).
34
Fig42.
4.- Declarar las siguientes variables en el formulario
Di x, y As Single
Dim operacion As String
5.- A continuación mostraremos el código para cada botón
BOTONES NUMERICOS
Prívate Sub cmd1_Click()
txtResultado.Text = txtResultado.Text + "1"
End Sub
35
Private Sub cmd2_Click()
txtResultado.Text = txtResultado.Text + "2"
End Sub
Private Sub cmd3_Click()
txtResultado.Text = txtResultado.Text + "3"
End Sub
Private Sub cmd4_Click()
txtResultado.Text = txtResultado.Text + "4"
End Sub
Private Sub cmd5_Click()
txtResultado.Text = txtResultado.Text + "5"
End Sub
Private Sub cmd6_Click()
txtResultado.Text = txtResultado.Text + "6"
36
End Sub
Private Sub cmd7_Click()
txtResultado.Text = txtResultado.Text + "7"
End Sub
Private Sub cmd8_Click()
txtResultado.Text = txtResultado.Text + "8"
End Sub
Private Sub cmd9_Click()
txtResultado.Text = txtResultado.Text + "9"
End Sub
BOTONES DE OPERACIONES
Private Sub cmdDivision_Click()
x = txtResultado.Text
operacion = "division"
txtResultado.Text = ""
37
End Sub
Private Sub cmdMenos_Click()
x = txtResultado.Text
operacion = "resta"
txtResultado.Text = ""
End Sub
Private Sub cmdMultiplicacion_Click()
x = txtResultado.Text
operacion = "multiplicacion"
txtResultado.Text = ""
End Sub
Private Sub cmdSuma_Click()
x = txtResultado.Text
operacion = "suma"
txtResultado.Text = ""
38
End Sub
BOTON “CE”
Private Sub cmdReinicio_Click()
txtResultado.Text = ""
End Sub
BOTON Resultado
Private Sub cmdIgual_Click()
y = txtResultado.Text
If operacion = "suma" Then txtResultado.Text = CInt(x) + CInt(y)
If operacion = "resta" Then txtResultado.Text = CInt(x) - CInt(y)
If operacion = "multiplicacion" Then txtResultado.Text = CInt(x) * CInt(y)
If operacion = "division" Then txtResultado.Text = CSng(x) / CSng(y)
End Sub
39
6.- Insertamos un módulo (Fig43).
Fig43.
7.- Creamos una macros en el modulo
Sub calculadora()
frmCalculadora.Show
End Sub
40
8.- creamos un botón al cual asignamos la macro creada (Fig44).
Fig44.
9.- Probamos la calculadora
41
Capitulo IV: Filtros Avanzados Usando Macros
En este caso realizaremos un ejercicio cuyo propósito es diseñar un Filtro Avanzado
automatizado con el uso de una Macro.
Para este ejemplo utilizaremos la siguiente lista de datos de las ventas de una empresa
que contiene el vendedor (Fig45), el producto, las unidades vendidas, el precio unitario, el
valor total y el sector.
Fig45.
42
Los cuadros de la parte de superior que se encuentran vacíos son los que usaremos como
criterios para realizar nuestros filtros avanzados que deben tener los mismos nombres que
nuestra información de ventas.
Lo que buscaremos es ingresar datos en los cuadros vacíos de la parte superior y que según
esos criterios se muestre la información en el cuadro.
1.- Para empezar a grabar la macro nos dirigimos a la barra de herramientas, vista, macros,
grabar macro (Fig46).
Fig46.
2.- Nos aparecerá un cuadro de dialogo con el nombre Grabar Macro donde ingresaremos el
nombre que queremos darle a la macro en este caso: Filtro avanzado, método de tecla
abreviado para que el macro se ejecute al pulsar dicha combinación de teclas, guardamos la
macro en este libro y le damos una pequeña descripción, luego aceptamos (Fig47).
43
Fig47.
3.- Luego de aceptar la macros empieza a grabarse y en parte inferior nos aparece un botón
para detener cuando hayamos acabado de grabar nuestra macro (Fig48).
Fig48.
44
4.- Vamos a la barra de herramientas, datos y damos clic en avanzadas (Fig49).
Fig49.
Nos aparece el cuadro de filtro avanzado (Fig50).
45
Fig50.
5.- En acción dejamos marcado Filtrar la lista sin moverla a otro lugar, y en rango de lista
marcamos nuestro rango de lista de la siguiente manera (Fi51):
Fig51.
6.- En el rango de criterio seleccionamos los cuadros de la parte superior de la siguiente
manera (Fig52):
46
Fig52.
Damos clic en aceptar y listo hemos acabado de grabar nuestra macro, vamos al botón
detener y le damos clic (Fig53).
Fig53.
7.- Ahora queremos llamar la macro que ya está grabada a través de un botón en este caso
vamos a insertar, formas e insertamos un cuadrado y le ponemos texto (Fig54).
47
Fig55.
9.- Nos aparecerá el siguiente cuadro, donde seleccionaremos la macro que hemos creado
en este caso Filtro Avanzado y le damos aceptar (Fig56).
Fig56.
49
10.- Ahora probemos si funciona nuestro filtro avanzado usando macros.
Para probarlo en unidades pondremos >50 para que solo muestre las ventas cuyas unidades
sean mayores que 50, y pulsamos el botón aplicar macro que creamos y le asignamos la
macro (Fig57).
Fig57.
11.- Y listo vemos que solo nos muestran las ventas con unidades mayores que 50(Fig58).
50
Fig58.
También podemos filtrar con varios criterios a la vez.
2 V: SQL reportes en Excel
1. Entramos al Excel y nos vamos a la opción datos y seleccionamos la opción de otras
fuentes para iniciar desde SQL Server (Fig59).
51
Fig59.
2. Luego nos aparece un asistente para la conexión de datos ponemos nuestro nombre de
servidor y le damos siguiente (Fig60):
Fig60.
52
3. Luego nos aparecerá una ventana que nos va permitir seleccionar la base de datos y la
tabla y de ahí le damos siguiente (Fig61).
Fig61.
4. Luego guardamos el archivo de conexión de datos y de ahí le damos finalizar (Fig62).
53
Fig62.
5. Luego nos aparece esta venta que nos va permitir seleccionar como queremos que vaya
aparecer los datos y le damos aceptar (Fig63).
54
Fig63.
6. Finalmente nos aparece el reporte de la tabla seleccionada y podemos ver lo que nos
piden (Fig64).
55
Referencias
Moisés Ortíz (enero del 2011) Excel Total. https://exceltotal.com/
Rob Wong (17 de julio 2015) udemy blog. https://blog.udemy.com/tutorial-de-macros-
excel-como-crear-una-sencilla-macro-en-excel-2013/
Ludovico Lannoy(2010) VBA Excel 2010: Desarrolle mediante un ejemplo una aplicación
profesional
Edwin Raymundo Delgado (2010) Macros en Excel.
https://docs.google.com/document/d/1VHYZAfWvunR-
BQfPZYu6tgWR9P29LuOPzn5dfEYmPro/edit?pli=1
57
Top Related