Original article: Excel VBA Tutorial – How to Write Code in a Spreadsheet Using Visual Basic
Introducción
Este es un tutorial sobre como escribir código en hojas de cálculos Excel usando Visual Basic para Aplicaciones (VBA).
Excel es uno de los productos más populares de Microsoft. En 2016, el CEO de Microsoft dijo "Piensa en un mundo sin Excel. Eso es simplemente algo imposible para mí". Bueno, tal vez el mundo no pueda pensar sin Excel.
- En 1996, había sobre 30 millones de usuarios de Microsoft Excel (fuente).
- Hoy en día, hay un estimado de 750 millones de usuarios de Microsoft Excel. Eso es un poco más que la población de Europa y 25 veces más usuarios que los que había en 1996.
¡Somos una gran familia feliz!
En este tutorial aprenderás acerca de VBA y como escribir código en una hoja de cálculo Excel usando Visual Basic
Prerrequisitos
No necesitas experiencia previa programando para entender este tutorial. Sin embargo, necesitarás:
- Familiaridad básica a intermedia con Microsoft Excel
- Si quieres seguir los ejemplos de este artículo, necesitarás acceso a Microsoft Excel, preferentemente la última versión (2019) pero Excel 2016 y Excel 2013 funcionarán bien.
- Voluntad para probar nuevas cosas.
Objetivos de aprendizaje
a lo largo de este artículo aprenderás:
- Qué es VBA
- Porqué deberías usar VBA
- Como configurar Excel para escribir VBA
- Como resolver problemas reales con VBA
Conceptos Importantes
Aquí hay algunos conceptos importantes con los que deberías estar familiarizado para entender completamente este tutorial.
Objetos: Excel es orientado a objetos, lo que significa que todo es un objeto - la ventana de excel, el libro de trabajo, una hoja, un gráfico, una celda. VBA permite a los usuarios manipular y realizar acciones con los objetos en Excel.
Si no tienes experiencia con programación orientada a objetos y es un concepto completamente nuevo, tomate un segundo para dejar que asiente.
Procedimientos: un procedimiento es una porción de código VBA escrito en el editor Visual Basic que realiza una tarea, esto también se conoce como una macro (más sobre macros a continuación). Existen dos tipos de procedimientos:
- Subrutinas: un grupo de declaraciones VBA que realizan una o más acciones.
- Funciones: un grupo de declaraciones VBA que realizan una o más acciones y retornan uno o más valores
Nota: puedes tener funciones operando dentro de subrutinas. Lo verás más adelante.
Macros: Si has pasado algún tiempo aprendiendo funcionalidades más avanzadas de Excel, probablemente encontraste el concepto de "macro". Los usuarios de Excel pueden grabar macros, consistentes en comandos/pulsiones de teclas/clics, que luego se vuelven a ejecutar a la velocidad de la luz para realizar tareas repetitivas. Las macros grabadas generan código VBA, el cual podrás examinar. De hecho, es divertido grabar una macro simple y luego mirar el código VBA.
Por favor ten en cuenta que algunas veces será más sencillo y rápido grabar una macro que escribir a mano un procedimiento VBA.
Por ejemplo, tal vez trabajas en administración de proyectos. Una vez a la semana, tendrás que convertir un reporte sin formato exportado de tu sistema de gestión de proyectos en un reporte limpio y bellamente formateado para liderazgo. Necesitarás formatear los nombres de proyectos que están sobre el presupuesto en texto "negrita" y color rojo. Podrías grabar los cambios de formato como una macro y luego ejecutarla cada vez que necesites realizar un cambio.
¿Qué es VBA?
Visual Basic for Applications es un lenguaje de programación desarrollado por Microsoft. Cada programa de software en la suite Microsoft Office viene con el lenguaje VBA sin costo extra. VBA permite a los usuarios de Office crear pequeños programas que operan dentro del software Microsoft Office.
Piensa en VBA como un horno de pizza dentro de un restaurante. Excel es el restaurante. La cocina viene con electrodomésticos comerciales estandarizados, como refrigeradores grandes, estufas y viejos hornos regulares - estas son todas las características estándar de Excel.
Pero, ¿Qué pasa si quieres hacer pizza con leña? No podremos hacerlo en un horno comercial estándar. VBA es el horno de pizza.
Ñam!.
¿Por qué usar VBA en Excel?
¡Porque la pizza cocinada con leña es la mejor!
Pero en serio.
Mucha gente usa mucho tiempo en Excel como parte de sus trabajos. El tiempo en Excel se mueve diferente también. Dependiendo de las circunstancias, 10 minutos en Excel pueden parecer una eternidad si no eres capaz de hacer lo que necesitas, o 10 horas pueden pasar muy rápido si todo va muy rápido y bien. Es entonces cuando debes preguntarte, ¿por qué diablos estoy pasando 10 horas en Excel?
A veces, estos días son inevitables. Pero, si estás pasando 8-10 horas cada día en Excel haciendo tareas repetitivas, repitiendo mucho de los mismos procesos, tratando de limpiar después que otra persona use el archivo, o incluso actualizando otros archivos después de cambios realizados al archivo Excel, un procedimiento VBA podría ser la solución precisa para ti.
Deberías considerar usar VBA si necesitas:
- Automatizar tareas repetitivas
- Crear maneras fáciles para que los usuarios interactúen con tus hojas
- Manipular grandes cantidades de datos
Configurando el entorno para escribir VBA en Excel
Ficha desarrollador
Para escribir VBA, necesitas agregar la ficha Desarrollador a la Cinta de Opciones, así verás la Cinta de Opciones de la siguiente forma
Para agregar la ficha Desarrollador a la Cinta debes:
- En la ficha archivo, ir a Opciones > Personalizar Cinta de Opciones.
- Bajo "Personalizar Cinta de Opciones" y bajo "Pestañas principales", selecciona el check box Desarrollador
Después de mostrar la pestaña, la pestaña Desarrollador permanecerá visible, a menos que hallas limpiado la casilla de verificación o tengas que reinstalar Excel. Para más información, mira la documentación de ayuda Microsoft
Editor VBA
Navega a la pestaña Desarrollador, y luego haz clic en el botón Visual Basic. Una nueva ventana aparecerá - este es el editor Visual Basic. Para los propósitos de este tutorial, solo debes estar familiarizado con el panel Explorador de Proyecto y el panel Propiedades del Proyecto
Ejemplos de Excel VBA
Primero, creemos un archivo para jugar.
- Abre un nuevo archivo de Excel
- Grabalo como un libro habilitado para macros (maro-enabled workbook) (.xlsm)
- Selecciona la ficha Desarrollador
- Abre el editor VBA
Vamos a rock and roll con algunos ejemplos sencillos para que escribas código en una hoja de cálculo usando Visual Basic.
Ejemplo #1: Mostrar un mensaje cuando los usuarios abran el libro de Excel.
En el editor VBA, elige insertar -> Nuevo Módulo
Escribe este código en la ventana Módulo (no lo pegues!):
Sub Auto_open()
MsgBox("Bienvenido al libro de trabajo XYZ.")
End Sub
charaan!
¿Cómo hace esto?
Dependiendo de cuan familiarizado estés con la programación, tal vez intuyes algunas cosas. No es particularmente complejo, pero están pasando muchas cosas:
- Sub (abreviación de "Subrutina"): recuerda de lo antes descrito, "un grupo de declaraciones VBA que realizan una o más acciones".
- Auto_Open: esta es la subrutina específica. Esta ejecutará automáticamente tu cógigo cuando el archivo excel se abre - este es el evento que gatilla el procedimiento. Auto_Open sólo se ejecutará cuando el libro de trabajo se abre manualmente; y no se ejecutará si el libro de trabajo es abierto vía código desde otro libro de trabajo (Workbook_Open hará esto, aprende más sobre las diferencias entre estos dos)
- Por defecto, el acceso a una subrutina es público. Esto significa que cualquier otro módulo puede usar esta subrutina. Todos los ejemplos de este tutorial serán subrutinas públicas. Si lo necesitas, puedes declarar subrutinas de forma privada. Esto puede ser necesario en algunas ocasiones. Aprende más sobre modificadores de acceso a subrutinas
- MsgBox: esta es una función - un grupo de declaraciones que realiza una o más acciones y retorna un valor. El valor retornado es el mensaje "Bienvenido al libro de trabajo XYZ"
En resumen, esta es una subrutina simple que contiene una función
¿Cuándo podría usar esto?
Tal vez tienes un archivo muy importante, el cual tiene acceso de manera poco frecuente (digamos, una vez cada cuatrimestre), pero que es actualizado automáticamente por otro procedimiento VBA. Cuando se accede a este, es por muchas personas, en múltiples departamentos, alrededor de toda la compañía.
- Problema: La mayoría del tiempo cuando los usuarios acceden al archivo, ellos están confundidos respecto al propósito de este archivo (porqué existe), como es actualizado tan frecuentemente, quien lo mantiene, y cómo deberían interactuar con él. Los recién contratados siempre tienen un montón de preguntas, y tu tienes que contestar estas preguntas una y otra vez.
- Solución: Crear un mensaje a los usuarios que contenga respuestas específicas a cada una de estas preguntas frecuentes.
Ejemplos del Mundo Real
- Usar la función MsgBox para mostrar un mensaje cuando haya algún evento: el usuario cierra el libro de trabajo, el usuario imprime, una nueva hoja es agregada al libro de trabajo, etc.
- Usar la función MsgBox para mostar un mensaje cuando el usuario necesite llenar una condición antes de cerrar el libro de trabajo Excel
- Usar la función InputBox para obtener información del usuario
Ejemplo #2: Permitir al usuario ejecutar otro procedimiento
En el editor VBA selecciona Insertar -> Nuevo Módulo
Escribe este código en la ventana Módulo (no lo pegues!):
Sub UserReportQuery()
Dim UserInput As Long
Dim Answer As Integer
UserInput = vbYesNo
Answer = MsgBox("¿Desea procesar el reporte XYZ?",UserInput)
if Answer = vbYes Then ProcessReport
End Sub
Sub ProcessReport()
MsgBox("Gracias por procesar el reporte XYZ")
End Sub
Grábalo y navega de vuelta a la pestaña navegadora de Excel y selecciona la opción "Botón". Haz clic en una celda y asigna la macro UserReportQuery al botón.
Ahora haz clic en el botón. Este mensaje debería aparecer
Si haces clic en "Sí" o aprietas Enter.
¡¡Otra vez, tada!!
Pon atención a la subrutina secundaria "ProcessReport", podría ser cualquier cosa. Lo demostraré con más posibilidades en el ejemplo #3. Pero primero...
¿Cómo está haciendo esto?
Este ejemplo está construido sobre el ejemplo anterior con algunos elementos nuevos. Revisemos las cosas nuevas:
- Dim UserInput As Long: Dim es un diminutivo de "dimensión" y te permite declarar nombres de variables. En este caso, UserInput es el nombre de variable y Long es el tipo de dato. En español esta línea significa "Aquí hay una variable llamada "UserInput", y es de tipo Largo".
- Dim Answer as Integer: declara otra variable llamada "Answer", con el tipo de dato entero (integer). Aprende más sobre el tipo de datos aquí
- UserInput = vbYesNo: asigna un valor a la variable. En este caso, vbYesNo, el cual muestra los botones Sí y No. Hay muchos tipos de botones, aprende más sobre ellos aquí
- Answer = MsgBox("¿Desea procesar el reporte XYZ?", UserInput): asigna a la variable Answer la función MsgBox y la variable UserInput. Si, una variable dentro de una variable.
- If Answer = vbYes Then ProcessReport: esto es una "declaración if" una declaración condicional, la cual te permite decir si x es verdad, entonces hacer y. En este caso, si el usuario ha seleccionado "Sí", entonces ejecutar la subrutina ProcessReport.
¿Cuándo puedo usar esto?
Esto se puede usar de muchas, muchas maneras. El valor y versatilidad de esta funcionalidad está más definido por lo que hace la subrutina secundaria.
Por ejemplo, tal vez tienes un archivo que es usado para generar tres reportes semanales. Estos reportes están formateados de manera muy distinta.
- Problema: Cada vez que es necesario generar un reporte, un usuario abre el archivo y cambia los formatos y lo gráficos, así con cada usuario. Este archivo está siendo editado exhaustivamente a lo menos 3 veces a la semana, y toma a lo menos 30 minutos cada vez que es editado.
- Solución: Crear un botón por tipo de reporte, el cual automáticamente reformatea los componentes necesarios de los reportes y genera los gráficos necesarios.
Ejemplos del Mundo Real
- Crear un cuadro de dialogo para que un usuario pueda poblar una información determinada a través de múltiples hojas.
- Usar la función InputBox para obtener información del usuario, la cual luego será utilizada para poblar múltiples hojas
Ejemplo #3: Agregar números a un rango con un ciclo For-Next
Los ciclos for son muy útiles si necesitas realizar tareas repetitivas en un rango específico de valores, arreglos o rangos de celdas. En español, un ciclo dice "para cada x, haz y" ("for each x, doy" - en inglés)
En el editor VBA, seleccione Insertar -> Nuevo Módulo
Escribe el siguiente código en la ventana módulo (no lo pegues!!):
Sub LoopExample()
Dim X As Integer
For X = 1 to 100
Range("A"&X).Value = X
Next X
End Sub
Guarda y luego navega de vuelta a la pestaña desarrollador de Excel, agrega un botón macro. Corre la macro LoopExample.
Esto debería pasar:
Hasta llegar a la fila 100-ésima.
¿Cómo hace esto?
- Dim X As Integer: declara la variable X como un dato de tipo entero
- For X=1 To 100: este es el comienzo del ciclo For. En palabras simples, le dice al ciclo repite esto hasta que X = 100. X es el contador. El ciclo se seguirá ejecutando hasta X = 100, ejecuta una última vez y luego se detiene.
- Range("A"&X).Value = X: esto declara el rango del ciclo y qué poner en ese rango. Desde X = 1 inicialmente, la primera celda será A1, en cuyo lugar el ciclo pondrá X en esa celda.
- Next X: le dice al ciclo que se ejecute otra vez.
¿Cuándo puedo usar esto?
El ciclo For-Next es una de las funcionalidades más poderosas de VBA; existen muchos casos de uso potenciales. Este e un ejemplo más complejo que requerirá múltiples capas de lógica, pero comunica un mundo de posibilidades en los ciclos For-Next.
Tal vez tienes una lista de todos los productos vendidos en tu pastelería en la Columna A, el tipo de producto en la Columna B (queques, donas o muffins), el costo de los ingredientes en la Columna C, y el precio promedio de mercado de cada tipo de producto en otra hoja.
Necesitas saber cuál debería ser el precio de venta de cada producto. Piensas que debería ser el costo de los ingredientes más un 20%, pero también 1.2% bajo el precio promedio de mercado en caso de ser posible. Un ciclo For-Next podrían permitirte hacer este tipo de cálculos.
Ejemplos del Mundo Real
- Usa un ciclo con una declaración if anidada para agregar valores específicos a un arreglo separado sólo si se cumplen ciertas condiciones
- Realizar cálculos matematicos en cada valor de un rango, i.e. calcular cargos adicionales y agregarlos al valor
- iterar sobre cada caracter en un texto (string) y extraer todos los números
- Elegir aleatoriamente números de valores en un array
Conclusión
Ahora que hemos hablado acerca de pizza y mufins y claro, como escribir código VBA en hojas de Excel, hagamos un chequeo del aprendizaje. Revisa si puedes responder estas preguntas.
- ¿Qué es VBA?
- ¿Como configurar el enterno para comenzar a usar VBA en Excel?
- ¿Como y cuando debes usar VBA?
- ¿Cuales son algunos problemas que puedes resolver con VBA?
Si tienes una idea clara de cómo responder estas preguntas, entonces esto fue un éxito.
Ya sea que eres un usuario ocasional o un usuario avanzado, espero que este tutorial provea información útil acerca de lo que podemos conseguir con un poco de código en tus hojas de Excel.
¡Feliz código!
Recursos de aprendizaje
- Excel VBA Programming for Dummies, John Walkenbach
- Introducción a VBA en Office
- Learning VBA in Excel, Lynda
Un poco de la autora original
Chloe Tucker, artísta y desarrolladora en Portland, Oregon. Como educadora, estoy continuamente buscando la intersección entre aprender y enseñar, o entre tecnología y arte. Me puedes encontrar en Twitter @_chloetucker y revisar mi sitio web chloe.dev