Tutorial de Excel VBA: cómo escribir código en una hoja de cálculo con Visual Basic

Programación

Introducción

Este es un tutorial sobre cómo escribir código en hojas de cálculo de 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: "Piense en un mundo sin Excel. Eso es simplemente imposible para mí". Bueno, tal vez el mundo no pueda pensar sin Excel.

  • En 1996, había más de 30 millones de usuarios de Microsoft Excel (fuente).
  • En la actualidad, se estima que hay 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 en 1996.

¡Somos una gran familia feliz!

En este tutorial, aprenderá sobre VBA y cómo escribir código en una hoja de cálculo de Excel usando Visual Basic.

Prerrequisitos

No necesitas experiencia previa en programación para comprender este tutorial. Sin embargo, necesitará:

  • Familiaridad de básico a intermedio con Microsoft Excel
  • Si desea seguir los ejemplos de VBA en este artículo, necesitará acceso a Microsoft Excel, preferiblemente la última versión (2019), pero Excel 2016 y Excel 2013 funcionarán bien.
  • Voluntad de probar cosas nuevas.

Objetivos de aprendizaje

En el transcurso de este artículo, aprenderá:

  1. Que es VBA
  2. Por qué usarías VBA
  3. Cómo configurar Excel para escribir VBA
  4. Cómo resolver algunos problemas del mundo real con VBA

Conceptos importantes

Aquí hay algunos conceptos importantes con los que debe estar familiarizado para comprender completamente este tutorial.

Objetos : Excel está 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 objetos en Excel.

Si no tiene ninguna experiencia con la programación orientada a objetos y este es un concepto completamente nuevo, ¡tómese un segundo para asimilarlo!

Procedimientos : un procedimiento es un fragmento de código VBA, escrito en el Editor de Visual Basic, que realiza una tarea. A veces, esto también se conoce como macro (más sobre macros a continuación). Hay dos tipos de procedimientos:

  • Subrutinas: un grupo de declaraciones de VBA que realiza una o más acciones
  • Funciones: un grupo de declaraciones de VBA que realiza una o más acciones y devuelve uno o más valores

Nota: puede tener funciones operando dentro de subrutinas. Verás más tarde.

Macros : si ha dedicado algún tiempo a aprender funciones más avanzadas de Excel, probablemente se haya encontrado con el concepto de una "macro". Los usuarios de Excel pueden grabar macros, que consisten en comandos de usuario / pulsaciones de teclas / clics, y reproducirlos a la velocidad del rayo para realizar tareas repetitivas. Las macros grabadas generan código VBA, que luego puede examinar. En realidad, es bastante divertido grabar una macro simple y luego mirar el código VBA.

Tenga en cuenta que a veces puede ser más fácil y rápido grabar una macro en lugar de codificar manualmente un procedimiento VBA.

Por ejemplo, quizás trabajes en gestión de proyectos. Una vez a la semana, debe convertir un informe sin procesar exportado de su sistema de gestión de proyectos en un informe limpio y bellamente formateado para el liderazgo. Debe dar formato a los nombres de los proyectos con exceso de presupuesto en texto rojo negrita. Puede grabar los cambios de formato como una macro y ejecutarlo siempre que necesite realizar el cambio.

¿Qué es VBA?

Visual Basic para Aplicaciones es un lenguaje de programación desarrollado por Microsoft. Cada programa de software de la suite de Microsoft Office se incluye con el lenguaje VBA sin costo adicional. VBA permite a los usuarios de Microsoft Office crear pequeños programas que operan dentro de los programas de software de Microsoft Office.

Piense en VBA como un horno de pizza dentro de un restaurante. Excel es el restaurante. La cocina viene con electrodomésticos comerciales estándar, como refrigeradores grandes, estufas y hornos ole 'normales; esas son todas las características estándar de Excel.

Pero, ¿y si quieres hacer pizza al horno de leña? No se puede hacer eso en un horno de cocción comercial estándar. VBA es el horno de pizza.

Mmm

¿Por qué usar VBA en Excel?

¡Porque la pizza de leña es la mejor!

Pero en serio.

Mucha gente pasa mucho tiempo en Excel como parte de su trabajo. El tiempo en Excel también se mueve de manera diferente. Dependiendo de las circunstancias, 10 minutos en Excel pueden parecer una eternidad si no puede hacer lo que necesita, o 10 horas pueden pasar muy rápido si todo va bien. Es entonces cuando deberías preguntarte, ¿por qué diablos paso 10 horas en Excel?

A veces, esos días son inevitables. Pero si pasa de 8 a 10 horas todos los días en Excel haciendo tareas repetitivas, repitiendo muchos de los mismos procesos, tratando de limpiar después de otros usuarios del archivo o incluso actualizando otros archivos después de que se realizan cambios en el archivo de Excel, un procedimiento de VBA podría ser la solución para usted.

Debería considerar usar VBA si necesita:

  • Automatizar tareas repetitivas
  • Cree formas sencillas para que los usuarios interactúen con sus hojas de cálculo
  • Manipula grandes cantidades de datos

Prepararse para escribir VBA en Excel

Pestaña de desarrollador

Para escribir VBA, deberá agregar la pestaña Desarrollador a la cinta, por lo que verá la cinta como esta.

Para agregar la pestaña Desarrollador a la cinta:

  1. En la pestaña Archivo, vaya a Opciones> Personalizar cinta.
  2. En Personalizar la cinta y en Pestañas principales, seleccione la casilla de verificación Desarrollador.

Después de mostrar la pestaña, la pestaña Desarrollador permanece visible, a menos que desactive la casilla de verificación o tenga que reinstalar Excel. Para obtener más información, consulte la documentación de ayuda de Microsoft.

Editor de VBA

Vaya a la pestaña Desarrollador y haga clic en el botón Visual Basic. Aparecerá una nueva ventana: este es el Editor de Visual Basic. Para los propósitos de este tutorial, solo necesita estar familiarizado con el panel Explorador de proyectos y el panel Propiedades de propiedad.

Ejemplos de Excel VBA

Primero, creemos un archivo para que juguemos.

  1. Abra un nuevo archivo de Excel
  2. Guárdelo como un libro de trabajo habilitado para macros (. Xlsm)
  3. Seleccione la pestaña Desarrollador
  4. Abra el editor de VBA

Vamos a rockear con algunos ejemplos sencillos para que pueda escribir código en una hoja de cálculo con Visual Basic.

Ejemplo n. ° 1: mostrar un mensaje cuando los usuarios abren el libro de Excel

En el Editor de VBA, seleccione Insertar -> Nuevo módulo

Escriba este código en la ventana del módulo (¡no pegue!):

Sub Auto_Open ()

MsgBox ("Bienvenido al libro XYZ")

End Sub

Guarde, cierre el libro y vuelva a abrirlo. Este diálogo debería aparecer.

Ta da!

¿Cómo está haciendo eso?

Dependiendo de su familiaridad con la programación, es posible que tenga algunas conjeturas. No es particularmente complejo, pero están sucediendo muchas cosas:

  • Sub (abreviatura de "Subrutina): recuerde desde el principio," un grupo de declaraciones de VBA que realiza una o más acciones ".
  • Auto_Open: esta es la subrutina específica. Ejecuta automáticamente su código cuando se abre el archivo de Excel; este es el evento que desencadena el procedimiento. Auto_Open solo se ejecutará cuando el libro se abra manualmente; no se ejecutará si el libro de trabajo se abre mediante el código de otro libro de trabajo (Workbook_Open lo hará, obtenga más información sobre la diferencia entre los dos).
  • Por defecto, el acceso a una subrutina es público. Esto significa que cualquier otro módulo puede utilizar esta subrutina. Todos los ejemplos de este tutorial serán subrutinas públicas. Si es necesario, puede declarar subrutinas como privadas. Esto puede ser necesario en algunas situaciones. Obtenga más información sobre los modificadores de acceso a subrutinas.
  • msgBox: esta es una función, un grupo de declaraciones de VBA que realiza una o más acciones y devuelve un valor. El valor devuelto 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 tenga un archivo muy importante al que se accede con poca frecuencia (digamos, una vez por trimestre), pero que se actualiza automáticamente a diario mediante otro procedimiento de VBA. Cuando se accede a él, lo hacen muchas personas en varios departamentos, en toda la empresa.

  • Problema: la mayoría de las veces, cuando los usuarios acceden al archivo, están confundidos acerca del propósito de este archivo (por qué existe), cómo se actualiza con tanta frecuencia, quién lo mantiene y cómo deben interactuar con él. Los nuevos empleados siempre tienen toneladas de preguntas, y debe responder estas preguntas una y otra vez.
  • Solución: cree un mensaje de usuario que contenga una respuesta concisa a cada una de estas preguntas frecuentes.

Ejemplos del mundo real

  • Utilice la función MsgBox para mostrar un mensaje cuando haya algún evento: el usuario cierra un libro de Excel, el usuario imprime, se agrega una nueva hoja al libro, etc.
  • Use la función MsgBox para mostrar un mensaje cuando un usuario necesita cumplir una condición antes de cerrar un libro de Excel
  • Utilice la función InputBox para obtener información del usuario

Ejemplo n. ° 2: permitir que el usuario ejecute otro procedimiento

En el Editor de VBA, seleccione Insertar -> Nuevo módulo

Escriba este código en la ventana del módulo (¡no pegue!):

Sub UserReportQuery ()

Atenuar UserInput tan largo

Respuesta tenue como entero

UserInput = vbYesNo

Answer = MsgBox ("¿Procesar el informe XYZ?", UserInput)

Si respuesta = vbYes, entonces ProcessReport

End Sub

Informe de subproceso ()

MsgBox ("Gracias por procesar el informe XYZ")

End Sub

Guarde y vuelva a la pestaña Desarrollador de Excel y seleccione la opción "Botón". Haga clic en una celda y asigne la macro UserReportQuery al botón.

Ahora haga clic en el botón. Este mensaje debería mostrar:

Haga clic en "sí" o presione Enter.

¡Una vez más, tada!

Tenga en cuenta que la subrutina secundaria, ProcessReport, podría ser cualquier cosa . Demostraré más posibilidades en el ejemplo n. ° 3. Pero primero...

¿Cómo está haciendo eso?

Este ejemplo se basa en el ejemplo anterior y tiene bastantes elementos nuevos. Repasemos las cosas nuevas:

  • Dim UserInput As Long: Dim es la abreviatura de "dimensión" y le permite declarar nombres de variables. En este caso, UserInput es el nombre de la variable y Long es el tipo de datos. En inglés simple, esta línea significa "Aquí hay una variable llamada" UserInput ", y es un tipo de variable Long"
  • Dim Answer As Integer: declara otra variable llamada "Respuesta", con un tipo de datos de Integer. Obtenga más información sobre los tipos de datos aquí.
  • UserInput = vbYesNo: asigna un valor a la variable. En este caso, vbYesNo, que muestra los botones Sí y No. Hay muchos tipos de botones, obtenga más información aquí.
  • Answer = MsgBox ("¿Procesar el informe XYZ?", UserInput): asigna el valor de la variable Answer para que sea una función MsgBox y la variable UserInput. Sí, una variable dentro de una variable.
  • If Answer = vbYes Then ProcessReport: esta es una "declaración If", una declaración condicional, que nos permite decir si x es verdadera, entonces haz y. En este caso, si el usuario ha seleccionado "Sí", ejecute la subrutina ProcessReport.

¿Cuándo podría usar esto?

Esto podría usarse de muchas, muchas formas. El valor y la versatilidad de esta funcionalidad está más definido por lo que hace la subrutina secundaria.

Por ejemplo, tal vez tenga un archivo que se utiliza para generar 3 informes semanales diferentes. Estos informes tienen un formato muy diferente.

  • Problema: cada vez que es necesario generar uno de estos informes, un usuario abre el archivo y cambia el formato y los gráficos; así sucesivamente. Este archivo se está editando extensamente al menos 3 veces por semana y toma al menos 30 minutos cada vez que se edita.
  • Solución: cree 1 botón por tipo de informe, que reformatea automáticamente los componentes necesarios de los informes y genera los gráficos necesarios.

Ejemplos del mundo real

  • Cree un cuadro de diálogo para que el usuario complete automáticamente cierta información en varias hojas
  • Utilice la función InputBox para obtener información del usuario, que luego se completa en varias hojas

Ejemplo n. ° 3: agregar números a un rango con un bucle para el siguiente

Los bucles for son muy útiles si necesita realizar tareas repetitivas en un rango específico de valores: matrices o rangos de celdas. En un lenguaje sencillo, un bucle dice "para cada x, haz y".

En el Editor de VBA, seleccione Insertar -> Nuevo módulo

Escriba este código en la ventana del módulo (¡no pegue!):

Sub LoopExample ()

Dim X como entero

Para X = 1 a 100

Rango ("A" y X). Valor = X

Siguiente X

End Sub

Guarde y vuelva a la pestaña Desarrollador de Excel y seleccione el botón Macros. Ejecute la macro LoopExample.

Esto debería suceder:

Etc, hasta la fila 100.

¿Cómo está haciendo eso?

  • Dim X As Integer: declara la variable X como un tipo de datos de Integer.
  • Para X = 1 a 100: este es el comienzo del ciclo For. En pocas palabras, le dice al ciclo que se repita hasta que X = 100. X es el contador . El ciclo seguirá ejecutándose hasta que X = 100, se ejecutará una última vez y luego se detendrá.
  • Range ("A" & X) .Value = X: esto declara el rango del bucle y qué poner en ese rango. Dado que X = 1 inicialmente, la primera celda será A1, momento en el que el bucle colocará X en esa celda.
  • Siguiente X: esto le dice al bucle que se ejecute nuevamente

¿Cuándo podría usar esto?

El bucle For-Next es una de las funcionalidades más poderosas de VBA; Existen numerosos casos de uso potenciales. Este es un ejemplo más complejo que requeriría múltiples capas de lógica, pero comunica el mundo de posibilidades en los bucles For-Next.

Tal vez tenga una lista de todos los productos vendidos en su panadería en la Columna A, el tipo de producto en la Columna B (pasteles, donas o muffins), el costo de los ingredientes en la Columna C y el costo promedio de mercado de cada tipo de producto en otra hoja.

Debe averiguar cuál debería ser el precio minorista de cada producto. Está pensando que debería ser el costo de los ingredientes más un 20%, pero también 1.2% por debajo del promedio del mercado si es posible. Un bucle For-Next le permitiría realizar este tipo de cálculo.

Ejemplos del mundo real

  • Use un bucle con una declaración if anidada para agregar valores específicos a una matriz separada solo si cumplen ciertas condiciones
  • Realizar cálculos matemáticos en cada valor en un rango, por ejemplo, calcular cargos adicionales y agregarlos al valor
  • Recorra cada carácter en una cadena y extraiga todos los números
  • Seleccionar aleatoriamente varios valores de una matriz

Conclusión

Ahora que hemos hablado de pizza y muffins y, oh, sí, cómo escribir código VBA en hojas de cálculo de Excel, hagamos una verificación de aprendizaje. Vea si puede responder estas preguntas.

  • ¿Qué es VBA?
  • ¿Cómo me configuro para comenzar a usar VBA en Excel?
  • ¿Por qué y cuándo usarías VBA?
  • ¿Cuáles son algunos de los problemas que podría resolver con VBA?

Si tiene una idea clara de cómo podría responder estas preguntas, entonces esto fue exitoso.

Ya sea que sea un usuario ocasional o un usuario avanzado, espero que este tutorial proporcione información útil sobre lo que se puede lograr con solo un poco de código en sus hojas de cálculo Excel.

¡Feliz codificación!

Recursos de aprendizaje

  • Programación Excel VBA para tontos, John Walkenbach
  • Introducción a VBA, documentación de Microsoft
  • Aprendiendo VBA en Excel, Lynda

Un poco sobre mí

Soy Chloe Tucker, una artista y desarrolladora en Portland, Oregon. Como exeducadora, busco continuamente la intersección entre el aprendizaje y la enseñanza, o la tecnología y el arte. Comuníquese conmigo en Twitter @_chloetucker y visite mi sitio web en chloe.dev.