Para sacar el máximo partido a Excel y a VBA, es necesario saber cómo utilizar los bucles de forma eficiente.
En VBA, los bucles permiten recorrer un conjunto de objetos/valores y analizarlo uno a uno. También puede realizar tareas específicas para cada bucle.
Aquí tiene un ejemplo sencillo de uso de bucles VBA en Excel.
Suponga que tiene un conjunto de datos y quiere resaltar todas las celdas de las filas pares. Puede utilizar un bucle VBA para recorrer el rango y analizar cada número de fila de celda. Si resulta ser par, le das un color, si no, lo dejas como está.
Ahora bien, esto, por supuesto, es muy simple de bucle en Excel VBA (y también puedes hacerlo usando el formato condicional).
En la vida real, puedes hacer mucho más con los bucles VBA en Excel que pueden ayudarte a automatizar tareas.
Aquí hay algunos ejemplos más prácticos en los que los bucles VBA pueden ser útiles:
- Realizar un bucle a través de un rango de celdas y analizar cada celda (resaltar las celdas con un texto específico en ella).
- Realizar un bucle a través de todas las hojas de trabajo y hacer algo con cada una (como protegerla/desprotegerla).
- Realizar un bucle a través de todos los libros de trabajo abiertos (y guardar cada libro de trabajo o cerrar todos excepto el libro de trabajo activo).
- Realizar un bucle a través de todos los caracteres de una celda (y extraer la parte numérica de una cadena).
- Realizar un bucle a través de todos los valores un array.
- Realizar un bucle a través de todos los gráficos/objetos (y dar un borde o cambiar el color de fondo).
Ahora bien, para utilizar mejor los bucles en Excel VBA, es necesario conocer los diferentes tipos que existen y la sintaxis correcta de cada uno.
En este tutorial, mostraré diferentes tipos de bucles de Excel VBA y cubriré algunos ejemplos para cada bucle
Si estás interesado en aprender VBA de la manera más fácil, echa un vistazo a mi Formación VBA Online de Excel.
Este tutorial cubre:
El bucle ‘For Next’ te permite pasar por un bloque de código por el número de veces especificado.
Por ejemplo, si te pido que sumes los enteros del 1 al 10 manualmente, sumarías los dos primeros números, luego añadirías el tercer número al resultado, luego añadirías el cuarto número al resultado, como así.
¿No es así?
La misma lógica se utiliza en el bucle For Next de VBA.
Especificas cuántas veces quieres que se ejecute el bucle y también especificas qué quieres que haga el código cada vez que se ejecute el bucle.
A continuación se muestra la sintaxis del bucle For Next:
For Counter = Start To End Next
En el bucle For Next, puede utilizar un Contador (o cualquier variable) que se utilizará para ejecutar el bucle. Este contador le permite ejecutar este bucle durante un número requerido de veces.
Por ejemplo, si quiero sumar los 10 primeros enteros positivos, entonces el valor de mi Contador sería de 1 a 10.
Veamos algunos ejemplos para entender mejor cómo funciona el bucle For Next.
Ejemplo 1 – Suma de los 10 primeros enteros positivos
A continuación se muestra el código que sumará los 10 primeros enteros positivos utilizando un bucle For Next.
A continuación, mostrará un cuadro de mensaje mostrando la suma de estos números.
Sub AddNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 1 To 10Total = Total + CountNext CountMsgBox TotalEnd Sub
En este código, el valor de Total se pone a 0 antes de entrar en el bucle For Next.
Una vez que entra en el bucle, mantiene el valor total después de cada bucle. Así, después del primer bucle, cuando Contador es 1, el valor de Total se convierte en 1, y después del segundo bucle se convierte en 3 (1+2), y así sucesivamente.
Y finalmente, cuando el bucle termina, la variable Total tiene la suma de los 10 primeros enteros positivos.
A continuación, un MsgBox simplemente muestra el resultado en un cuadro de mensaje.
Ejemplo 2 – Suma de los 5 primeros enteros positivos pares
Para sumar los cinco primeros enteros positivos pares (es decir, 2,4,6,8 y 10), se necesita un código similar con una condición para que sólo se consideren los números pares y se ignoren los impares.
Aquí tienes un código que lo hará:
Sub AddEvenNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 2 To 10 Step 2Total = Total + CountNext CountMsgBox TotalEnd Sub
Nota que empezamos el valor de Count desde 2 y también usamos ‘Step 2’.
Cuando usas ‘Step 2’, le dice al código que incremente el valor de ‘Count’ en 2 cada vez que se ejecuta el bucle.
Así, el valor de Count comienza en 2 y luego se convierte en 4, 6, 8 y 10 a medida que se produce el bucle.
NOTA: Otra forma de hacer esto podría ser ejecutar el bucle desde 1 hasta 10 y dentro del bucle comprobar si el número es par o impar. Sin embargo, utilizar Step, en este caso, es una forma más eficiente ya que no requiere que el bucle se ejecute 10 veces, sino sólo 5 veces.
El valor de Step también puede ser negativo. En tal caso, el Contador comienza en un valor más alto y sigue disminuyendo por el valor de Paso especificado.
Ejemplo 3 – Introducir el número de serie en las celdas seleccionadas
También puede utilizar el bucle For Next para recorrer una colección de objetos (como celdas u hojas de trabajo o libros),
Aquí tiene un ejemplo que introduce rápidamente los números de serie en todas las celdas seleccionadas.
Sub EnterSerialNumber()Dim Rng As RangeDim Counter As IntegerDim RowCount As IntegerSet Rng = SelectionRowCount = Rng.Rows.CountFor Counter = 1 To RowCountActiveCell.Offset(Counter - 1, 0).Value = CounterNext CounterEnd Sub
El código anterior primero cuenta el número de filas seleccionadas y luego asigna este valor a la variable RowCount. A continuación, ejecutamos el bucle desde ‘1 hasta RowCount’.
También hay que tener en cuenta que como la selección puede ser cualquier número de filas, hemos establecido la variable Rng a Selection (con la línea ‘Set Rng = Selection’). Ahora podemos utilizar la variable ‘Rng’ para referirnos a la selección en nuestro código.
Ejemplo 4 – Proteger todas las hojas de trabajo del libro activo
Puede utilizar el bucle ‘For Next’ para recorrer todas las hojas de trabajo del libro activo, y proteger (o desproteger) cada una de las hojas de trabajo.
A continuación se muestra el código que hará esto:
Sub ProtectWorksheets()Dim i As IntegerFor i = 1 To ActiveWorkbook.Worksheets.CountWorksheets(i).ProtectNext iEnd Sub
El código anterior cuenta el número de hojas utilizando ActiveWorkbook.Worksheets.Count. Esto le dice a VBA cuántas veces debe ejecutarse el bucle.
En cada instancia, hace referencia al Iº libro de trabajo (utilizando Worksheets(i)) y lo protege.
Puede utilizar este mismo código para Desproteger hojas de trabajo también. Sólo tiene que cambiar la línea Hojas de trabajo(i).Proteger por Hojas de trabajo(i).Desproteger.
Bucles ‘For Next’ anidados
Puede utilizar bucles ‘For Next’ anidados para conseguir una automatización más compleja en Excel. Un bucle ‘For Next’ anidado significaría que hay un bucle ‘For Next’ dentro de un bucle ‘For Next’.
Déjeme mostrarle cómo usar esto usando un ejemplo.
Supongamos que tengo 5 libros abiertos en mi sistema y quiero proteger todas las hojas de trabajo en todos estos libros.
A continuación se muestra el código que hará esto:
Sub ProtectWorksheets()Dim i As IntegerDim j As IntegerFor i = 1 To Workbooks.CountFor j = 1 To Workbooks(i).Worksheets.CountWorkbooks(i).Worksheets(j).ProtectNext jNext iEnd Sub
Lo anterior es un bucle For Next anidado ya que hemos utilizado un bucle For Next dentro de otro.
Estados ‘EXIT For’ en los bucles For Next
La sentencia ‘Exit For’ te permite salir del bucle ‘For Next’ completamente.
Puedes usarlo en los casos en los que quieras que el bucle For Next termine cuando se cumpla una determinada condición.
Tomemos un ejemplo en el que tienes un conjunto de números en la columna A y quieres resaltar todos los números negativos en fuente roja. En este caso, tenemos que analizar cada celda por su valor y luego cambiar el color de la fuente en consecuencia.
Pero para hacer el código más eficiente, podemos comprobar primero si hay valores negativos en la lista o no. Si no hay valores negativos, podemos utilizar la sentencia Exit For para simplemente salir del código.
A continuación se muestra el código que hace esto:
Sub HghlightNegative()Dim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))Counter = Rng.CountFor i = 1 To CounterIf WorksheetFunction.Min(Rng) >= 0 Then Exit ForIf Rng(i).Value < 0 Then Rng(i).Font.Color = vbRedNext iEnd Sub
Cuando se utiliza la sentencia ‘Exit For’ dentro de un bucle anidado ‘For Next’, saldrá del bucle en el que se ejecuta y pasará a ejecutar la siguiente línea del código después del bucle For Next.
Por ejemplo, en el siguiente código, la sentencia ‘Exit For’ le hará salir del bucle interior, pero el bucle exterior seguiría funcionando.
Sub SampleCode()For i = 1 To 10For j = 1 to 10Exit ForNext JNext iEnd Sub
Bucle Do While
Un bucle ‘Do While’ le permite comprobar una condición y ejecutar el bucle mientras esa condición se cumple (o es TRUE).
Hay dos tipos de sintaxis en el bucle Do While.
Do Loop
y
DoLoop
La diferencia entre estas dos es que en la primera, se comprueba primero la condición While antes de ejecutar cualquier bloque de código, y en el segundo caso, se ejecuta primero el bloque de código y luego se comprueba la condición While.
Esto significa que si la condición While es False es ambos casos, el código seguirá ejecutándose al menos una vez en el segundo caso (ya que la condición ‘While’ se comprueba después de que el código se haya ejecutado una vez).
Ahora vamos a ver algunos ejemplos de uso de los bucles Do While en VBA.
Ejemplo 1 – Sumar los 10 primeros enteros positivos usando VBA
Suponga que quiere sumar los diez primeros enteros positivos usando el bucle Do While en VBA.
Para ello, puede usar el bucle Do While hasta que el siguiente número sea menor o igual a 10. Tan pronto como el número sea mayor que 1o, su bucle se detendría.
Aquí está el código VBA que ejecutará este bucle Do While y el mostrará el resultado en un cuadro de mensaje.
Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do While i <= 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub
El bucle anterior continúa trabajando hasta que el valor de ‘i’ se convierte en 11. En cuanto se convierte en 11, el bucle finaliza (ya que la condición While se convierte en False).
Dentro del bucle, hemos utilizado una variable Result que contiene el valor final Una vez finalizado el bucle, un cuadro de mensaje muestra el valor de la variable ‘Result’.
Ejemplo 2 – Introducir las fechas del mes actual
Digamos que quiere introducir todas las fechas del mes actual en una columna de la hoja de trabajo.
Puede hacerlo utilizando el siguiente código de bucle Do While:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub
El código anterior introduciría todas las fechas en la primera columna de la hoja de trabajo (empezando por A1). Los bucles continúan hasta que el valor del mes de la variable ‘CMDate’ coincida con el del mes actual.
Sentencia Exit Do
Puede utilizar la sentencia Exit Do para salir del bucle. Tan pronto como el código ejecuta la línea ‘Exit Do’, sale del bucle Do While y pasa el control a la siguiente línea justo después del bucle.
Por ejemplo, si quiere introducir sólo las 10 primeras fechas, entonces puede salir del bucle tan pronto como se introduzcan las 10 primeras fechas.
El siguiente código hará esto:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub
En el código anterior, la sentencia IF se utiliza para comprobar si el valor de i es mayor que 10 o no. Tan pronto como el valor de ‘i’ se convierte en 10, se ejecuta la sentencia Exit Do y el bucle finaliza.
Bucle Do Until
Los bucles ‘Do Until’ son muy parecidos a los bucles ‘Do While’.
En ‘Do While’, el bucle se ejecuta hasta que se cumpla la condición dada, mientras que en ‘Do Until’, hace un bucle hasta que se cumpla la condición especificada.
Hay dos tipos de sintaxis en el bucle Do Until.
Do Loop
y
DoLoop
La diferencia entre estos dos es que en el primero, se comprueba primero la condición Until antes de ejecutar cualquier bloque de código, y en el segundo caso, se ejecuta primero el bloque de código y luego se comprueba la condición Until.
Esto significa que si la condición Until es TRUE es ambos casos, el código se seguirá ejecutando al menos una vez en el segundo caso (ya que la condición ‘Until’ se comprueba después de que el código se haya ejecutado una vez).
Ahora vamos a ver algunos ejemplos de uso de los bucles Do Until en VBA.
Nota: Todos los ejemplos de Do Until son los mismos que los de Do While. Estos han sido modificados para mostrarle cómo funciona el bucle Do Until.
Ejemplo 1 – Sumar los 10 primeros enteros positivos utilizando VBA
Suponga que quiere sumar los diez primeros enteros positivos utilizando el bucle Do Until en VBA.
Para ello, necesita ejecutar el bucle hasta que el siguiente número sea menor o igual a 10. Tan pronto como el número sea mayor que 1o, su bucle se detendría.
Aquí está el código VBA que ejecutará este bucle y mostrará el resultado en un cuadro de mensaje.
Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do Until i > 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub
El bucle anterior sigue funcionando hasta que el valor de ‘i’ se convierte en 11. Tan pronto como se convierte en 11, el bucle finaliza (ya que la condición ‘Until’ se convierte en True).
Ejemplo 2 – Introducir las fechas del mes actual
Digamos que quieres introducir todas las fechas del mes actual en una columna de la hoja de cálculo.
Puede hacerlo utilizando el siguiente código de bucle Do Until:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub
El código anterior introduciría todas las fechas en la primera columna de la hoja de trabajo (empezando por A1). El bucle continúa hasta que el Mes de la variable CMDate no sea igual al del mes actual.
Declaración Exit Do
Puede utilizar la declaración ‘Exit Do’ para salir del bucle.
En cuanto el código ejecuta la línea ‘Exit Do’, sale del bucle Do Until y pasa el control a la siguiente línea justo después del bucle.
Por ejemplo, si quiere introducir sólo las 10 primeras fechas, entonces puede salir del bucle tan pronto como se introduzcan las 10 primeras fechas.
El siguiente código hará esto:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub
En el código anterior, tan pronto como el valor de ‘i’ se convierta en 10, se ejecutará la sentencia Exit Do y el bucle finalizará.
For Each
En VBA, puede realizar un bucle a través de un conjunto de colecciones utilizando el bucle ‘For Each’.
Aquí hay algunos ejemplos de colecciones en Excel VBA:
- Una colección de todos los libros de trabajo abiertos.
- Una colección de todas las hojas de trabajo de un libro.
- Una colección de todas las celdas de un rango de celdas seleccionadas.
- Una colección de todos los gráficos o formas del libro de trabajo.
Usando el bucle ‘For Each’, puede recorrer cada uno de los objetos de una colección y realizar alguna acción sobre ellos.
Por ejemplo, puede pasar por todas las hojas de trabajo de un libro y protegerlas, o puede pasar por todas las celdas de la selección y cambiar el formato.
Con el bucle ‘For Each’ (también denominado bucle ‘For Each-Next’), no necesita saber cuántos objetos hay en una colección.
El bucle ‘For Each’ pasaría automáticamente por cada objeto y realizaría la acción especificada. Por ejemplo, si quiere proteger todas las hojas de trabajo de un libro, el código sería el mismo tanto si tiene un libro con 3 hojas de trabajo como si tiene 30 hojas de trabajo.
Aquí tiene la sintaxis del bucle For Each-Next en Excel VBA.
For Each element In collectionNext
Ahora veamos un par de ejemplos de uso del bucle For Each en Excel.
Ejemplo 1 – Recorrer todas las hojas de trabajo de un libro (y protegerlo)
Suponga que tiene un libro de trabajo en el que desea proteger todas las hojas de trabajo.
El siguiente bucle For Each-Next puede hacerlo fácilmente:
Sub ProtectSheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.ProtectNext wsEnd Sub
En el código anterior, hemos definido la variable ‘ws’ como un objeto Worksheet. Esto le dice a VBA que ‘ws’ debe interpretarse como un objeto de hoja de trabajo en el código.
Ahora utilizamos la sentencia ‘For Each’ para recorrer cada ‘ws’ (que es un objeto de hoja de trabajo) en la colección de todas las hojas de trabajo del libro activo (dada por ActiveWorkbook.Worksheets).
Note que a diferencia de otros bucles en los que hemos tratado de proteger todas las hojas de trabajo de un libro de trabajo, aquí no necesitamos preocuparnos de cuántas hojas de trabajo hay en el libro de trabajo.
No necesitamos contarlas para ejecutar el bucle. El bucle For Each asegura que todos los objetos sean analizados uno a uno.
Ejemplo 2 – Recorrer todos los libros de trabajo abiertos (y guardarlos todos)
Si trabaja con varios libros de trabajo al mismo tiempo, puede resultar útil poder guardar todos estos libros de trabajo a la vez.
El siguiente código VBA puede hacer esto por nosotros:
Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub
Nota que en este código, no te aparece un aviso que te pide guardar el libro de trabajo en una ubicación específica (si lo guardas por primera vez).
Lo guarda en la carpeta por defecto (en mi caso era la carpeta ‘Documentos’). Este código funciona mejor cuando estos archivos ya están guardados y estás haciendo cambios y quieres guardar todos los libros de trabajo rápidamente.
Ejemplo 3 – Recorrer todas las celdas de una selección (Resaltar valores negativos)
Usando el bucle ‘For Each’, puedes recorrer todas las celdas de un rango específico o del rango seleccionado.
Esto puede ser útil cuando se quiere analizar cada celda y realizar una acción basada en ella.
Por ejemplo, a continuación se muestra el código que recorrerá todas las celdas de la selección y cambiará el color de las celdas con valores negativos a rojo.
Sub HighlightNegativeCells()Dim Cll As RangeFor Each Cll In SelectionIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub
(Tenga en cuenta que he utilizado Cll como nombre corto de variable para Cell. Es recomendable no utilizar nombres de objetos como Sheets o Range como nombres de variables)
En el código anterior, el bucle For Each-Next recorre la colección de celdas de la selección. La sentencia IF se utiliza para identificar si el valor de la celda es negativo o no. En caso de que lo sea, se le da a la celda un color interior rojo, sino se pasa a la siguiente celda.
En caso de que no tengas una selección, y en cambio quieras que VBA seleccione todas las celdas rellenas de una columna, empezando por una celda concreta (igual que usamos Control + Mayúsculas + Flecha abajo para seleccionar todas las celdas rellenas), puedes usar el siguiente código:
Sub HighlightNegativeCells()Dim Cll As RangeDim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))For Each Cll In RngIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub
En el ejemplo anterior, no importa cuántas celdas rellenas haya. Empezará por la celda A1 y analizará todas las celdas rellenas contiguas de la columna.
Tampoco es necesario que tengas seleccionada la celda A1. Puede tener seleccionada cualquier celda lejana y cuando el código se ejecute, seguirá considerando todas las celdas de la columna A (empezando por A1) y coloreará las celdas negativas.
Estado ‘Exit For’
Puede utilizar el estado ‘Exit For’ en el bucle For Each-Next para salir del bucle. Esto se suele hacer en caso de que se cumpla una condición específica.
Por ejemplo, en el ejemplo 3, como estamos recorriendo un conjunto de celdas, puede ser más eficiente comprobar si hay valores negativos o no. En caso de que no haya valores negativos, podemos simplemente salir del bucle y ahorrar algo de tiempo de procesamiento de VBA.
A continuación se muestra el código VBA que hará esto:
Sub HighlightNegativeCells()Dim Cll As RangeFor Each Cll In SelectionIf WorksheetFunction.Min(Selection) >= 0 Then Exit ForIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub
Dónde poner el código VBA
¿Se pregunta dónde va el código VBA en su libro de Excel?
Excel tiene un backend VBA llamado el editor VBA. Necesita copiar y pegar el código en la ventana de código del módulo Editor VB.
Aquí están los pasos para hacer esto:
- Vaya a la pestaña Desarrollador.
- Haga clic en la opción Visual Basic. Esto abrirá el editor de VB en el backend.
- En el panel del Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desee insertar el código. Si no ve el Explorador de proyectos vaya a la pestaña Ver y haga clic en Explorador de proyectos.
- Vaya a Insertar y haga clic en Módulo. Esto insertará un objeto módulo para su libro de trabajo.
- Copie y pegue el código en la ventana del módulo.
También te pueden gustar los siguientes tutoriales de Excel:
- Cómo grabar una macro en Excel.
- Cómo crear funciones definidas por el usuario en Excel.
- Cuadro de mensajes VBA de Excel
- Cómo ejecutar una macro en Excel.
- Cómo crear y utilizar complementos en Excel.
- Eventos VBA de Excel – Una guía fácil (y completa).
- Cómo ordenar datos en Excel utilizando VBA (Una guía paso a paso).
- 24 útiles ejemplos de macros de Excel para principiantes de VBA (listos para usar).
- Cómo utilizar la función InStr de Excel VBA (con EJEMPLOS prácticos).
- Libro de trabajo de macros personales de Excel | Guardar & Utilizar macros en todos los libros de trabajo.
- Utilizar Select Case en Excel VBA.
- .