Erhalten Sie Zugang zu diesem und mehr als 300000 Büchern ab EUR 5,99 monatlich.
Visual Basic for Applications (VBA) es el lenguaje de programación ideal para automatizar tareas repetitivas con macros y dar una mayor funcionalidad a los libros de Excel. Si quiere descubrir todas las soluciones que pueden aplicarse a los diferentes problemas con los que se puede encontrar como usuario de Excel, ha llegado al libro indicado. VBA ejecuta de forma sencilla y rápida determinadas secuencias con un gran número de acciones ordenadas. En el libro se presentan 100 ejercicios prácticos que le permitirán fabricar con VBA desde funciones de usuario personalizadas hasta formularios con los que construir auténticas aplicaciones. Además, gracias a esta lectura: "Descubrirá el entorno de VBA para escribir, ejecutar y comprobar sus métodos. "Creará sus propios módulos o ficheros, en los que podrá almacenar los diferentes procedimientos que fabrique con el tiempo y reaprovecharlos en otras aplicaciones. "Conocerá los diferentes tipos de datos que pueden utilizarse para realizar aplicaciones y definirá las variables tipadas, que ayudan a optimizar el uso de memoria. "Aprenderá los diferentes operadores matemáticos y lógicos. "Tendrá a su disposición las instrucciones que constituyen el lenguaje y aprenderá cuáles son las sentencias que podrá utilizar para confeccionar sus bloques de decisión, etc. "Analizará el modelo de objetos para añadir, eliminar o acceder a las piezas que componen internamente una aplicación. "Abordará los diferentes controles que puede incorporar a sus hojas o formularios. Asimismo, el contenido del libro se respalda con el código fuente utilizado en la explicación de los ejercicios, que podrá descargar de forma gratuita desde www.marcombo.info. Aproveche todas las posibilidades que le ofrece VBA, modifique la interfaz gráfica de Excel y emplee macros para realizar determinadas acciones personalizadas según su necesidad.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 189
Das E-Book (TTS) können Sie hören im Abo „Legimi Premium” in Legimi-Apps auf:
Aprender
Visual Basic para Aplicaciones en Excel
con 100 ejercicios prácticos
Aprender
con 100 ejercicios prácticos
Esta obra ha recibido una ayuda a la edición del Ministerio de Cultura y Deporte del Gobierno de España, por el Plan de Recuperación, Transformación y Resiliencia, Financiado por la Unión Europea (NextGenerationEU)
Aprender Visual Basic para Aplicaciones en Excel con 100 ejercicios prácticos
© 2023 Juan Antonio Gómez Gutiérrez
Primera edición, 2023
© 2023 MARCOMBO, S.L.
www.marcombo.com
Maquetación interior y diseño: María Paz Mora Encinas
Correctora: Haizea Beitia Aldaiturriaga
Director de colección: Pablo Martínez Izurzu
Directora de producción: M.ª Rosa Castillo Hidalgo
«Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra solo 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: 978-84-267-3590-4
Producción del ePub: booqlab
Este libro muestra los diferentes elementos a tener en cuenta cuando nos adentramos en el mundo de la programación de macros con Visual Basic. En primer lugar, muestra cómo es posible fabricar macros sin tener que escribir ninguna línea de código gracias al uso de la grabación de macros y, seguidamente, realiza un recorrido por las diferentes herramientas que componen el IDE de desarrollo (editor, ventanas diversas, barras de iconos, menús, etc. Además, destaca las sentencias más importantes del lenguaje, así como las funciones más utilizadas, con las que podrá resolver todo tipo de problemas gracias a los ejemplos que acompañan cada explicación.
Este libro está dirigido tanto a los entusiastas de Microsoft Excel que pretenden utilizar una de sus diversas funcionalidades para sacar mayor partido a la herramienta como a aquellos que necesitan material de consulta y ejemplos para resolver algunas tareas repetitivas que pueden encontrarse en su día a día, ya sea personal o profesionalmente.
El seguimiento de este libro no requiere ningún conocimiento especial previo, con lo que puede serle útil a cualquier persona que utilice Microsoft Excel.
Nuestra experiencia en el ámbito de la enseñanza nos ha llevado a diseñar este tipode manual, en el que cada una de las funciones 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 ilustradocon imágenes descriptivas de los pasos más importantes o de los resultados que deberían obtenerse, y con recuadros «IMPORTANTE» que ofrecen información complementaria sobre los temas tratados en los ejercicios.
En la parte inferior de la primera página del libro encontrará el código de acceso que le permitirá descargar de forma gratuita los contenidos adicionales del libro en www.marcombo.info.
Dedico este libro a mi mujer Victoria y a mi hijo Pablo, que son la alegría de mi vida. También se lo dedico a mi madre, a mi hermana y a mi padre, a los que quiero con todo mi corazón.
Por último, quisiera dedicárselo a mis amigos Victor y Quim que, a pesar de los años, siempre están ahí.
001 Crear libro xlsm y mostrar pestaña Programador
002 Grabar primera macro
003 Ejecutar macro
004 Asociar macro a imagen
005 Asociar macro a un botón
006 Asociar macro a opción de cinta de opciones
007 Crear macro con referencias absolutas/relativas
008 Modificar macro
009 Seguridad para macros
010 Guardar en libro personalizado de macros
011 Acceso al IDE de Visual Basic
012 Descripción de la interfaz
013 Herramientas: Referencias y controles adicionales
014 Herramientas: Opciones y propiedades del proyecto
015 Insertar módulo y crear procedimiento “Hola_Mundo”
016 Introducción y definición de variables y opción Explicit
017 Tipos de datos
018 Arrays
019 Módulos de clase
020 Colecciones de objetos
021 Tipos personalizados
022 Etiquetas y comentarios
023 Constantes y enumeraciones
024 Operadores matemáticos
025 Operadores de comparación
026 Operadores lógicos
027 If Then Else
028 For
029 For Each
030 Do While
031 Do Until
032 Select Case
033 GoTo
034 Llamada a un procedimiento
035 Creación de una función
036 De parámetros a procedimientos y funciones (I)
037 De parámetros a procedimientos y funciones (II)
038 Función de usuario y llamada desde una hoja
039 Asc, Chr, Val y String
040 InStr/InStrRev/StrComp
041 LCase/UCase/StrConv/StrReverse
042 Left/Right/Mid
043 LTrim/RTrim/Trim
044 Len/Replace
045 Formateo de números
046 Formateo de cadena
047 Formateo de fecha y hora
048 Array/Filter/Join
049 Split
050 Lbound/UBound
051 Redim
052 Date/DateAdd/DateDiff
053 Hour/Minute/Second/TimeSerial/TimeValue
054 Day/Month/Year/DateSerial/DateValue
055 Otras funciones de fecha
056 Abs/Rnd/Randomize
057 Sin/Cos/Tan
058 Round/Redondeos
059 Otras funciones matemáticas
060 Pmt/Nper/FV
061 IsArray/IsDate/IsNumeric, etc
062 Funciones_Conversion (Cbool, CByte, etc.)
063 Choose/IIf/Switch
064 Dir/CurDir/Environ
065 Shell/CreateObject
066 On Error Resume/Goto
067 Modelo de objetos
068 Application (propiedades, métodos y eventos)
069 Workbook (propiedades, métodos y eventos)
070 WorkSheet (propiedades, métodos y eventos)
071 Range/Cells (propiedades y métodos)
072 Crear un libro y guardarlo en un directorio
073 “Guardar como” y otros formatos
074 Workbooks
075 Worksheets
076 Windows
077 Shapes
078 Gráficos
079 WorksheetFunction
080 Enumeraciones y constantes de Excel
081 Introducción y presentación de controles de formulario
082 Ejemplos de controles de formulario
083 Introducción y presentación de controles ActiveX
084 Ejemplos de controles ActiveX
085 Creación de un formulario
086 Descripción de controles
087 Resumen de propiedades
088 Resumen de métodos
089 Resumen de eventos
090 Controles - I
091 Controles - II
092 Controles - III
093 Controles - IV
094 Ejemplo de automatización - I
095 Ejemplo de automatización - II
096 MsgBox
097 InputBox
098 Tratamiento de ficheros (Open, Close, Input, Write, etc.)
099 FileDialog/GetSaveAsFilename
100 Importar/Exportar Archivos VBA
Para poder trabajar con macros en un libro de Excel, disponemos de varias posibilidades, ya que podemos crear macros utilizando la función Grabar Macro o escribiendo todo su código al completo.
En nuestro primer ejercicio, vamos a utilizar la forma más sencilla, que es Grabar Macro. De esta forma, podremos disponer de una primera macro creada automáticamente con todos los detalles que se necesitan para su almacenamiento y ejecución.
Para ello crearemos un libro de Excel y mostraremos la pestaña Programador (si aún no la tenemos a la vista), ya que es la que nos va a facilitar todas las acciones relacionadas con la creación y ejecución de macros.
En primer lugar, abriremos Microsoft Excel y crearemos un Libro en blanco1. A continuación, trataremos de localizar la pestaña Programador2 en la cinta de opciones. Si no está disponible, tendremos que configurar Excel para que la muestre. Para mostrar la pestaña Programador, accederemos a Archivo -> Opciones3 y, en el cuadro de diálogo que aparece, seleccionaremos el apartado Personalizar cinta de opciones y, en la parte derecha, dentro de las pestañas principales que se muestran, marcaremos el casillero correspondiente a Programador4.
En este punto, ya vemos la pestaña Programador y ya podríamos empezar a trabajar con macros. Conviene recordar que, para poder grabar libros de macros, tendremos que guardarlos con la extensión xlsm, que es la extensión prevista para este tipo de libros. Para ello, la primera vez que guardamos el libro tendremos que guardarlo mediante la opción Archivo -> Guardar como -> Examinar5 y, en el cuadro de diálogo que aparece, pondremos el nombre que deseemos (p. ej. Ejercicio01.xlsm) y seleccionaremos el tipo Libro de Excel habilitado para macros (*.xslm)6.
Ahora, en el título de la ventana de Excel 7 comprobaremos que nuestro libro ya está guardado con la extensión necesaria para trabajar con macros.
Muestre la pestaña Programador para poder acceder a todas las funcionalidades asociadas al tratamiento de macros y utilización de VBA para Excel y recuerde grabar el libro con el formato xlsm, que es el correspondiente a Libro de Excel habilitado para macros.
En este ejercicio vamos a grabar nuestra primera macro, la cual simplemente consistirá en ajustar el ancho de todas las columnas para que el ancho coincida exactamente con la longitud de la mayor cadena de caracteres existentes en cada una de ellas.
Para ello, crearemos un libro nuevo de Excel y haremos un Guardar como (según vimos en el ejercicio anterior) con el nombre de Ejercicio02.xlsm.
A continuación, introduciremos algunos valores en las celdas A1, B1 y C11.
Seguidamente, localizaremos la opción Grabar macro en la pestaña Programador2 y pulsaremos sobre ella. Observaremos que aparece un cuadro de diálogo en el que podemos definir algunas características de la macro que estamos creando 3. Por ejemplo, como nombre de la macro introduciremos el texto “Ajuste01” y, en Descripción, indicaremos “Mi primera macro”. Podríamos definir alguna combinación de teclas que nos permitiera ejecutar la macro si especificáramos alguna letra en el campo Tecla de método abreviado y, asimismo, podríamos guardar la macro en un libro diferente al libro en curso 4 si cambiáramos la selección en Guardar macro en:. Este detalle, sin embargo, lo comentaremos en un ejercicio posterior con mayor detalle.
Cuando pulsamos en Aceptar, observamos que la opción Grabar macro desaparece y en su lugar aparece la opción Detener macro5 para que podamos detener la grabación cuando ya no queramos almacenar más acciones durante el proceso de grabación.
Ahora vamos a ejecutar secuencialmente cada una de las acciones que queremos almacenar en la macro. Las acciones a realizar serán las siguientes:
Seleccionar todas las celdas de la hoja cuyas columnas queremos ajustar 6. Para ello, simplemente hacemos clic en la esquina superior izquierda de la zona destinada a las celdas.
Hacemos doble clic en cualquiera de las separaciones de columnas para que se ejecute la acción Autofit (Autoajuste) 7.
Por último, detenemos la grabación8.
En este momento, ya hemos finalizado la grabación y disponemos de la macro dentro de nuestro propio libro.
En el siguiente ejercicio, veremos cómo podemos ejecutar esta macro cuando sea necesario.
Realice las acciones que desee almacenar en la macro de forma secuencial e indique un nombre descriptivo para reconocerla. Más tarde podrá corregir alguna si es necesario.
Existen diversas maneras de ejecutar una macro, pero la más sencilla e inmediata es la de acudir a la pestaña Programador y pulsar sobre la opción Macros1.
Para realizar este ejercicio, podemos hacer una copia del libro creado en el ejercicio anterior (Grabar primera macro; Ejercicio02.xlsm) sobre un libro nuevo llamado Ejercicio03.xlsm, o bien realizar los mismos pasos descritos para crear el libro y grabar la macro. Una vez que tengamos el libro Ejercicio03.xlsm creado, al abrirlo veremos con toda probabilidad un mensaje en el que se indica que “Las macros se han deshabilitado” 2. Por el momento, pulsaremos sobre el botón “Habilitar contenido” y en el ejercicio de Seguridad de macros ya comentaremos más detalladamente este tipo de situaciones. Una vez abierto el libro, veremos que las celdas A1, B1 y C1 contienen un texto de prueba, 3 el cual modificaremos introduciendo un texto de longitud variable para comprobar cómo se autoajusta el ancho de las columnas al ejecutar la macro.
Introduzcamos cadenas de diferente longitud en las celdas A1, B1 y C1. Por ejemplo, en A1 introducimos “Uno (pruebas)”; en B1, “Dos (Pruebas de longitud variable y extensa)”, y en
C1, “Tres (pruebas long)”4.
A continuación, pulsaremos sobre Macros desde la pestaña Programador y veremos que aparece el cuadro de diálogo Macro5 con diversas opciones y con una lista de las macros disponibles en todos los libros abiertos. En nuestro caso, solo se muestra la macro Ajuste01. Al pulsar sobre Ejecutar, comprobaremos cómo se ha ajustado el ancho de cada columna en función del nuevo texto introducido 6.
De nuevo, modificamos otra vez el texto y añadimos textos en las celdas D1 y E1 para comprobar que el ajuste se hace sobre todas las columnas, ya que la selección realizada en la macro alcanza a toda la hoja. En D1 introducimos “Nuevo” y en E1, “Texto para otra columna nueva”7. Después ejecutamos otra vez la macro Ajuste01.
Comprobamos una vez más que se ha ajustado de nuevo el ancho de cada columna, incluidas las columnas nuevas.
En el cuadro de diálogo Macro utilizado para ejecutar la macro disponemos de las siguientes opciones:
Nombre de la macro
Permite introducir el nombre de la macro a crear. Si se introduce un nombre nuevo, se hablita el botón Crear
Macros en
Permite seleccionar el libro que contiene la macro que deseamos ejecutar. Por defecto, se propone el libro en curso
Descripción
Contiene una descripción sobre la macro seleccionada
Ejecutar
Ejecuta la macro seleccionada
Paso a paso
Abre el editor de Visual Basic y ejecuta la macro en modo Debug ejecutando línea a línea
Modificar
Abre el editor de Visual Basic y permite la modificación del código fuente de la macro
Crear
Abre el editor de Visual Basic y permite la introducción de código de la nueva macro
Eliminar
Elimina la macro seleccionada solicitando confirmación previamente 8
Opciones
Permite modificar la Tecla de método abreviado y la Descripción de la macro9
Cancelar
Abandona el diálogo Macro sin realizar ninguna acción
Almacene las macros en el libro donde vaya a utilizarlas y guarde en Libro de macros personal aquellas macros de uso más generalizado que puedan utilizarse en otros libros de trabajo.
Otra forma de ejecutar una macro es asociándola a una imagen de forma que, al pulsar sobre la misma, se realice la ejecución.
Para facilitar la preparación del ejercicio, de nuevo sugerimos copiar el libro que creamos en el ejercicio Grabar primera macro (Ejercicio02.xlsm) sobre Ejercicio04.xlsm para disponer de una hoja con datos 1 y con la macro Ajuste01 ya creada.
A continuación, para preparar el ejercicio, modificaremos el contenido de las celdas A1, B1 y C1. Por ejemplo, en A1 introducimos “Uno (pruebas)”; en B1, “Dos (Pruebas de longitud variable y extensa)”, y en C1, “Tres (pruebas long)”2. De esta forma, vemos claramente que el ancho actual de las columnas no permite visualizar todo el contenido de cada celda.
Seguidamente, buscamos una imagen cualquiera y la insertamos en nuestra hoja. En nuestro ejemplo usaremos una imagen fabricada expresamente para el ejercicio que denominamos ImgAjuste.png3. Colocaremos la imagen mas o menos sobre la celda D3 dejando a la vista las celdas con información 4.
Ahora, haciendo clic con el botón derecho del ratón sobre la imagen, seleccionaremos la opción Asignar macro…5, lo que dará paso a la visualización del diálogo Asignar macro, el cual, por defecto 6, nos ofrece un nombre nuevo por si en ese momento queremos crear (Nuevo) o grabar (Grabar…) una macro nueva. En nuestro caso, sin embargo, seleccionaremos la macro que ya tenemos, Ajuste01. Observe cómo al pulsar sobre Ajuste01 desaparecen los botones Nuevo y Grabar y en su lugar aperece el botón Modificar, por si queremos modificar la macro antes de asociarla 7. A continuación, pulsamos en Aceptar y ya damos por finalizada la asignación de la macro a la imagen.
Una vez que se haya cerrado el diálogo Asignar macro, veremos que ahora, al pasar el puntero del ratón sobre la imagen, este cambia invitando a pulsar sobre la misma para ejecutar la macro 8.
Si pulsamos sobre la imagen, comprobaremos cómo la macro, efectivamente, se ejecuta y ajusta el ancho de cada columna en función del contenido de cada celda 9.
Para desenlazar la macro de la imagen, haremos clic con el botón derecho del ratón y, en el diálogo llamado Asignar macro, borraremos el texto que hace referencia a la macro en el campo Nombre de la macro.
Una vez que la macro está asignada a una imagen, cualquier cambio sobre la macro no implicará redefinir nada sobre la asignación. Recuerde que, además de asignar macros a imágenes, puede asociar vínculos, con lo que puede plantearse la fabricación de una página de tipo menú mediante una hoja que vaya distribuyendo diversas acciones basándose exclusivamente en imágenes.
En este ejercicio, crearemos un libro de macros y ejecutaremos una macro a través de un botón. Se trata de un ejercicio similar al visto anteriormente para asociar una macro a una imagen, con la diferencia de que, además de asociar la macro a un botón de formulario, esta vez la macro se creará durante el proceso de grabación que se lanzará durante la asignación.
En primer lugar, crearemos el libro Ejercicio05.xlsm abriendo Microsoft Excel, seleccionando Libro en blanco y, después, Archivo -> Guardar como -> Examinar -> Ejercicio05.xlsm1.
A continuación, introduciremos alguna información en las celdas A1, B1 y C1 para poder ejecutar posteriormente un autoajuste del ancho de las columnas, de forma que cada columna tenga exactamente el ancho necesario para visualizar el texto de cada celda sin truncar su contenido ni excederse de tamaño. Por ejemplo, podemos introducir “Uno” en A1, “Dos” en B1 y “Tres” en C12.
Seguidamente, desde la pestaña Programador, seleccionaremos Insertar -> Botón3 y dibujaremos un botón sobre la celda D34. Observaremos que se abre el cuadro de diálogo Asignar macro, que nos propone un nombre de macro por defecto (“Botón1_Haga_clic_en”) 5.
Modificamos el Nombre de la macro para introducir Ajuste05 y pulsamos en el botón Grabar para iniciar la grabación de la macro 6. Al pulsar sobre el botón Grabar, aparece el cuadro de diálogo Grabar macro, en el que podemos introducir una Descripción y pulsar Aceptar7 para iniciar la grabación.
Las acciones para realizar serán:
En primer lugar, seleccionaremos con el ratón las columnas A, B y C 8.
Seguidamente, haremos doble clic sobre cualquiera de las separaciones de columna (por ejemplo, entre la B y la C) para provocar el autoajuste 9.
Detendremos la grabación pulsando sobre la opción Detener grabación en la pestaña Programador. 10
Para comprobar el funcionamiento, modificaremos el contenido de las celdas A1, B1 y C1. Por ejemplo, en A1 introducimos “Uno (pruebas)”; en B1, “Dos (Pruebas de longitud variable y extensa)”, y en C1, “Tres (pruebas long)”11.
Por último, pulsaremos sobre el botón y comprobaremos que la macro se ha ejecutado y las columnas se han autoajustado a su ancho ideal 12.
Si lo desea, puede cambiar el texto del botón haciendo clic con el puntero del ratón sobre el mismo, seleccionando la opción Editar texto y sobrescribiendo el contenido con el texto deseado (por ejemplo, Autoajuste) 13.
Asigne macros a botones al igual que hizo anteriormente asociándolas a imágenes. Los botones aportan un funcionamiento muy intuitivo y permiten separar las acciones claramente dentro de una hoja o formulario.
En este ejercicio, añadiremos una pestaña nueva a la cinta de opciones con un grupo de comandos donde colocar finalmente nuestra macro.
Para realizar este ejercicio, podemos hacer una copia del libro creado en el ejercicio Grabar primera macro (Ejercicio02.xlsm) sobre un libro nuevo llamado Ejercicio06.xlsm, o bien realizar los mismos pasos descritos para crear el libro y grabar la macro.
Una vez dispongamos del libro Ejercicio06.xlsm con datos en las celdas A1, B1 y C1 y, además, la macro Ajuste011, lo abriremos y accederemos a la opción Archivo -> Opciones -> Opciones de Excel2.
En este cuadro de diálogo, lo primero que haremos será crear una pestaña haciendo clic en el botón Nueva pestaña3. Observaremos que se ha creado una nueva pestaña y un nuevo grupo dentro de la misma. Para cambiar el nombre de la nueva pestaña, haremos clic sobre el check que contiene el nombre Nueva pestaña (personalizada) y, a continuación, pulsaremos sobre el botón Cambiar nombre…, lo que dará paso a un pequeño cuadro de diálogo con el título Cambiar nombre en el que introduciremos, por ejemplo, “MisMacros”4.
Ahora, cambiaremos el nombre del grupo de forma similar a la descrita para la nueva pestaña. Seleccionaremos el nuevo grupo y pulsaremos sobre el botón Cambiar nombre…, donde en el cuadro de diálogo que aparece introduciremos el nombre Ajustes y seleccionaremos el icono que representará al grupo en la nueva pestaña recién creada 5.
Por último, seleccionaremos el nuevo grupo Ajustes (personalizada) y en el desplegable de Comandos disponibles, seleccionaremos Macros y, seguidamente, seleccionaremos la macro Ajuste01 y pulsaremos sobre el botón Agregar >> para añadir la macro a nuestro grupo 6.
En este punto, ya disponemos de una nueva pestaña (MisMacros) con un nuevo grupo (Ajustes) que contiene una llamada a nuestra macro (Ajuste01) 7. Para mostrar la pestaña en una posición concreta, basta con seleccionarla en el cuadro de diálogo Opciones de Excel y, dentro del apartado Pestañas principales, arrastrarla a la posición deseada 8.
Para eliminar la nueva pestaña recién creada, bastará con seleccionarla en el apartado Pestañas principales situado en el diálogo de Opciones de Excel y, con el botón derecho sobre ella, seleccionar Quitar10.
Utilice este tipo de ejecución preferiblemente con macros que tengan un interés general y que estén guardadas en su libro de macros personal.
Para probar la nueva opción, basta con seleccionar las columnas A, B y C y cambiarles el tamaño aleatoriamente para posteriormente pulsar sobre nuestra nueva opción Ajuste01 y comprobar cómo el ancho de las columnas se ha vuelta a ajustar automáticamente 9.
Cuando grabamos una macro, por defecto estamos usando la grabación con referencias absolutas, lo que significa que las acciones que se realizan se realizan siempre sobre las celdas que se indicaron en el momento de la grabación.