Aprender a programar con Excel VBA con 100 ejercicios prácticos
© MEDIAactive
Primera edición, 2013
© 2013 MARCOMBO, S.A.
Gran Via de les Corts Catalanes, 594
08007 Barcelona
En coedición con:
© 2013 ALFAOMEGA GRUPO EDITOR, S.A. de c.v.
C/ Pitágoras 1139 - Colonia del Valle
03100 - México D.F. (México)
Diseño de la cubierta: NDENU DISSENY GRÀFIC
«Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra sólo puede ser realizada con la autorización de sus titulares, salvo excepción prevista por la ley. Diríjase a CEDRO (Centro Español de Derechos Reprográficos, www.cedro.org) si necesita fotocopiar o escanear algún fragmento de esta obra».
ISBN por Marcombo: 978-84-267-2019-1
ISBN por Alfaomega:
D.L.: B-32985-2012
Printed in Spain
Presentación |
APRENDER A PROGRAMAR CON EXCEL VBA CON 100 EJERCICIOS PRÁCTICOS
Este manual presenta en 100 ejercicios prácticos resueltos un recorrido por los conceptos básicos de programación con Microsoft Visual Basic para Aplicaciones (VBA) para Excel. Si bien es imposible recoger en las páginas de este libro las infinitas posibilidades que ofrece este potente programador, una vez realizados los 100 ejercicios que componen este manual, el lector manejará nociones fundamentales para comenzar a programar por su cuenta y sacar máximo provecho a la aplicación.
LA FORMA DE APRENDER
Nuestra experiencia en el ámbito de la enseñanza nos ha llevado a diseñar este tipo de manual, en el que cada concepto se ejercita mediante la realización de un ejercicio práctico. Dicho ejercicio se halla explicado paso a paso y pulsación a pulsación, a fin de no dejar ninguna duda en su proceso de ejecución. Además, lo hemos ilustrado con imágenes descriptivas de los pasos más importantes y los resultados que deberían obtenerse. Por otra parte, los recuadros IMPORTANTE ofrecen información complementaria sobre los temas tratados en los ejercicios.
Gracias a este sistema se garantiza que una vez realizados los 100 ejercicios que componen el manual, el usuario será capaz de crear sus propias macros y funciones personalizadas, compartirlas correctamente con otros usuarios y sacar el máximo partido de sus múltiples prestaciones.
LOS ARCHIVOS NECESARIOS
En el caso de que desee utilizar los archivos de ejemplo de este libro, cosa que recomendamos, puede descargarlos desde la zona de descargas de la página de Marcombo (www.marcombo.com) y desde la página específica de este libro.
A QUIÉN VA DIRIGIDO EL MANUAL
Este libro está destinado a usuarios experimentados de Microsoft Excel que desean iniciarse en el mundo de la programación para hacer más eficientes sus hojas de cálculo y automatizar procesos mediante la creación de códigos de programación en lenguaje Visual Basic.
Aunque cada ejercicio está tratado de forma independiente, le recomendamos seguir el orden del libro, puesto que en muchos ejercicios se trabaja de forma progresiva sobre los mismos proyectos. Además, hemos intentado agrupar aquellos ejercicios con temática común. De cualquier manera, los ejercicios son independientes y si necesita realizar una consulta puntual, podrá dirigirse al ejercicio en el que se trata el tema y llevarlo a cabo sobre su propio proyecto.
VISUAL BASIC PARA APLICACIONES Y MICROSOFT EXCEL
Visual Basic para Aplicaciones es una herramienta proporcionada por Microsoft junto a su Suitte Office que permite crear toda clase de códigos para controlar la ejecución de sus programas. Con este manual aprenderá a grabar y utilizar macros para Excel que reducirán la realización de acciones repetitivas a la pulsación de botones de acceso directo en sus libros, en sus barras de herramientas o en la cinta de opciones. Podrá editar desde Visual Basic para Aplicaciones los códigos de las macros grabadas y también será capaz de escribir códigos íntegramente desde el editor.
Conocerá para ello el “ABC” del lenguaje de programación de Visual Basic, se familiarizará con los principales objetos, propiedades, métodos y eventos utilizados para crear códigos para Excel, practicará con la sintaxis de cada uno de ellos y comprobará su ejecución y efecto.
Con estos conocimientos creará macros que ejecutarán diversidad de modificaciones de contenido y formato en sus hojas de cálculo, convertirá datos en gráficos de forma automática y luego editará éstos procedimientos VBA. Así mismo, aprenderá a crear sus propias funciones y también cuadros de diálogo y formularios personalizados que ejecutarán toda clase de acciones.
Cómo funcionan los libros “Aprender…” |
Índice |
001 Grabar una macro
002 Ejecutar, modificar y eliminar una macro
003 Aplicar macro de otro libro
004 Usar el libro personalizado de macros
005 Crear botón de acceso rápido para macro
006 Crear grupo de macros en la cinta de opciones
007 Insertar botón de macro en una hoja
008 Establecer la seguridad para macros
009 Conocer Visual Basic para Aplicaciones
010 Personalizar la interfaz de VBA
011 Establecer propiedades del proyecto
012 Asignar contraseña de seguridad al proyecto
013 Modificar el código de un módulo VBA
014 Personalizar visualización de textos de los códigos
015 Crear un módulo de macro en VBA
016 Eliminar, exportar e importar un módulo
017 Consultar la ayuda de VBA
018 Crear macro con referencias absolutas
019 Crear macro con referencias relativas
020 Firmar una macro
021 Crear un procedimiento
022 Formular cálculos aritméticos en VBA
023 Introducción al uso de las variables
024 Opciones de depuración
025 Usar la ventana Inmediato
026 Definir un objeto y comprender jerarquías
027 Utilizar las propiedades
028 Conocer los métodos
029 Aplicar un método a una colección
030 Trabajar con los parámetros
031 Parámetros con valor de variable y otros casos
032 Agregar una inspección
033 Expandir expresión en ventana Inspecciones
034 Utilizar el Examinador de objetos
035 Convertir instrucciones en comentarios
036 Declarar tipos de variables
037 Hacer necesaria la declaración de variables
038 Más sobre declaración de variables
039 Declarar variables Public y Static
040 Trabajar con constantes
041 Utilizar variables y constantes tipo String
042 Declarar variables y constantes tipo Date
043 Conocer y usar las constantes predefinidas
044 Trabajar con el objeto Range
045 Range como propiedad del objeto Range
046 La propiedad Cells
047 Propiedades Text, Count, Row, Column y Address
048 Propiedades de Range que modifican formato
049 Los métodos Select Copy, Paste, Clear
050 Utilizar el método PasteSpecial
051 Intersectar, combinar, insertar y unir rangos
052 Utilizar funciones de Excel en una macro
053 Usar procedimientos de función
054 Declarar tipos de datos en una función
055 Describir funciones y variables
056 Declarar una matriz de datos
057 Crear una matriz de múltiples dimensiones
058 Convertir lista de datos en una matriz
059 Crear matriz dinámica o de tamaño cambiante
060 Crear un tipo de dato personalizado
061 Utilizar el control condicional If… Then
062 Utilizar el control If… Then… Else
063 Trabajar con varias instrucciones If… Then
064 Controles condicionales con ElseIf
065 Controles condicionales con Selec Case
066 Anidar controles condicionales
067 Crear una macro con bucle usando Do-Loop
068 Repetir instrucciones X número de veces
069 Aplicar bucle para el análisis de datos
070 Bucle en objetos de colección con For Each
071 GoTo o insertar salto a alguna instrucción
072 Usar Call o llamar a un procedimiento
073 Crear macro de inicio automático
074 Crear una macro de arranque
075 Crear procedimiento que se ejecuta al cierre
076 Ejecutar procedimiento al recalcular hoja
077 Crear procedimiento para nuevos libros
078 Usar BeforeDoubleClic en gráfico
079 Macros que se inician con teclado
080 Mostrar un cuadro de diálogo de Excel
081 Utilizar la función MsgBox
082 Incorporar datos con la función InputBox
083 Crear un formulario personalizado
084 Asignar instrucciones a controles de formulario
085 Crear procedimiento que abra el formulario
086 Insertar y utilizar controles (I)
087 Insertar y utilizar controles (II)
088 Crear un formulario de múltiples hojas y usarlo
089 Crear un gráfico desde VBA
090 Crear un gráfico incrustado
091 Crear gráficos con el método AddChart
092 Editar elementos de gráfico con SetElements
093 Propiedades y métodos de gráficos paso a paso
094 Aplicar filtro automático desde VBA
095 Filtrar por color y por valores relativos
096 Filtrar por matriz de datos, palabras e iconos
097 Añadir comando a menú contextual
098 Añadir botón a la ficha Comando de la cinta
099 Crear un complemento para Excel
100 Instalar y usar un complemento
001 | Grabar una macro |
UNA MACRO ES UNA SERIE DE instrucciones que el usuario proporciona para automatizar una secuencia de acciones con simple uso de un comando o una combinación de teclas para ello establecida y, de este modo, ahorrarse la necesidad de repetir un mismo proceso una y otra vez. La forma más sencilla de hacerlo es utilizando el grabador de Macros, que captura en un módulo de macro todos los pasos que usted realice durante el proceso de grabación.
IMPORTANTE
Al grabar una macro, se registran todos y cada uno de los pasos que dé el usuario durante la grabación. Para crear una macro más eficiente y rápida, planifique los pasos antes de iniciar la grabación.
1 En este ejercicio le demostraremos lo fácil que es crear una macro utilizando el grabador de macros de Excel. Para comenzar, descargue el archivo Prácticas1 de nuestra web y ábralo en su primera hoja, Pedidos_enero.
2 Crearemos una macro que sume los valores de la columna B en la celda B17. Seleccione la celda B17, que es la que mostrará el resultado de la macro. Antes de crear una macro debe seleccionar la celda en la que se mostrará el resultado de ésta.
3 Active la ficha Vista de la cinta de opciones de Excel pulsando sobre su pestaña.
4 Pulse el botón de punta de flecha del comando Macros para mostrar sus opciones.
5 Puede ver macros almacenados previamente, grabar una nueva macro o usar referencias relativas (más adelante veremos a qué se refiere). Seleccione la opción Grabar macro.
6 En el cuadro de diálogo Grabar macro, establezca como nombre SumaTotal.
7 En el campo Método abreviado escriba la letra H, en mayúsculas.
8 Ha establecido la combinación de teclas Ctrl.+Mayúsculas+H como acceso directo para la macro que va a grabar. Para este primer ejercicio mantenga seleccionada la opción Este libro del campo Guardar macro en y redacte una descripción para la macro en el campo Descripción.
9 En el momento en el que pulse el botón Aceptar, se iniciará la grabación de la macro. Lea antes las instrucciones de los próximos pasos para poder ejecutarlos con fluidez y, cuando esté preparado, pulse el botón Aceptar.
10 Muestre la ficha Fórmulas de la cinta de opciones.
11 Pulse el botón de flecha del comando Autosuma y seleccione la opción Suma. Todos los pasos que ejecute desde que pulsa el botón Aceptar hasta que detiene la grabación formarán parte de la macro.
12 Una vez se haya introducido la fórmula en la celda B17, que automáticamente debería seleccionar las celdas correctas, pulse la tecla Retorno para aplicarla.
13 Para detener la grabación de la macro, regrese a la ficha Vista.
14 Despliegue una vez más el menú del comando Macro y, en esta ocasión, seleccione la opción Detener grabación, ahora disponible.
15 Ya ha grabado su primera macro.
IMPORTANTE
El nombre de una macro siempre debe comenzar por una letra y no puede contener espacios en blanco aunque sí puede utilizar el guión bajo para separar conceptos.
002 | Ejecutar, modificar y eliminar una macro |
UNA VEZ HA GRABADO UNA MACRO, puede utilizarla para ejecutar con una sola instrucción y en un nuevo conjunto de celdas para ello definido, la serie de acciones que contiene. También puede cambiar sus opciones o eliminarla en cualquier momento.
IMPORTANTE
Tenga en cuenta que si utiliza como atajo de teclado para una macro una combinación de teclas que ya tiene una función en Excel, por ejemplo, Ctrl.+S (que habitualmente ejecuta el comando Guardar), la macro sobrescribirá al comando anterior para cualquier caso.
1 La gracia de grabar una macro es, evidentemente, reproducirla para ejecutar las acciones que contiene. Ubíquese en la celda C17 y pulse el atajo de teclado creado en el ejercicio anterior, es decir, Ctrl.+Mayúsculas+H.
2 Automáticamente se suman los valores de la columna C y se muestra el resultado en la celda C17. Se selecciona, además, la celda B17, porque también era la celda seleccionada al terminar el último paso de la macro. Haga un clic en la celda C17 y compruebe la fórmula que contiene.
3 Efectivamente, sólo se ha copiado la función SUMA para el mismo número de filas, pero en la columna correcta. Ubíquese ahora en la celda D17.
4 Despliegue el menú Macros y seleccione la opción Ver macros.
5 Se abre el cuadro de diálogo Macro, donde puede ver la macro SumaTotal. En los otros ejercicios trabajaremos con los diversos comandos de este cuadro. Pulse el botón Opciones.
6 en el cuadro de diálogo Opciones de la macro puede cambiar tanto el acceso directo asignado como la descripción, pero no el nombre. Sustituya la letra H por una S mayúscula y pulse el valor Aceptar. Puede acceder al cuadro Opciones de la macro y modificar las teclas establecidas como método abreviado de acceso y la descripción de las acciones que ejecuta.
7 Así de fácil ha sido modificar el atajo de teclado vinculado a la macro. Pulse ahora el botón Ejecutar para comprobar el efecto que tiene.
8 Se suman los valores de la columna D y se muestra su valor en la celda que estaba seleccionada al ejecutar la macro. Nuevamente se selecciona la celda B18, tal como sucedió al finalizar la grabación de la macro. Una macro puede incluir todo tipo de acciones. En este caso, por ejemplo, podría modificar también el formato de la celda en la que se ejecuta.
9 Como al crear la macro establecimos que se guardara en el libro abierto, se hace necesario convertirlo en un tipo de archivo que soporte macros. Para ello despliegue la vista backstage de Excel desde la pestaña Archivo y seleccione la opción Guardar como.
10 Despliegue el menú Tipo del cuadro de diálogo en pantalla y seleccione la opción Libro de Excel habitado para macros.
11 Pulse el botón Guardar.
12 Despliegue una vez más el menú del comando Macro y seleccione la misma opción de la última vez: Ver macros.
13 Puede ver en el cuadro de diálogo la macro creada. SU nombre se muestra precedido por el nombre del libro que lo contiene Seleccione la opción Eliminar y confirme que desea borrar la macro SumaTotal.
IMPORTANTE
Puede borrar cualquier macro con tranquilidad, pues los libros en los que haya sido ejecutada no sufrirán ningún cambio.
003 | Aplicar macro de otro libro |
SUS MACROS PUEDEN INCLUIR CAMBIOS en los atributos de celdas o texto, pueden afectar a varias celdas en una misma acción y pueden aplicarse a otras hojas de cálculo, siempre que tenga abierta en segundo plano la hoja que la contiene.
IMPORTANTE
En el cuadro Grabar macro, puede establecer que la macro no se inserte en el libro actual sino en un libro nuevo o en un libro de macros personal.
1 Pulse con el botón secundario del ratón sobre la cinta de opciones y escoja del menú contextual que se muestra la opción Personalizar cinta de opciones.
2 En el cuadro Opciones de excel, en el panel de la derecha, seleccione la ficha Programador y pulse el botón Aceptar.
3 Active la ficha que así se inserta en la cinta. Al habilitar la ficha Programador se añade una cantidad de utilidades interesantes a la aplicación.
4 Para este ejercicio borre las fórmulas insertadas en las celdas B17 a D17 en los dos ejercicios anteriores.
5 Vamos a crear una nueva macro. Seleccione las celdas B17 a D17 y escoja la opción Grabar macro, que ahora está disponible directamente en el grupo Código.
6 Llame la nueva macro TotalesMeses, asigne el método abreviado Ctrl.+Mayúsculas+H y escriba una descripción.
7 Mantenga seleccionada la opción En este libro del comando Guardar macro en y lea los pasos siguientes para ejecutarlos sin demoras. Cuando esté listo, pulse el botón Aceptar. Al crear una macro, puede guardarla en cualquier libro que esté abierto, o puede hacerlo en un libro personal de macros.
8 Active la ficha Fórmulas y pulse el botón Autosuma para insertar directamente la función Suma.
9 Active la ficha Inicio y, del grupo de herramientas Fuente, seleccione el comando Negritas, para cambiar el formato.
10 Despliegue ahora la paleta del comando Color de relleno y seleccione alguno que le agrade.
11 Regrese a la ficha Programador y pulse el botón Detener grabación.
12 Descargue ahora de nuestra web el archivo Prácticas2 y ábralo en la hoja Pedidos febrero.
13 Es una hoja de cálculo prácticamente idéntica a la anterior, salvo que en este caso los valores son distintos, pues se supone que corresponden a otro mes. Ubíquese en la celda B17.
14 Pulse el botón Macros de la ficha Programador de la cinta de opciones.
15 En el cuadro Macro puede ver la macro creada en el ejercicio anterior. Como se encuentra guardada en otro archivo, se muestra el nombre de éste antes del nombre de la macro. Despliegue el menú Macros en.
16 Si trabaja con muchas macros y tiene varios archivos abiertos a la vez, puede utilizar este menú para filtrar cuáles deberán mostrarse, y facilitar la búsqueda. Cierre el menú con un clic en el fondo del cuadro y pulse el botón Ejecutar para totalizar los datos de febrero.Puede aplicar en el libro abierto cualquier macro que esté guardada en otro libro, siempre que éste se encuentre abierto en el momento de hacerlo.
IMPORTANTE
Cuando trabaja con varios libros a la vez, existe la posibilidad de que haya asignado el mismo atajo de teclado a distintas macros, en distintos libros. En ese caso es probable que en algún momento no se ejecute la macro que usted espera. Por eso, si obtiene resultados inesperados, verifique los atajos de teclado y modifique los que le dan problemas, si es necesario.