Programación VBA básica que todo usuario de Excel debería conocer

Los usuarios de Excel no programan, y a los programadores no les interesa Excel así que el potencial de VBA sigue siendo una tierra baldía entre estos dos mundos para muchos usuarios.

Este artículo te permitirá escribir tus propios programas y funciones de Excel personalizadas con independencia de tu nivel actual.

Photo by Oleg Magni from Pexels

Escribo esto con un enfoque muy directo, por que aunque hay muchos blogs de VBA buenísimos, no consiguen romper la barrera para que las personas que no saben programar puedan empezar a utilizar este lenguaje.

Mi objetivo es ayudar a tantas personas como sea posible a mejorar la forma en que trabajan con unos pocos conocimientos de VBA.

Si ya sabes lo que es VBA, y si además ya tienes algunas nociones básicas de programación entonces te sugiero que te saltes la introducción y vayas directo al apartado Pongámonos serios que encontrarás más abajo. Si no es ese tu caso entonces deja que te explique de que se trata esto.

Las siglas VBA son por Visual Basic for Applications, es un lenguaje de programación incluido en el paquete de Microsoft Office.

VBA es perfecto para realizar tareas repetitivas que no añaden valor. Por ejemplo, el casico libro excel tipo actualizar-copiar-pegar-repetir. Analizar informes añade valor, pero construirlos es una pérdida de tiempo así que en lugar de hacerlo tú mismo deja que el ordenador trabaje por ti.

Con un puñado de programas VBA puedes ahorrar una cantidad enorme de tiempo.

Además, estos programas están contenidos en los propios archivos Excel, así que otra persona sin conocimientos técnicos puede beneficiarse del código que tú hayas escrito anteriormente.

También puedes utilizarlo para realizar tareas difíciles que de otra forma serían muy tediosas o simplemente imposibles de hacer solo con las funciones que vienen por defecto en Excel. Como realizar una acción en función del color de fondo de una celda, por ejemplo.

Pero antes de ponernos manos a la obra, hay bastante confusión en cuanto a lo que es una macro y lo que es un programa VBA, así que vamos a dejarlo claro antes de empezar.

¿VBA y macros son lo mismo?

Las macros son trozos de código VBA generados automáticamente vía Excel. Es realmente fácil crear una macro, solo necesitas ir a Programador/Grabar Macro ( o Desarrollador/Grabar Macro según tu versión de Excel) y realizar las acciones que quieres que repita más tarde de forma automática.

Cuando utilizas macros no hay necesidad de ver el código, mucho menos editarlo, pero en el fondo es eso exactamente lo que está haciendo Excel. En este video puedes ver como se genera el código mientras grabamos una macro.

Incluso para usuarios de VBA experimentados, de vez en cuando es útil grabar una macro por que te da pistas sobre como escribir un código específico sin tener que pasarse una tarde buscando en google. Como contrapartida, el código generado de esta forma rara vez es la mejor opción.

Por ejemplo, en el video anterior Excel escribe 7 líneas para cambiar el color de una celda, pero con una sola línea con el código ActiveCell.Interior.Color=65535 habría sido más que suficiente.

Dejemos de lado las macros y vamos a centrarnos en programación VBA pura y dura.

Parte 1: Escribir un programa

Photo by Annie Spratt on Unsplash

Como dije antes, mi objetivo es que puedas programar programas VBA sencillos, así que perdona si voy al grano y me salto algunos detalles técnicos.

Si tienes algún problema siguiendo estos pasos solo deja un comentario, estaré encantado de ayudarte.

Lo primero que haremos es crear un módulo, te lo puedes imaginar como un blog de notas donde escribirás código VBA. Sitúate en cualquier hoja Excel y pulsa Alt+F11 o ve a Desarrollador/Visual Basic, con esto abrirás el Editor de VBA.

A la izquierda deberías ver una ventana con el nombre Project — VBA que contiene una estructura tipo árbol con el nombre de todos los libros que tienes abiertos en este momento, sus hojas, y otras cosas de las que hablaremos más adelante. Si no puedes ver esta ventana ve a View/Project Explorer.

Ahora haz clic-derecho en cualquier hoja y pulsa Insert/Module (ver gif a continuación).

Hola Mundo

Si ya has programado una función Hello World antes quizás prefieras saltarte esta parte, pero si no es así esta es una forma divertida de escribir tu primer programa.

Copia esto en tu módulo, mejor si lo escribes tú misma, y luego pulsa F5 o Run/Run Sub.

Sub HelloWorld()
MsgBox ("Hello World")
End Sub

Debería quedarte algo tal que así:

Tada!!! Felicidades por tu primer programa. Vamos a darle un vistazo paso a paso. La primera línea del bloque sub le está diciendo a Excel aquí empieza un programa llamado “HelloWorld”. La línea End Sub señala donde termina el programa. Escribirás tu programa entre estas dos líneas.

Sub HelloWorld()
End Sub

Lo siguiente es llamar a la función Msgbox(). Esta función puede hacer mucho más que esto, pero a veces está bien simplificar.

Importante tener en cuenta que el texto debe ir entre comillas, de lo contrario el editor pensará que es el nombre de una variable y nos dará error.

Sub HelloWorld()
Msgbox("Hello World")
End Sub

Truco 1: puedes dejar comentarios entre las líneas de código poniendo un apostrofe delante. En tu editor VBA los verás de color verde.

'Este es el clásico programa "Hola Mundo"
Sub HelloWorld() 'En esta línea empieza el programa
MsgBox (message) 'Esta función abre una ventana con el texto
End Sub 'Esta es la última línea del programa

Truco 2: También puedes utilizar tabulaciones, espacios y dejar líneas en blanco para hacerlo más fácil de leer, ni estos elementos ni los comentarios afectan al programa en absoluto.

'Este es el clásico programa "Hola Mundo"

Sub HelloWorld()

MsgBox (message)
    
End Sub

Pongámonos serios

¿Qué tal si contamos el número de celdas de color dentro de un rango? Como en el ejercicio anterior esto tiene un propósito pedagógico, pero trata de pensar en el tipo de tareas en las que podrías aplicarlo.

Primero de todo, tengo una hoja con este aspecto:

Tal como hice antes, te enseñaré el programa terminado para que puedas hacer copia&pega y ver que tal funciona en tu ordenador. Luego te lo explicaré línea a línea.

Aquí va:

Sub Colored()
Dim R As Range
Dim Count As Integer
Set R = Range("A1:H15")
Count = 0
For Each cell In R     
    If Not cell.Interior.ColorIndex = xlNone Then Count = Count + 1
   
Next
MsgBox (Count)
End Sub

Cuando lo ejecutes debería aparecer un mensaje como este:

Funcionará para cualquier color y cualquier número de celdas coloreadas dentro del rango A1:H15. Pero no me creas, compruébalo por ti mismo coloreando diferentes celdas con diferentes colores.

¿Quieres saber cómo funciona? Vamos por partes:

Dim R as Range
Dim Count as Integer

Una forma sana de empezar un programa es declarando las variables que vamos a utilizar, para esto utilizamos el comando Dim.

Escribimos Dim luego el nombre que le queremos dar a esa variable, y a continuación as y el tipo de variable.

Dim <<nombre>> as <<tipo de variable>>

Hemos declarado dos variables, un Range y un Integer. Te puedes imaginar una variable como un contenedor para un tipo específico de dato. Por ejemplo, hemos creado un integer, que es simplemente un número entero, y un Range. Un Range es un rango de celdas, son objetos realmente interesantes por que te dan acceso a las propiedades de sus celdas, cosas como su valor, su color, formato, etc. Luego entramos en ese tema.

Siguiente paso, les asignamos valores a las variables:

Set R=Range("A1:H15")

Count=0

Fíjate que para rellenar una variable Range utilizamos la función Set, pero no es necesario utilizar esta función con variables más imples como integer, string (cadena de texto), float (número con decimales), double (igual que float pero con más precisión) o date (fecha) .

Actuando sobre cada celda de un rango

Para hacer esto utilizaremos la función for. En caso de que no estés familiarizado con esta función, los for recorren un conjunto de elementos realizando una acción en cada uno de ellos.

En su expresión más simple se pueden utilizar para repetir una acción. Por ejemplo, si pruebas esto:

Sub Count1to10()
For i=1 to 10
    msgbox(i)
Next
End Sub

El resultado será algo como esto:

¿Le vas cogiendo el truquillo? Ahora vamos a fijarnos en el código que hemos utilizado al principio:

For Each cell In R 
     
   'Haz algo
    
Next

Este código en lugar de contar de 1 a 10 hace algo más útil, recorre todas las celdas de un rango dado realizando una acción en cada una de ellas.

Ahora vamos a dar un vistazo dentro del for.

If, la función SI de VBA.

Como usuaria experimentada de Excel ya debes estar familiarizada con la función SI. En caso contrario, funciona así:

La condición es un booleano, lo que significa que el resultado solo puede ser Verdadero o Falso.

El código VBA equivalente sería:

If (2+2=3) then
    msgbox("Sip")
else
    msgbox("Nop")
End If

En otras palabras:

If [CONDICIÓN BOLEANA] then
    [HAZ ESTO SI ES CIERTO]
else
    [HAZ ESTO SI ES FALSO]
End If

En nuestro ejemplo, solo estamos interesados en la parte verdadera. Si una celda tiene color entonces la contamos, si no, no nos importa. Cuando solo te interesa la parte positiva puedes escribir el código en una sola línea:

If [ CONDICIÓN BOLEANA ] then [ HAZ ESTO ]

Volviendo a nuestro código:

If (Not cell.Interior.ColorIndex = xlNone) Then (Count = Count + 1)

La condición es (Not Cell.Interior.ColorIndex=xlNone), en lenguaje natural sería algo como (esta no es una celda incolora). De esta forma si la celda tiene algún color entonces el programa sumará +1 a la variable llamada Count. Finalmente, cuando el for haya recorrido todas las celdas habremos sumado tantos +1 como celdas de color. Así es como las cuenta.

Propiedades de celdas en VBA

No me quiero poner muy técnico, pero no está de más decir que VBA es un lenguaje orientado a objetos. Los objetos VBA tienen propiedades y métodos. Una propiedad es algo como color, tamaño, columna… Un método es una operación como copiar o guardar, por ejemplo.

A cada tipo de objeto se le llama Clase y cada clase tiene diferentes propiedades y métodos. Por ejemplo, para la clase Libro tiene sentido el método Guardar, pero no lo tiene para la clase Celda ya que no puedes guardar una sola celda.

Cuando escribes un punto al lado del nombre de una variable el editor te mostrará todas las propiedades y métodos disponibles.

En el ejemplo de contar colores hemos utilizado la propiedad Interior.ColorIndex. Una forma sencilla de saber el nombre de una propiedad es grabar un macro.

La Clase Celda tiene decenas de propiedades. Algunas de las más útiles son:

• .Row: número de fila
• .Column: número de columna. Insisto, no letra, número. La columna A sería 1, la B 2 y así sucesivamente.
• .Value: su valor, ya sea el resultado de una formula o simplemente un valor que hemos introducido manualmente (normalmente un número, un texto o un boleano).

Existe también una propiedad particularmente interesante llamada .Offset( ).

Esta propiedad tiene dos argumentos .Offset(filas,columnas). Por ejemplo, Range(“A1”).Offset(1,2) corresponde a la celda C2, es decir una fila por debajo de A1 y dos columnas hacia la derecha. Con esta propiedad puedes actuar sobre una celda dependiendo de su posición con respecto a otra que ya has declarado como variable.

Échale un ojo a este ejemplo. Tomate un momento para leer el código e interiorizarlo, presta atención a lo que está haciendo.

¿Ves como se combinan las propiedades Offset y Value?

Por cierto, para ejecutar un programa línea a línea como hice en el gif simplemente hay que ir pulsando F8.

Cómo parar un for de golpe

A veces, mientras ejecutas un for no necesitas pasar por todas las celdas. Por ejemplo, imagina que solo queremos saber si hay alguna celda coloreada. Como no nos importa cuantas hay no hace falta revisarlas todas, podemos parar cuando encontremos la primera. Esto se hace con el comando Exit For.
Aquí tienes un ejemplo:

Sub Colored_variant()
Dim R As Range
Set R = Range("A1:H15")
For Each Cell In R    
   
   cell.select '<- con este comando ponemos el cursor sobre la celda
   
   If Not Cell.Interior.ColorIndex = xlNone Then
   
    MsgBox ("one colored cell found")
    Exit For '<--- Skips the remaining cells
   
   End If
Next
End Sub

Puedes copiar este código en tu editor de VBA y ejecutarlo paso a paso pulsando F8 repetidamente. Sitúa las ventanas de manera que puedas ver la hoja en paralelo. He incorporado el comando cell.Select para que puedas ver sobre que celda está en cada momento.

Parte 2: Diferentes formas de ejecutar un programa

Pulsar el botón “Play” (F5) en el editor VBA es la manera más sencilla de ejecutar un programa, pero necesitas saber unos mínimos de VBA para hacerlo de esta forma.
¿Qué pasa si tu programa es para alguien que no sabe nada de VBA?
¿Y si quieres que tu código se ejecute automáticamente en determinados momentos? Como por ejemplo al abrir un libro determinado.

Ejecutar un programa a demanda

La forma más fácil de hacer esto es asignar el programa a una forma. Simplemente pulsa el botón derecho del ratón sobre cualquier forma y elige la opción Asignar Macro.

Una forma más profesional de hacer esto es con controles Active X. Hay diferentes tipos de controles, pero en este artículo vamos a ver solo los botones.

Ve a Desarrollador/Controles Active X y agrega un nuevo botón.

Haz clic derecho sobre el botón y elige Ver Código, aquí puedes programar exactamente lo que quieres que haga el botón al pulsarlo.
Fíjate que este código se crea dentro del código de la hoja.

Puedes llamar a programas de otros módulos y hojas con la función Call.

Private Sub CommandButton1_Click()
Call HelloWorld()
End Sub

Ejecutar en función de Eventos

En este contexto un evento es una acción. En el ejemplo anterior hemos visto el evento Button_Click. En aquel caso el evento era hacer click sobre un botón en particular, no es algo muy original, pero hay un montón de eventos diferentes disponibles. Veamos algunos ejemplos:

1. Activar una hoja determinada

Para programar un evento sobre una hoja en concreto simplemente abre su código haciendo doble clic en el editor de proyectos VBA y elije entre los diferentes eventos disponibles. Si conoces el comando también puedes escribir el código directamente.

Esto sería un ejemplo de lo que puedes hacer.

Private Sub Worksheet_Activate()
MsgBox ("Welcome to sheet number 1")
End Sub

Se vería así:

Recuerda que también puedes llamar a funciones de otros módulos con el comando Call.

2. Abriendo un libro

Igual que con las hojas, existen algunos eventos para actuar sobre libros enteros. Para programar uno de estos haremos doble clic en ThisWorkbook en el explorador de proyectos.

Imagina que tienes un montón de tablas dinámicas en el mismo documento, y quieres asegurarte de que no olvidas actualizarlas todas. No te hagas líos, deja que el ordenador trabaje por ti. Puedes automatizar esto con solo 3 líneas de código:

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub

Prueba diferentes opciones con los selectores para descubrir más eventos interesantes, cuanto más experimentes más aprenderás.

Parte 3: Funciones personalizadas

Photo by Annie Spratt

¿Te gustaría crear tus propias funciones? No hablo de funciones VBA, sino de funciones que puedes usar directamente en tus hojas de cálculo como =SUMA( ) o =BUSCARV( ). Entonces tengo buenas noticias, esto es mucho más fácil de lo que parece.

¿Por ejemplo, alguna vez has tenido una hoja llena de ratios tipo A/B? Puede ser muy molesto cuando B es 0 y empieza a devolver errores #DIV0.
Ya se que podemos hacer simplemente algo como:

=SI(B5=0;0;A5/B5)

¿Pero no sería más fácil tener una fórmula que haga exactamente esto pero sin perder tiempo anidando funciones cada vez?

Crea un módulo nuevo y prueba de pegarle esto:

Function Div(A,B) as double
If B=0 then 
    Div=0
Else
    Div=A/B
End If
End Function

¡Ya está! No necesitas hacer nada más, ya tienes la nueva función disponible.
Así es como funciona:

Bonus: ¿Que hacer ahora?

Photo by Danielle MacInnes on Unsplash

Estas son las bases para empezar a hacer algunos programas VBA que valgan la pena, pero es solo la punta del iceberg.

Puedes hacer mucho más que esto, prácticamente todo es posible así que trata de pensar más allá de lo que puede hacer una simple hoja de cálculo.

Algunos consejos:

• La documentación de Office on-line está muy bien para empezar.
• Hay decenas de blogs cubriendo diferentes temas. A no ser que estés haciendo algo realmente raro lo más probable es que si lo buscas en google encuentres por lo menos dos o tres blogueros cubriendo el tema.
• Si es que realmente estás haciendo algo raro.. Primero de todo ¡Felicidades!¡Así es como se aprende! Pero si necesitas algo de ayuda puedes ir a www.todoexpertos.com y hacer una pregunta.

Y por supuesto, si puedo ayudarte con algún programa, o si te gustaría que escriba sobre algun tema en particular de VBA, Excel o Access solo dímelo en los comentarios o envíame un privado a linkedIn si lo prefieres.

¡¡Estaré encantado de ayudar!!

Espero que te haya parecido últil.
Gracias por leer y que tengas un día estupendo.

Este artículo es una traducción de Essential VBA coding every Excel user should know.