PDF de programación - Curso de Programación de Macros en Excel

Aviso legal: todos los textos y pantallas están extraidos del curso de macros de aulaclic.

Alfredo Rico – RicoSoft - 2011

2



1. Las herramientas del Excel

2. Programación orientada a objetos

4. VBA. Más estructuras
5. Programación usando objetos del
Excel

3. Lenguaje Visual Basic de Aplicaciones (VBA)

6. Creación de formularios de usuario

7. Aplicaciones



Í n d i c e d e t a l l a d o

Unidad 1. Las herramientas del Excel

Unidad 5. Programación usando objetos del Excel

Introducción


Herramientas del Excel
Aplicaciones de las macros
Botones de control de formularios para ejecutar marcos

Unidad 2. Programación orientada a objetos

Objeto
Propiedades
Método
Ejemplo de objeto

Unidad 3. Lenguaje Visual Basic de Aplicaciones

(VBA)

El Editor del Visual Basic
Variables
Sentencia DIM
Constantes
Módulos
Procedimientos
Argumentos
Asignación de valores o expresiones

Sentencia condicional: If … End If; Select Case …. End

Ingreso de datos. Emisión de resultados

Select

Objetos del Excel
Objeto Application. Propiedades. Métodos.

Objeto WorkBook. Propiedades y Métodos.

Objeto WorkSheet. Propiedades y Métodos.

Conjunto Range. Propiedades y Métodos.

Ejemplos.

Ejemplos.

Ejemplos.

Ejemplos.

Unidad 6. Creación de formularios de usuario

Ejemplos de interacción con módulos y macros.
Cuadros de control del UserForm

Unidad 7. Aplicaciones

Aplicación 1 : Consulta y extracción en una base de

Aplicación 2. Emisión de un reporte de compra.
Aplicación 3. Macro para imprimir un formato-

Aplicación 4. Realizar varias consultas y transferir

datos

recibo.

al Excel.

Aplicación 5. Seleccionar determinado producto

para su venta y transferir al Excel

Unidad 4. VBA. Más estructuras

Sentencias repetitivas
Arreglos (Vectores o Matrices) en VBA
Funciones



Alfredo Rico – RicoSoft - 2011

3

Unidad 1. Las herramientas del Excel

Introducción

Antes de ingresar al desarrollo del Lenguaje Visual Basic para Aplicaciones, haremos
una breve exposición de algunas herramientas del Excel que se supone son conocidas
por nuestro amable lector. Sin embargo, para unificar criterios y con el ánimo de
recordar lo conocido, haremos una exposición de la secuencia de pasos que se sigue
para ejecutar o usar determinadas herramientas.

Este repaso nos servirá también para mostrar dicha secuencia usado en las

versiones Excel 2003 y Excel 2007.

El objetivo de esta sección es, entonces, refrescar al participante de dichos
procedimientos a fin de que pueda usarlos sea durante la grabación de macros o
cuando tenga que programar ciertas acciones que requieran del uso de tales
procedimientos.

En el numeral 1 presentamos la secuencia de pasos para realizar o ejecutar la
herramienta con algunas observaciones y comentarios. En el numeral 2 daremos
algunos ejemplos de grabación de macros que hagan uso de determinadas
herramientas. En el numeral 3 desarrollaremos algunas macros que nos permitan
realizar algunas operaciones en Excel de uso frecuente. Esta introducción termina con
una breve exposición de la filosofía de objetos en la Programación Orientada a Objetos
(POO), técnica empleada en la programación de macros.



Alfredo Rico – RicoSoft - 2011

4

Herramientas del Excel

Filtro avanzado

Figura 1



Usamos filtro avanzado para poder extraer, sea en una misma hoja o en otra, ciertos

registros de una base de datos que cumplen determinadas condiciones.

Para realizar el filtro avanzado es necesario definir previamente el rango de criterios
así como conocer el rango de datos y la celda, a partir de la cual se deben emitir los
resultados.

El rango de criterios debe contener en su primera fila, los nombres de los campos

(columnas) y en las siguientes filas contener los valores o criterios del filtrado.

La imagen que se presenta en la Figura 01, nos muestran la secuencia de pasos que

se debe seguir para realizar el filtro avanzado.

En la ventana de diálogo Filtro avanzado seleccione dónde desea el listado, cuál es

el rango de criterios y a partir de qué celda se debe recibir los resultados.

Nota 1:

En el caso de Excel 2003, si desea extraer lo filtrado hacia otra hoja, entonces debe
definir el rango de los datos con un nombre de rango. Si desea, el rango de criterios
puede estar otra hoja, sólo que, para mayor facilidad se puede usar también nombre de
rango. En la versión 2007 no es necesario que el rango tenga nombre, pero sí que el
procedimiento se ejecute estando en la hoja hacia donde se desea el resultado.

Alfredo Rico – RicoSoft - 2011

5

Ejemplo

Abra el archivo Pedidos.xlsx. En la hoja Pedidos se tiene una lista de pedidos de
830 clientes, atendidos por un grupo de empleados y enviados a distintos destinos. En
la hoja Detalles de pedidos, se tiene la lista de productos pedidos por cada cliente. Se
desea obtener

a) Un reporte de los pedidos realizados por un cliente en particular.

b)Igualmente se desea obtener un reporte de todos los pedidos atendidos por dos

empleados en particular y cuya forma de envío sea Speddy Express.

Solución

Haciendo clic en el cuadro de nombres, apreciamos que los datos de la hoja
Pedidos tiene por nombre de rango, Pedidos y que la otra hoja tiene por nombre,
Detalle.

Pregunta a)



Insertemos una nueva hoja; que su nombre sea Reporte 1.

Copiemos toda la cabecera (nombres de campo) de la hoja Detalles de pedidos y la

pegamos en la primera fila de la hoja Reporte 1

Supongamos que se desea obtener los pedidos del cliente cuyo número de pedido

es 11077. Para ello digitamos debajo de Id Pedido, 11077 (en la hoja Reporte)



Ahora realizaremos el procedimiento del Filtro Avanzado:

Estando en la hoja Reporte, hacemos clic en <Avanzadas> del grupo <Ordenar y

filtrar> de la ficha <Datos>.

Completamos la ventana de diálogo que aparece, según se muestra en la siguiente

imagen:

Alfredo Rico – RicoSoft - 2011



6

 A la derecha se aprecia una parte de dicho reporte.



Pregunta b)



Inserte una nueva hoja y que se nombre sea Reporte 2.

Copiaremos la cabecera de la hoja Pedidos y la pegamos en esta nueva hoja.

Supongamos que se desea obtener un reporte de los pedidos atendidos por
Buchanan, Steven y por Davolio, Nancy. Esto significa que debemos ingresar estos
nombres debajo de la columna Empleado y "Speddy Express" lo ingresamos debajo
de Forma de envío pero repetido, como se muestra en la siguiente imagen:

A continuación realice el mismo procedimiento anterior digitando en Rango de la

lista: Pedidos. La siguiente imagen muestra parte de este reporte.



Alfredo Rico – RicoSoft - 2011

7

Consolidación



Se puede consolidar rangos de datos que están en una misma hoja, rangos de datos
que están en diferentes hojas o igualmente rango de datos que corresponden a libros
diferentes.

Nota 1:

Se debe tomar en cuenta que la consolidación se lleva a cabo sobre rangos que
tiene la misma estructura, aunque el número de filas puede variar entre un rango y otro.
Para los temas de consolidación use el archivo Ventas anuales.xlsx y Ventas
diarias.xlsx.

Nota 2:

Si los rangos tuvieran columnas que no puedan ser consolidadas, puede dejar de
incluirlas en la selección y usar las siguientes o trasladarlas al final a fin de que se
pueda elegir un rango adecuado. Siempre consolida tomando en cuenta la primera
columna del rango.

Consolidación de rangos de una misma hoja

La Figura 2 muestra la secuencia de pasos para realizar una consolidación de rangos

dentro de una misma hoja de un libro.

Observación

En la imagen de la Figura 2, se han consolidado cuatro rangos contenidos en
la misma hoja. Haciendo clic en el cuadro <Referencia> se han seleccionado cada
rango y luego se hizo clic en <Agregar>. Esto para cada rango a ser consolidado..

Alfredo Rico – RicoSoft - 2011

8

Figura 2



Consolidación de hojas

En este caso, los rangos de consolidación se encuentran en hojas diferentes, aunque

no necesariamente deben estar todos en hojas diferentes.

La secuencia de acciones se muestra en la figura 03.

Figura 3



En esta imagen se ha seleccionado el rango correspondiente a la hoja Semana1 y se

procede a <Agregar> a la consolidación la Semana2 y todas las otras semanas.

Nota 3

Alfredo Rico – RicoSoft - 2011

9

A

fin de

llevar a cabo una consolidación

interesante, abra el archivo

Proyecciones.xls y realice la consolidación de las proyecciones de todos los meses.

Nota 4

Recuerde que, si desea una consolidación efectuando una suma, promedio, etc.
Sobre la misma columna de consolidación, dicha columna debe tener la misma
cabecera en todos los componentes (rangos) de la consolidación. En este último caso,
en lugar de tener 2001, 2002, etc, se debiera tener un mismo rótulo.

Consolidación de libros

Igualmente, si los rangos a ser consolidados estuvieran en libros diferentes, también
se pueden realizar dicha acción. Para ello es necesario que estén abiertos todos los
libros a ser consolidados así como un nuevo libro donde se realizará la misma.

Tablas dinámicas

Una de las herramientas de mayor uso en el Excel es la tabla dinámica. Los datos a
ser empleados provienen de una tabla o base de datos en general. Tiene la
característica de ser dinámica por cuanto los elementos que conforman su estructura
pueden ser modificados (añadiendo