Para aprovechar al máximo Excel y VBA, necesita saber cómo usar los bucles de manera eficiente.
En VBA, los bucles le permiten pasar por un conjunto de objetos / valores y analizarlos uno por uno. También puede realizar tareas específicas para cada bucle.
Aquí hay un ejemplo simple del uso de bucles VBA en Excel.
Suponga que tiene un conjunto de datos y desea resaltar todas las celdas en filas pares. Puede usar un bucle VBA para recorrer el rango y analizar cada número de fila de celda. Si resulta parejo, le das un color, de lo contrario lo dejas como está.
Ahora bien, esto, por supuesto, es muy simple de hacer un bucle en Excel VBA (y también puede hacer esto usando formato condicional).
En la vida real, puede hacer mucho más con los bucles de VBA en Excel que pueden ayudarlo a automatizar tareas.
Aquí hay algunos ejemplos más prácticos en los que los bucles VBA pueden ser útiles:
- Recorrer un rango de celdas y analizar cada celda (resaltar celdas con un texto específico).
- Recorrer todas las hojas de trabajo y hacer algo con cada una (como protegerla / desprotegerla).
- Recorra todos los libros abiertos (y guarde cada libro o cierre todos excepto el libro activo).
- Recorra todos los caracteres de una celda (y extraiga la parte numérica de una cadena).
- Recorre todos los valores de una matriz.
- Recorra todos los gráficos / objetos (y dé un borde o cambie el color de fondo).
Ahora, para utilizar mejor los bucles en Excel VBA, debe conocer los diferentes tipos que existen y la sintaxis correcta de cada uno.
En este tutorial, mostraré diferentes tipos de bucles VBA de Excel y cubriré algunos ejemplos para cada bucle.
Nota: Este será un gran tutorial, donde intentaré cubrir cada bucle de VBA con cierto detalle. Le recomiendo que marque esta página para referencia futura.Si está interesado en aprender VBA de forma sencilla, consulte mi Capacitación en línea de Excel VBA.
Para el siguiente bucle
El ciclo "Para el siguiente" le permite pasar por un bloque de código el número de veces especificado.
Por ejemplo, si le pido que sume los números enteros del 1 al 10 manualmente, sumaría los dos primeros números, luego agregaría el tercer número al resultado, luego agregaría el cuarto número al resultado, y así sucesivamente …
¿No es así?
La misma lógica se usa en el bucle For Next en VBA.
Usted especifica cuántas veces desea que se ejecute el bucle y también especifica qué desea que haga el código cada vez que se ejecuta el bucle.
A continuación se muestra la sintaxis del bucle For Next:
Para contador = Inicio a fin [Valor de paso] [Bloque de código para ejecutar] Siguiente [contador]
En el ciclo For Next, puede utilizar un contador (o cualquier variable) que se utilizará para ejecutar el ciclo. Este contador le permite ejecutar este bucle el número de veces necesario.
Por ejemplo, si quiero sumar los primeros 10 enteros positivos, entonces mi valor de Contador sería de 1 a 10.
Echemos un vistazo a algunos ejemplos para comprender mejor cómo funciona el bucle For Next.
Ejemplo 1: suma de los primeros 10 números enteros positivos
A continuación se muestra el código que agregará los primeros 10 enteros positivos usando un bucle For Next.
A continuación, mostrará un cuadro de mensaje que muestra la suma de estos números.
Sub AddNumbers () Dim Total As Integer Dim Count As Integer Total = 0 Para Count = 1 a 10 Total = Total + Count Siguiente Count MsgBox Total End Sub
En este código, el valor de Total se establece en 0 antes de ingresar al bucle For Next.
Una vez que entra en el ciclo, mantiene el valor total después de cada ciclo. Entonces, después del primer ciclo, cuando el Contador es 1, el valor "Total" se convierte en 1, y después del segundo ciclo se convierte en 3 (1 + 2), y así sucesivamente.
Y finalmente, cuando finaliza el ciclo, la variable "Total" tiene la suma de los primeros 10 números enteros positivos.
A continuación, un MsgBox simplemente muestra el resultado en un cuadro de mensaje.
Ejemplo 2: suma de los primeros 5 números enteros pares positivos
Para sumar los primeros cinco números enteros pares positivos (es decir, 2, 4, 6, 8 y 10), necesita un código similar con una condición para considerar solo los números pares e ignorar los números impares.
Aquí hay un código que lo hará:
Sub AddEvenNumbers () Dim Total As Integer Dim Count As Integer Total = 0 For Count = 2 to 10 Step 2 Total = Total + Count Siguiente recuento MsgBox Total End Sub
Tenga en cuenta que comenzamos el valor de Count desde 2 y también usamos "Paso 2‘.
Cuando usas 'Paso 2', le dice al código que incremente el valor de "Recuento" en 2 cada vez que se ejecuta el ciclo.
Entonces, el valor de Count comienza desde 2 y luego se convierte en 4, 6, 8 y 10 a medida que ocurre el bucle.
NOTA: Otra forma de hacer esto podría ser ejecutar el ciclo de 1 a 10 y dentro del ciclo verificar si el número es par o impar. Sin embargo, usar Step, en este caso, es una forma más eficiente ya que no requiere que el ciclo se ejecute 10 veces, sino solo 5 veces.
El valor de Paso también puede ser negativo. En tal caso, el contador comienza con un valor más alto y sigue disminuyendo según el valor de paso especificado.
Ejemplo 3: introducción del número de serie en las celdas seleccionadas
También puede utilizar el ciclo For Next para recorrer una colección de objetos (como celdas, hojas de trabajo o libros de trabajo),
Aquí hay un ejemplo que ingresa rápidamente números de serie en todas las celdas seleccionadas.
Sub EnterSerialNumber () Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count para el contador = 1 To RowCount ActiveCell.Offset (Counter - 1, 0) .Value = Counter Next Counter End Sub
El código anterior primero cuenta el número de filas seleccionadas y luego asigna este valor a la variable RowCount. Luego ejecutamos el ciclo de "1 a RowCount".
También tenga en cuenta que, dado que la selección puede ser cualquier número de filas, hemos establecido la variable Rng en Selección (con la línea "Establecer Rng = Selección"). Ahora podemos usar 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 usar el ciclo "Para el siguiente" para revisar todas las hojas de trabajo en el libro de trabajo 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 Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i) .Protect Next i End Sub
El código anterior cuenta el número de hojas utilizando ActiveWorkbook.Worksheets.Count. Esto le dice a VBA cuántas veces se debe ejecutar el ciclo.
En cada caso, se refiere al libro de trabajo I (usando Hojas de trabajo (i)) y lo protege.
También puede usar este mismo código para Desproteger hojas de trabajo. Solo cambia la linea Hojas de trabajo (i) .Proteger para Hojas de trabajo (i)..
Bucles "para el siguiente" anidados
Puede utilizar bucles "For Next" anidados para realizar 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éjame mostrarte cómo usar esto usando un ejemplo.
Suponga que tengo 5 libros de trabajo abiertos en mi sistema y quiero proteger todas las hojas de trabajo en todos estos libros de trabajo.
A continuación se muestra el código que hará esto:
Sub ProtectWorksheets () Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Cunt For j = 1 To Workbooks (i) .Worksheets.Count Workbooks (i) .Worksheets (j) .Protect Next j Next i End Sub
El anterior es un bucle For Next anidado, ya que hemos utilizado un bucle For Next dentro de otro.
Declaraciones "EXIT For" en For Next Loops
La declaración "Exit For" le permite salir del ciclo "For Next" por completo.
Puede usarlo en los casos en que desee que el ciclo For Next finalice cuando se cumpla una determinada condición.
Tomemos un ejemplo en el que tiene un conjunto de números en la columna A y desea resaltar todos los números negativos en fuente roja. En este caso, necesitamos analizar cada celda por su valor y luego cambiar el color de la fuente en consecuencia.
Pero para hacer que el código sea más eficiente, primero podemos verificar si hay valores negativos en la lista o no. Si no hay valores negativos, podemos usar Exit For para que la declaración simplemente salga del código.
A continuación se muestra el código que hace esto:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Contador = Rng.Count For i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Luego salga para If Rng (i) .Value <0 Then Rng (i) .Font.Color = vbRed Siguiente i End Sub
Cuando usa la instrucción "Exit For" dentro de un ciclo "For Next" anidado, saldrá del ciclo en el que se ejecuta y continuará para ejecutar la siguiente línea en el código después del ciclo For Next.
Por ejemplo, en el siguiente código, la declaración "Exit For" lo sacará del bucle interno, pero el bucle externo continuará funcionando.
Sub SampleCode () Para i = 1 a 10 Para j = 1 a 10 Salir para siguiente J Siguiente i Finalizar sub
Hacer mientras bucle
Un bucle "Do While" le permite verificar una condición y ejecutar el bucle mientras esa condición se cumple (o es VERDADERA).
Hay dos tipos de sintaxis en Do While Loop.
Hacer [Condición while] [Bloque de código para ejecutar] Bucle
y
Haz [Bloque de código para ejecutar] Bucle [Condición while]
La diferencia entre estos dos es que en el primero, la condición While se verifica primero antes de que se ejecute cualquier bloque de código, y en el segundo caso, el bloque de código se ejecuta primero y luego se verifica la condición While.
Esto significa que si la condición While es False en ambos casos, el código aún se ejecutará al menos una vez en el segundo caso (ya que la condición "While" se verifica después de que el código se haya ejecutado una vez).
Ahora veamos algunos ejemplos del uso de bucles Do While en VBA.
Ejemplo 1: agregue los primeros 10 números enteros positivos usando VBA
Suponga que desea agregar los primeros diez enteros positivos usando el ciclo Do While en VBA.
Para hacer esto, puede usar el ciclo 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 ciclo se detendrá.
Aquí está el código VBA que ejecutará este bucle Do While y mostrará el resultado en un cuadro de mensaje.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
El ciclo anterior continúa funcionando hasta que el valor de "i" se convierte en 11. Tan pronto como se convierte en 11, el ciclo termina (cuando la condición While se vuelve Falsa).
Dentro del ciclo, hemos utilizado una variable de resultado que contiene el valor final. Una vez que se completa el ciclo, un cuadro de mensaje muestra el valor de la variable "Resultado".
Ejemplo 2: ingrese fechas para el mes actual
Supongamos que desea ingresar 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 Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
El código anterior ingresaría todas las fechas en la primera columna de la hoja de trabajo (comenzando desde A1). Los ciclos continúan hasta que el valor del mes de la variable "CMDate" coincide con el del mes actual.
Exit Do Statement
Puede usar la instrucción Exit Do para salir del ciclo. 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 solo desea ingresar las primeras 10 fechas, puede salir del ciclo tan pronto como ingrese las primeras 10 fechas.
El siguiente código hará esto:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 Si i> = 10 Entonces Salir Do CMDate = CMDate + 1 Loop End Sub
En el código anterior, la declaración IF se usa para verificar 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 instrucción Exit Do y el ciclo finaliza.
Hacer hasta bucle
Los bucles "Hacer hasta" son muy parecidos a los bucles "Hacer mientras".
En "Hacer mientras", el ciclo se ejecuta hasta que se cumple la condición dada, mientras que en "Hacer hasta", se repite hasta que se cumple la condición especificada.
Hay dos tipos de sintaxis en el ciclo Do until.
Hacer [Hasta condición] [Bloque de código para ejecutar] Bucle
y
Hacer [Bloque de código para ejecutar] Bucle [Hasta condición]
La diferencia entre estos dos es que en el primero, la condición Hasta se verifica primero antes de que se ejecute cualquier bloque de código, y en el segundo caso, el bloque de código se ejecuta primero y luego se verifica la condición Hasta.
Esto significa que si la condición Hasta es VERDADERA en ambos casos, el código aún se ejecutará al menos una vez en el segundo caso (ya que la condición "Hasta" se verifica después de que el código se haya ejecutado una vez).
Ahora veamos algunos ejemplos del uso de bucles Do until en VBA.
Nota: Todos los ejemplos de Do until son los mismos que los de Do While. Estos se han modificado para mostrarle cómo funciona el ciclo Do until.
Ejemplo 1: agregue los primeros 10 números enteros positivos usando VBA
Suponga que desea agregar los primeros diez enteros positivos usando el ciclo Do until en VBA.
Para hacer esto, necesita ejecutar el ciclo hasta que el siguiente número sea menor o igual a 10. Tan pronto como el número sea mayor que 1o, su ciclo se detendrá.
Aquí está el código VBA que ejecutará este bucle y mostrará el resultado en un cuadro de mensaje.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do until i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
El ciclo anterior continúa funcionando hasta que el valor de "i" se convierte en 11. Tan pronto como se convierte en 11, el ciclo finaliza (cuando la condición "Hasta" se convierte en Verdadero).
Ejemplo 2: ingrese fechas para el mes actual
Supongamos que desea ingresar todas las fechas del mes actual en una columna de la hoja de trabajo.
Puede hacerlo utilizando el siguiente código de bucle Do until:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Año (Fecha), Mes (Fecha), 1) Hasta el mes (CMDate) Mes (Fecha) Rango ("A1"). Desplazamiento ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
El código anterior ingresaría todas las fechas en la primera columna de la hoja de trabajo (comenzando desde A1). El ciclo continúa hasta que el mes de la variable CMDate no es igual al del mes actual.
Exit Do Statement
Puede utilizar la declaración "Exit Do" para salir del ciclo.
Tan pronto como 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 solo desea ingresar las primeras 10 fechas, puede salir del ciclo tan pronto como ingrese las primeras 10 fechas.
El siguiente código hará esto:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Año (Fecha), Mes (Fecha), 1) Hasta el mes (CMDate) Mes (Fecha) Rango ("A1"). Desplazamiento ( i, 0) = CMDate i = i + 1 Si i> = 10 Entonces Salir Do CMDate = CMDate + 1 Loop End Sub
En el código anterior, tan pronto como el valor de "i" se convierte en 10, se ejecuta la instrucción Exit Do y el ciclo finaliza.
Para cada
En VBA, puede recorrer un conjunto de colecciones utilizando el ciclo "For Each".
Aquí hay algunos ejemplos de colecciones en Excel VBA:
- Una colección de todos los libros abiertos.
- Una colección de todas las hojas de trabajo de un libro.
- Una colección de todas las celdas en un rango de celdas seleccionadas.
- Una colección de todos los gráficos o formas del libro.
Con el ciclo "For Each", puede pasar por cada uno de los objetos de una colección y realizar alguna acción en él.
Por ejemplo, puede revisar todas las hojas de trabajo de un libro y protegerlas, o puede revisar todas las celdas de la selección y cambiar el formato.
Con el ciclo "For Each" (también denominado ciclo "For Each-Next"), no es necesario 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 desea proteger todas las hojas de trabajo en un libro de trabajo, el código sería el mismo si tiene un libro de trabajo con 3 hojas de trabajo o con 30 hojas de trabajo.
Aquí está la sintaxis del bucle For Each-Next en Excel VBA.
Para cada elemento de la colección [Bloque de código para ejecutar] Siguiente [elemento]
Ahora veamos un par de ejemplos del uso de For Each Loop en Excel.
Ejemplo 1: revise todas las hojas de trabajo de un libro de trabajo (y protéjalo)
Suponga que tiene un libro de trabajo en el que desea proteger todas las hojas de trabajo.
A continuación, el bucle For Each-Next puede hacer esto fácilmente:
Sub ProtectSheets () Dim ws como hoja de trabajo para cada ws en ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
En el código anterior, hemos definido la variable "ws" como un objeto de hoja de trabajo. Esto le dice a VBA que 'ws' debe interpretarse como un objeto de hoja de trabajo en el código.
Ahora usamos la declaración "For Each" para revisar cada "ws" (que es un objeto de hoja de trabajo) en la colección de todas las hojas de trabajo en el libro de trabajo activo (proporcionado por ActiveWorkbook.Worksheets).
Tenga en cuenta que, a diferencia de otros bucles en los que hemos intentado proteger todas las hojas de trabajo de un libro de trabajo, aquí no tenemos que preocuparnos por cuántas hojas de trabajo hay en el libro de trabajo.
No necesitamos contarlos para ejecutar el ciclo. For Cada bucle garantiza que todos los objetos se analicen uno por uno.
Ejemplo 2: revisar todos los libros abiertos (y guardar todos)
Si trabaja con varios libros de trabajo al mismo tiempo, puede resultar útil poder guardar todos estos libros de trabajo a la vez.
A continuación, el código de VBA puede hacer esto por nosotros:
Sub Guardar todos los libros de trabajo () Dim wb como libro de trabajo para cada wb en los libros de trabajo wb. Guardar siguiente wb End Sub
Tenga en cuenta que en este código, no recibe un mensaje que le pide que guarde el libro de trabajo en una ubicación específica (si lo guarda por primera vez).
Lo guarda en la carpeta predeterminada (era la carpeta "Documentos" en mi caso). Este código funciona mejor cuando estos archivos ya están guardados y está realizando cambios y desea guardar todos los libros de trabajo rápidamente.
Ejemplo 3: revise todas las celdas de una selección (resalte los valores negativos)
Con el bucle "For Each", puede recorrer todas las celdas en un rango específico o en el rango seleccionado.
Esto puede resultar útil cuando desee 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 como rango para cada Cll en la selección Si Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
(Tenga en cuenta que he usado Cll como un nombre de variable corto para Cell. Es recomendable no usar nombres de objeto como Hojas o Rango como nombres de variable)
En el código anterior, el ciclo For Each-Next pasa por la colección de celdas de la selección. La instrucción IF se utiliza para identificar si el valor de la celda es negativo o no. En caso de que lo sea, la celda recibe un color interior rojo, de lo contrario, pasa a la siguiente celda.
En caso de que no tenga una selección y, en su lugar, desee que VBA seleccione todas las celdas llenas en una columna, comenzando desde una celda específica (al igual que usamos Control + Shift + tecla de flecha abajo para seleccionar todas las celdas llenas), puede use el siguiente código:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Establecer Rng = Range ("A1", Range ("A1"). End (xlDown)) Para cada Cll In Rng If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
En el ejemplo anterior, no importa cuántas celdas llenas haya. Comenzará desde la celda A1 y analizará todas las celdas rellenas contiguas en la columna.
Tampoco es necesario que tengas seleccionada la celda A1. Puede seleccionar cualquier celda lejana y, cuando se ejecute el código, seguirá considerando todas las celdas de la columna A (comenzando desde A1) y coloreará las celdas negativas.
Declaración de "salida para"
Puede utilizar la instrucción "Exit For" en el ciclo For Each-Next para salir del ciclo. Esto generalmente se hace en caso de que se cumpla una condición específica.
Por ejemplo, en el Ejemplo 3, a medida que pasamos por un conjunto de celdas, puede ser más eficiente verificar si hay valores negativos o no. En caso de que no haya valores negativos, simplemente podemos salir del ciclo 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 como rango para cada Cll en la selección If WorksheetFunction.Min (Selection)> = 0 Luego Salir para If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End 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 de VBA llamado editor de VBA. Debe copiar y pegar el código en la ventana de código del módulo VB Editor.
Estos son los pasos para hacer esto:
- Vaya a la pestaña Desarrollador.
- Haga clic en la opción de Visual Basic. Esto abrirá el editor de VB en el backend.
- En el panel Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desea 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 de módulo para su libro de trabajo.
- Copie y pegue el código en la ventana del módulo.
