Blog de Ninja ExcelMacros en ExcelLOOPS en VBA: conoce cómo funcionan y cuándo usarlos

LOOPS en VBA: conoce cómo funcionan y cuándo usarlos

Información clave

Una herramienta común para hacer macros en VBA de Excel es el uso de LOOPS. También se les conoce en español como “bucles”. Con loops o bucles puedes repetir un código el número de veces que determines para no tener que escribirlo todas las veces para cada caso.

Lo básico sobre Visual Basic (VBA)

Finalidad: Visual Basic para Aplicaciones (VBA) es el lenguaje de programación de las macros en Excel. De esta manera puedes crear y modificar macros para que hagan lo que tú quieras.

Ventajas: Usando VBA tienes mayor flexibilidad y puedes lograr hacer más aplicaciones que usando la grabadora de macros.

Estructura:

Sub Nombre_Macro()

Contenido

End Sub
  • En Visual Basic (VBA), para crear macros debes empezar con “Sub Nombre_Macro()” y terminar con “End Sub”.
  • La palabra “Sub” quiere decir “subrutina”. El contenido de la subrutina es lo que debes programar.
  • Los comentarios se escriben con una comilla simple antes. Se usan en general para explicar lo que se está haciendo en caso de que otra persona trabaje con este código. Al ser ejecutado el código, VBA de Excel ignora los comentarios. Por ejemplo:
Sub Nombre_Macro()

'Esto es un comentario

Contenido

End Sub

Antes de empezar con Visual Basic (VBA)

Antes de crear la macro, debes tener visible la pestaña de “Programador” en Excel.

Si tienes Windows sigue estos pasos:

  1. Haz click derecho en cualquier parte de la cinta de opciones y haz click en “Personalizar la cinta de opciones”.
  2. Al abrirse el cuadro de opciones, en el recuadro de la derecha, haz click en el cuadrado de “Programador”
  3. Haz click en “Aceptar”.
  4. Vas a encontrar la pestaña de “Programador” al lado de la pestaña “Vista”.Vista de la Pestaña de Programador en Excel

Si tienes Mac sigue los siguientes pasos:

  1. Haz click en “Excel” (arriba a la izquierda)
  2. Haz click en “Preferencias”
  3. Selecciona la sección de “Barra de Herramientas y cinta de opciones”
  4. Al abrirse el cuadro de opciones, en el recuadro de la derecha, haz click en el cuadrado de “Programador”
  5. Haz click en “Guardar”.
  6. Vas a encontrar la pestaña de “Programador” al lado de la pestaña “Vista”.

Cómo crear una macro con Visual Basic (VBA)

  1. Haz click en “Visual Basic”, dentro de la Pestaña de “Programador”.Abrir VBA en Excel

Tip Ninja: Para Windowspuedes apretar Alt+F11 para que se abra el editor de Visual Basic. Para Mac puedes apretar Fn+Alt+F11

  1. Se abre la siguiente ventana. Esta ventana es el editor de Visual Basic (VBA) para Excel. Consiste en 5 partes:
    1. Barra de menú: tiene las opciones de: “Archivo”, “Edición”, “Ver”, etc.
    2. Barra de herramientas: tiene las opciones de “Guardar”, “Copiar”, “Pegar”, “Ejecutar macro” (el triángulo verde de “play”).
    3. Explorador de proyectos: permite ver los Libros de Excel activos, su número de hojas y los módulos.
    4. Ventana de propiedades: muestra las propiedades del proyecto seleccionado.
    5. Ventana de trabajo: área donde se abren los módulos: ventana donde se programan las macros.Ventana de editor de VBA en Excel.
  2. En general, para escribir macros se usa “Módulos”. Para esto, en la Barra de menú haz click en “Insertar” y luego haz click en “Módulo”.Agregar módulo de VBA en Excel
  3. Ahora, en el Explorador de Proyectos aparece Módulo 1. En este módulo puedes escribir tus macros.Módulo 1 de VBA en Excel creado

Tip Ninja: puedes hacer tus macros en distintos módulos y no habrá problema, es una decisión de orden que depende de cada uno.

  1. En la Ventana de Trabajo escribe lo siguiente:
Sub Primera_Macro()
Range(“A1”).Value = “Hola mundo"
End Sub


En este caso, a la celda A1 (Range(“A1”)) le asignamos el valor (.Value =) de “Hola mundo”. Más adelante veremos cómo se usa “Range” y “Value”.

  1. ¡Listo! Ya está creada la macro. Puedes cerrar el editor de VBA.
  2. Para ejecutarla ve al menú de Macros, selecciona la macro y haz click “Aceptar”.
  3. Se esta manera se obtiene:Ejecución de la primera macro en VBA de Excel

También puedes ejecutar una macro seleccionando el código en VBA y haciendo click el símbolo de “Ejecutar macro” o apretando F5 en Windows. Para Mac debes apretar Fn+Alt+F8.Ejecutar macro de Excel en VBA desde el módulo

También puede resultarte más cómodo crear un botón para una macro. Para aprender cómo crear un botón y asociarlo a una macro ve este video:

Ventanas con mensajes: MsgBox en VBA

Un uso común de las macros usando VBA en Excel es hacer ventanas con mensajes. También se conoce por su abreviación en VBA: MsgBox. Msgbox es muy útil para enviar mensajes avisando lo que se está haciendo en la planilla o para dar mensajes de bienvenida.

Sintaxis simplificada:

MsgBox(mensaje[,botones][,título])


Para ver la sintaxis completa, las opciones de botones y más detalles de cómo funciona MsgBox puedes ver este artículo de Microsoft.

Argumentos:

  • mensaje: es el mensaje que se quiere mostrar. Tiene un máximo de 1024 caracteres. El texto debe estar entre comillas.
  • [botones]: es una expresión numérica que está relacionada con tipos de botones. El valor por defecto es 0 que significa “Ok”. Es opcional.
  • [título]: es el título de la ventana. Es opcional.

Veamos ejemplos de MsgBox en VBA:

  1. Mensaje simple: en un módulo de VBA escribe lo siguiente:
Sub Mensaje_1()
MsgBox ("Hola Mundo")
End Sub
Segunda macro para ejecutar en VBA de Excel

Tip Ninja: Nota que no debes crear un nuevo módulo para crear una nueva macro. Para agregar una macro nueva solamente debes escribirla a continuación en el mismo módulo.

Luego, se ejecuta la macro y obtenemos la ventana que dice “Hola Mundo”, como se ve en la siguiente imagen:MsgBox ejecutado en VBA Excel

  1. Mensaje que incluye el valor de una celda: Para combinar palabras y el valor de una celda se usa “&”. Prueba con el siguiente código:
Sub Mensaje_2()
MsgBox ("El total de ventas es " & Range(“C8”).Value)
End Sub


Al ejecutar la macro se obtiene: Segundo ejemplo de MsgBox en VBA de Excel

Crear variables en VBA: Usar “Dim”

Para gran parte de las macros, es necesario crear una variable. Para crear variables en VBA se usa el comando “Dim”.

Sintaxis simplificada:

Dim variable [As tipo]


Para la sintaxis completa, que escapa del alcance de este artículo, puedes ver este artículo de Microsoft.

Argumentos:

  • Dim: quiere decir “dimensión” y con este comando puedes crear variables.
  • variable: el nombre de la variable que estás creando. Este nombre lo defines tú.

Tip Ninja: el nombre de la variable no usa espacios. Si es que quieres nombrarla con más de una palabra puedes usar mayúsculas para el comienzo de cada palabra o guión bajo como separador.

  • [As tipo]: As quiere decir “como” y luego eliges el tipo de variable que es. Los más comunes son:
    • String: para palabras
    • Integer: para números enteros
    • Decimal: para decimales
    • Variant: que se usa para tanto números como palabras
    • Range: para un conjunto de celdas

Para ver todos los tipos de variables puedes ver este artículo de Microsoft.

Como ejemplo crearemos una variable “mi_valor”. La VBA tomará como insumo tu edad en años usando InputBox, que funciona igual que MsgBox. Luego, transformará tu edad en meses (multiplicando por 12) y lo escribirá en la celda A1. Ve el siguiente código:

Sub Edad_en_meses()

'Crea la variable “mi_valor”
Dim mi_valor As Integer

'Define mi_valor como el valor ingresado
mi_valor = InputBox("Qué edad tienes?")

'Escribe en A1
Range("A1").Value = “Tienes ” & mi_valor * 12 & “ o más meses de edad”

End Sub

Al ejecutarlo aparece la ventana y rellenamos:

Ejemplo InputBox en VBA de Excel

Luego, al hacer click en “Aceptar” y aparece:

Macro de InputBox ejecutado, VBA en Excel

Selección de celdas en VBA: “Range”

El objeto “Range” es de los más importantes en VBA de Excel, significa “Rango” y es la representación de la celdas en el código. Puede ser una columna, una fila, una celda, un conjunto de celdas separadas, etc.

Hay una serie de acciones que se pueden hacer a las celdas como seleccionarlas (Select), darles un valor (Value), vaciarlas (Clear), etc. A estas acciones se les llaman “métodos”. Puedes ver todas las opciones de métodos para “Range” en este artículo de Microsoft. Los métodos se usan poniendo un punto y luego escribir lo que se quiere hacer.

Rangos simples:

Veamos unos ejemplos para entender cómo funciona:

  1. Darle un valor a la celda: Para decir “celda B3” se escribe “Range(“B3”)” y luego, para darle el valor 2 se agrega “.Value = 2”
Sub Rango1()
Range("B3").Value = 2
End Sub


Al ejecutar la macro vemos:

Ejemplo 1 Rango Simple VBA Excel
  1. Darle un valor a varias celdas: Para llamar a las celdas desde A1 hasta A4 se escribe “Range(“A1:A4”)” y luego se les asigna el valor 5 escribiendo “.Value = 5”
Sub Rango2()
Range("A1:A4").Value = 5
End Sub

Al ejecutar la macro nos resulta:

Ejemplo 2 Rango Simple VBA en Excel
  1. Darle un valor a varias celdas que no están juntas: Para llamar a las celdas desde A1 hasta A2 y desde B3 a C4  se escribe “Range(“A1:A2,B3:C4”))” y luego se les asigna el valor 10  escribiendo “.Value = 10”.
Sub Rango3()
Range("A1:A2,B3:C4").Value = 10
End Sub


Al ejecutar la macro obtenemos:

Ejemplo 3 Rango Simple VBA en Excel
  1. Crear variable “Ejemplo” que es un conjunto de celdas y se les asigna el valor 8: Se aplica lo aprendido de crear variables, luego se define esa variable como las celdas que queremos y luego se hace la acción sobre la variable.
Sub Rango4()

'Se crea la variable Ejemplo
Dim Ejemplo As Range

'Se define Ejemplo como las celdas desde A1 hasta C4
Set Ejemplo = Range("A1:C4")

'A todas las celdas de Ejemplo se les da el valor 8
Ejemplo.Value = 8

End Sub


Luego de ejecutar la macro tenemos:

Ejemplo 4 Rango Simple VBA en Excel
  1. Crear una variable que sea un conjunto de celdas y vaciarlas: Se aplica lo aprendido de crear variables, luego se define esa variable como las celdas que queremos y luego se hace la acción sobre la variable.
Sub Rango5()

'Se crea la variable Ejemplo
Dim Ejemplo As Range

'Se define Ejemplo como las celdas desde A2 hasta C4
Set Ejemplo = Range("A2:C4")

'Vaciamos las celdas de Ejemplo
Ejemplo.Clear

End Sub


Si teníamos esto en un comienzo:

Ejemplo 5 de Rango simple VBA en Excel

Luego de ejecutar la macro obtenemos:

Resultado del ejemplo 5 de Rango Simple VBA en Excel
  1. Crear una variable que sea un conjunto de celdas y seleccionarlas: Es similar al caso anterior, pero sólo se seleccionan esas celdas.
Sub Rango6()

'Se crea la variable Ejemplo2
Dim Ejemplo2 As Range

'Se define Ejemplo2 como las celdas desde B2 hasta C4
Set Ejemplo2 = Range("B2:C4")

'Selecciona las celdas de Ejemplo2
Ejemplo2.Select

End Sub


Al ejecutar la macro se obtiene:

Ejemplo 6 Rango Simple de VBA en Excel

Rango dinámico:

¿Qué pasa si queremos aplicar la macro a todas las celdas que tengo seleccionadas? En VBA se usa “Selection” para decir “las celdas que están seleccionadas al ejecutar la macro”.

Por ejemplo, para hacer una macro que le asigne el valor 5 a todas las celdas seleccionadas:

Sub Seleccion_5()

Dim rango As Range
Set rango = Selection
rango.Value=5

End Sub


También se podría escribir:

Sub Seleccion_5()
Selection.Value=5
End Sub

Si antes de ejecutar la macro se tenía seleccionado lo siguiente:

Ejemplo 1 Rango Dinámico VBA Excel

Luego de ejecutar la macro resulta:

Ejemplo 1 Rango dinámico ejecutado en VBA de Excel

Rango hasta la última entrada:

A veces no queremos seleccionar todo cada vez que queremos correr el programa, sino que preferimos que vaya hasta el último termino de una tabla. En VBA de Excel, para identificar la última celda rellenada se usa “.End(xlDown)” después de la celda de la que se comienza.

Tip Ninja: Puedes Tambien usar xlUp, xlToRight and xlToLeft para ir hasta la última celda hacia arriba, derecha e izquierda, respectivamente.

Veamos distintas aplicaciones con los siguientes ejemplos:

  1. Seleccionar la última fila no vacía: El siguiente código de VBA toma C3, luego baja hasta el final a partir de C3 y selecciona esa última celda:
Sub RangoUltimaEntrada1()

Range("C3").End(xlDown).Select

End Sub

Al ejecutar la macro se obtiene:

Ejemplo 1 Rango hasta última entrada en VBA de Excel

Si te fijas, este código es equivalente a apretar C3 luego Ctrl+Flecha abajo

  1. Seleccionar desde cierta celda hasta la última: Este código de VBA selecciona desde C3 hasta el final bajando desde C3:
Sub RangoHastaUltimaEntrada1()
Range(Range("C3"), Range("C3").End(xlDown)).Select
End Sub

Al ejecutar la macro se vería lo siguiente:

Ejemplo 2 Rango Dinámico en VBA de Excel

Si te fijas, este Código es equivalente a apretar C3 luego Ctrl+Shift+Fecha abajo

  1. Seleccionar desde la actual hasta la última: El siguiente código de VBA selecciona desde la celda seleccionada actualmente hasta el final:
Sub RangoHastaUltimaEntrada2()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Tip Ninja: ¿Por qué se usa ActiveCell y no Selection? Para VBA, ActiveCell es la celda que seleccionaste en caso de haber seleccionado una sola celda o la primera celda que seleccionaste dentro de un grupo. Selection es el conjunto de todas las celdas que seleccionaste. Así, para seleccionar desde una sola celda hasta abajo usamos ActiveCell.

Así, si la celda C3 es seleccionada al ejecutar la macro, el resultado es el mismo que el ejemplo anterior:

Ejemplo 3 Rango Dinámico en VBA de Excel

Si se ejecuta la macro teniendo la celda C5 seleccionada se obtiene:Ejemplo 3 Rango Dinámico VBA en Excel, resultado alternativo

Rango de filas y columnas enteras

Si queremos seleccionar o ejecutar una acción a filas o columnas enteras en VBA lo hacemos de la misma manera:

  1. Para toda la planilla se usa “Cells” en VBA. En este caso se selecciona toda la planilla:
Sub Seleccionar_Todo()
Cells.Select
End Sub

Al ejecutar esto resulta:

Seleccionar todas las celdas VBA de Excel
  1. En VBA para seleccionar columnas completas se usa “Columns” y el número que corresponda a la columna (A es 1, B es 2, etc). En el ejemplo a continuación, queremos vaciar la columna B:
Sub Vaciar_B()
Columns(2).Clear
End Sub


Si es que comenzamos con esto:

Comienzo ejemplo 2 rango filas y columnas en VBA de Excel

Al ejecutar el código de VBA resulta:

Ejemplo 2 Columnas y filas VBA en Excel
  1. Para seleccionar filas completas en VBA se usa “Rows” y el número de fila que corresponde. En este caso, se les asigna a todas el valor 100:
Sub Filas_100()
Rows("2:5").Value=100
End Sub

Al ejecutar esto resulta:Ejemplo 3 columnas y filas VBA en Excel

El condicional IF en VBA

Se usa el comando “If” en VBA para ejecutar ciertas instrucciones sólo si se cumplen ciertas condiciones. La estructura de este comando es similar a la función SI.

Estructura:

If Condición Then
Código que se ejecuta cuando se cumple la condición
Else
Código que se ejecuta cuando no se cumple la condición
End if

Argumentos:

  • “If”: significa el condicional “Si”.
  • “Then”: significa “Entonces” y sirve para asignar lo que pasa si es que se cumplen las condiciones.
  • “Else”: significa “En otro caso” para indicar qué hacer en caso de no cumplirse la condición.
  • “End If”: debes usarlo si es que usas más de una línea para expresar la condición y sus instrucciones relacionadas.

Existe una versión abreviada en que sólo das la instrucción si es que se cumple la condición y si no se cumple no pasa nada:

If Condición Then Instrucción


Para entender mejor este comando, veamos un par de ejemplo

  1. Condición abreviada: En el siguiente código se pone una condición para aprobar una asignatura.
Sub Aprueba_Reprueba()

'Se crean las variables
Dim puntaje As Integer, resultado As String

'Se define la variable puntaje como el valor de la celda C3
puntaje = Range("C3").Value

'Si es que el puntaje es mayor o igual a 60, entonces la variable resultado es “Aprueba”
If puntaje >= 60 Then resultado = "Aprueba"

'El valor de la celda D3 va a ser igual a la variable resultado
Range("D3").Value = resultado

End Sub

Entonces, si es que partimos de lo siguiente:

Ejemplo 1 If en VBA Excel

Al ejecutar la macro obtenemos:

Ejemplo 1 If ejecutado VBA en Excel

Si es que partimos de lo siguiente, como no definimos qué pasaba si no se cumplía la condición al ejecutar la macro no pasa nada:

Ejemplo 1 If en VBA de Excel, no se cumple la condición

Tip Ninja: Puedes usar los siguientes operadores para comparar: igual (=), distinto (<>), mayor (>), mayor o igual (>=), menor (<) y menor o igual (<=). También puedes usar “Not” para decir que no se cumpla alguna condición.

  1. Si usamos la versión “larga” del “If” para el mismo caso anterior, podemos decidir qué pasa si es que no cumple con la condición. Ve el siguiente código:
Sub Aprueba_Reprueba2()

‘Se crean las variables
Dim puntaje As Integer, resultado As String

‘Se define la variable puntaje como el valor de la celda C3
puntaje = Range("C3").Value

‘Si es el que puntaje es mayor o igual a 60, entonces:
If puntaje >= 60 Then

‘la variable resultado es “Aprueba”
resultado = "Aprueba"

‘Si es que no es así
Else

‘la variable resultado es “Reprueba”
resultado = "Reprueba"
End If

‘El valor de la celda D3 va a ser igual a la variable resultado
Range("D3").Value = resultado

End Sub

En el mismo ejemplo anterior, al ejecutar la macro nos queda:

Ejemplo If-Then en VBA en Excel, si se cumple

Y en este caso:

Ejemplo If Then en VBA Excel, no cumple la condición

Se pueden usar operadores lógicos dentro de las condiciones: “And”, “Or” y “Not”. Veamos un ejemplo de cada uno:

  1. Al usar “And” en una condición, para que esta se cumpla se deben cumplir ambas condiciones. En este ejemplo, se debe cumplir que el puntaje 1 sea mayor a 60 y asistencia mayor a 45% para aprobar.
Sub And_Uso()

'Se crean las variables
Dim puntaje As Integer, asistencia As Integer, resultado As String

'Se define puntaje como el valor de la celda C3
puntaje = Range("C3").Value

'Se define puntaje como el valor de la celda D3
asistencia = Range("D3").Value

'Si es el que puntaje es mayor o igual a 60 y la asistencia es mayor a 45%
'entonces aprueba; si no cumple, reprueba:
If puntaje >= 60 And asistencia > 0.45 Then
resultado = "Aprueba"
Else
resultado = "Reprueba"
End If

'El valor de la celda E3 va a ser igual a la variable resultado
Range("E3").Value = resultado

End Sub


En el siguiente caso, al ejecutar la macro el resultado es Aprueba porque cumple con ambas condiciones:

Ejemplo AND - IF en VBA de Excel

En este caso como no se cumplen ambas condiciones, el resultado es Reprueba:

Ejemplo AND - IF en VBA de Excel, no se cumplen ambas
  1. Al usar “Or” en una condición, para que esta se cumpla se deben al menos una condición. En este ejemplo, se debe cumplir que el puntaje 1 sea mayor a 60, que la asistencia sea mayor al 45% o ambos para aprobar.
Sub Or_Uso()

Dim puntaje As Integer, asistencia As Integer, resultado As String
puntaje = Range("C3").Value
asistencia = Range("D31").Value

If puntaje >= 60 Or asistencia > 0.45 Then
resultado = "Aprueba"
Else
resultado = "Reprueba"
End If

Range("E3").Value = resultado

End Sub


En este caso, como cumple al menos una condición también Aprueba:

Ejemplo OR - IF en VBA de Excel, se cumple una

En el siguiente caso, como cumple con ambas el resultado es Aprueba:

Ejemplo OR - IF en VBA de Excel, se cumplen ambas condiciones

Finalmente, en este caso como no cumple con ninguna el resultado es Reprueba:

Ejemplo OR - IF en VBA de Excel, no se cumple ninguna condición
  1. Al usar “Not” la condición es que algo no suceda. En este ejemplo, se va a aprobar si es que el puntaje 1 es mayor a 60 y la asistencia no sea cero.
Sub Not_Uso()

Dim puntaje As Integer, asistencia As Integer, resultado As String
puntaje = Range("A1").Value
asistencia = Range("B1").Value

If puntaje >= 60 And Not asistencia = 0 Then
resultado = "Aprueba"
Else
resultado = "Reprueba"
End If

Range("C1").Value = resultado

End Sub


En el siguiente caso, como cumple con ambas el resultado es Aprueba:

Ejemplo IF- NOT en VBA de Excel, sí se cumplen las condiciones

En este caso, no cumple con la segunda condición, así que el resultado es Reprueba:

Ejemplo NOT - IF en VBA de Excel, no se cumplen ambas

También puedes usar el condicional “If” combinado con otro tipo de acciones, por ejemplo con MsgBox. Por ejemplo, ve este código:

Sub Vaciar_Hoja()

'Se crea la variable respuesta (se ve en la siguiente sección)
Dim respuesta As Integer

'Se define la variable respuesta como lo que se elija en la ventana
respuesta = MsgBox("Estás seguro que quieres vaciar la hoja?", vbYesNo, "Vaciar Hoja")

'Se define que si la respuesta es “Sí” entonces se borrarán las celdas y si es que se elige ‘“No” no pasa nada (se verá después en este artículo)
If respuesta = vbYes Then Cells.ClearContents

End Sub


Al ejecutar, se abre la ventana que hace la pregunta dando dos opciones: “Sí” y “No” como se ve en la imagen. Si respondes “Sí”, se borran los contenidos de la Hoja. Si respondes “No”, no pasa nada. Más adelante se verá en detalle del uso de “If” que marca condiciones.

Ejemplo de MsgBox con IF en VBA de Excel

El FOR loop en VBA

Otra herramienta común en Visual Basic (VBA) es el uso de loops con el comando “For”. Con loop de “For” puedes repetir un código el número de veces que determines para no tener que escribirlo todas las veces para cada caso.

Estructura:

For contador = comienzo to final

Código

Next contador


Argumentos:

“For”: signfica “Para”, queriendo decir que “para esos valores” se repita el código.

“To”: significa “Hasta”. Indica hasta qué valor toma el contador.

“Next”: Significa “Siguiente”, cuando se llega a esta línea se redirige de nuevo a “For” para ejecutar todo de nuevo hasta terminar la lista.

  1. Loop simple: se recorre una lista de números y se ejecuta el código todas las veces que diga el loop. Revisa este ejemplo:
Sub Primer_Loop()

'Creamos la variable contador “i”
Dim i As Integer

'Decimos que i va a tomar los valores desde 1 hasta 6
For i = 1 To 6

'Este Código se va a ejecutar para cada valor de i
Cells(i, 1).Value = 100 + i

'Vuelve hasta “For” hasta que se termine la enumeración
Next i

End Sub


En este primer loop las líneas entre “For” y “Next” se va a repetir seis veces. Para i=1, Visual Basic (VBA) va a asignarle el valor 100+1 a la Celda(1,1). Cuando el código llega a “Next i”, va a hacer que i aumente en uno y vuelve a “For”. Luego, para i=2 VBA va a asignarle el valor de 100+2 a la Celda(2,1) y así sucesivamente hasta el 6.

Al ejecutar esta macro se obtiene:

Ejemplo For Loop Simple en VBA de Excel

Tip Ninja: Acá para referirnos a las celdas usamos Cells(fila, columna). Así, Cells(1,1) es la celda A1 y Cells(2,3) es la celda C2.

  1. Loop Doble: uno puede hacer la cantidad de loops que quiera, uno dentro del otro. Los “For” loops funcionan de manera secuencial: van desde afuera hacia adentro, y solo una vez que termina lo de adentro, vuelve a tomar lo que hay afuera.
Sub Loop_Doble()

Dim i As Integer, j As Integer

For i = 1 To 6
For j = 1 To 2
Cells(i, j).Value = 100 + i + j
Next j
Next i

End Sub


En este ejemplo para i=1 y j=1 VBA va a poner en la celda (1,1) el valor de 100+1+1. Luego, el código llega a “Next j”, por lo que j aumenta en 1 y vuelve a “For j”.

Luego, con el valor i=1 y j=2 VBA va a poner en la celda(1,2) el valor 100+1+2. Después, el código llega a Next j, pero lo ignora ya que no hay más (j iba desde 1 a 2).

Así, el código llega a “Next i” y así aumenta el valor de i en 1 y vuelve a “For i” y por lo tanto se vuelven a contar j desde 1. Ahora con i=2 y j=1, VBA escribe en la celda (2,1) el valor 100+2+1. De esta manera sigue recorriendo toda la lista hasta i=2 y j=6.

Al ejecutar esta macro se obtiene:

Ejemplo de For Loop doble en VBA de Excel
  1. Loop de rango definido. Si queremos que el loop de “For” recorra distintas celdas de un rango se usa “For Each” + “In”. Así se dice “Para cada celda dentro de lo seleccionado. Podemos revisar el ejercicio 4 de la sección de Rangos Simples y entender el condicional y loop:
Sub RangoHastaUltimaEntrada()

'Se crean las variables
Dim maximo As Double, rango As Range, celda As Range

'Se define rango como todas las celdas entre la activa y la última rellena hasta abajo
Set rango = Range(ActiveCell, ActiveCell.End(xlDown))

'Se ponen todos en color blanco
rango.Interior.ColorIndex = 0

'Se define maximo como el valor máximo de las celdas de rango
maximo = WorksheetFunction.Max(rango)

'Para cada celda en rango
For Each celda In rango

'Si es que su valor es el máximo, entonces píntala roja
If celda.Value = maximo Then celda.Interior.ColorIndex = 22

'Se va a la siguiente celda
Next celda

End Sub

Si por ejemplo teníamos esto:

Ejemplo FOR - IF - Cambio color en VBA de Excel

Al ejecutar la macro resulta:

Ejemplo FOR - IF - Cambio color en VBA de Excel, ejecutado

Tip Ninja: puedes usar las mismas funciones que usar en Excel como la función MAX escribiendo “WorksheetFunction.” y luego la función. En este ejemplo se usó WorksheetFunction.Max(rango)

  1. Ahora hagamos la misma aplicación anterior pero usando rango hasta la última celda. La única diferencia está en cómo definimos el rango.
Sub RangoHastaUltimaEntrada3()

'Se crean las variables
Dim maximo As Double, rango As Range, celda As Range

'Se define la variable rango como todo desde la celda seleccionada hasta el final
Set rango = Range(ActiveCell, ActiveCell.End(xlDown))

'Luego a todo el rango se rellena de blanco
rango.Interior.ColorIndex = 0

'Se define maximo como el valor máximo de lo seleccionado
maximo = WorksheetFunction.Max(rango)

'En lo siguiente se define: “si el valor de la celda es el máximo, rellénalo de color rojo”
For Each celda In rango
If celda.Value = maximo Then celda.Interior.ColorIndex = 22
Next celda

End Sub

Así, tenemos seleccionado C3 al ejecutar la macro, el resultado es el mismo:

Otro Ejemplo FOR- IF - Cambio color, sin ejecutar

Si es que se ejecuta la macro teniendo C5 seleccionado se obtiene:

Otro Ejemplo FOR- IF - Cambio color, ejecutado

Ejemplo aplicando todo lo aprendido

En este ejemplo haremos una calculadora de total de ventas para un empleado. Revisa el siguiente código de VBA:

Sub Calculadora_Ventas()

Dim empleado As String, total As Double, i As Integer
empleado = InputBox("Ingrese el nombre del empleado (con mayúsculas y minúsculas)")
total = 0

For i = 3 To 7
If Cells(i, 2).Value = empleado Then
total = total + Cells(i, 3).Value
End If
Next i

MsgBox "Total sales of " & empleado & " is " & total

End Sub

La idea es que el loop de “For” recorra las celdas desde (3,2) hasta (7,2), es decir, desde B3 hasta B7, viendo si el nombre del empleado es que el corresponde al que se ingresó en la ventana. En caso de ser el empleado, entonces a la variable “total” le suma el valor de las ventas de ese empleado. Después de recorrer todas las celdas, aparece una ventana informando las ventas del empleado.

En este caso, al ejecutar la macro se aparece la ventana:

Ejemplo final aplicando todo - Calculadora de Ventas: MsgBox

Si en la ventana se escribe “Benjamín”, entonces se obtiene:

Resultado del ejemplo final, ejecutado.

Complementar una macro grabada con Visual Basic (VBA)

En el siguiente video puedes ver un ejemplo de cómo se creó una macro con la grabadora de macros y se complementa con Visual Basic (VBA) para lograr lo que se busca.

En este video se crea la macro usando la “Grabadora de Macros”:

Y en este video se modifica con VBA:

Ahora que ya entendemos VBA avancemos…

Lo básico sobre Loops en VBA

Finalidad: Ahorrar tiempo y evitar errores al hacer una macro de Excel que involucra hacer lo mismo varias veces. En vez de escribirlo muchas veces, se hace con un loop para que se haga automáticamente. El loop más común es el que usa “For Next”.

Estructura “For Next” Loop:

For contador = comienzo to final

Código

Next contador

Argumentos:

“For”: significa “Para”, queriendo decir que “para esos valores” se repita el código.

“To”: significa “Hasta”. Indica hasta qué valor toma el contador.

“Next”: significa “Siguiente”, cuando se llega a esta línea se redirige de nuevo a “For” para ejecutar todo de nuevo hasta terminar la lista.

Contador: es una variable que en general la definiremos como un número y le puedes poner el nombre que quieras. En este artículo usaremos “i”, pero puedes ponerle “atún” o el nombre que quieras.

Loops Simples

El loop de VBA recorre una lista de números y se ejecuta el código todas las veces que diga el loop. Revisa este ejemplo:

Sub Primer_Loop()

'Creamos la variable contador “i”
Dim i As Integer

'Decimos que i va a tomar los valores desde 1 hasta 6
For i = 1 To 6

'Este Código se va a ejecutar para cada valor de i
    Cells(i, 1).Value = 100 + i

'Vuelve hasta “For” hasta que se termine la enumeración
Next i

End Sub

En este primer loop de VBA, las líneas entre “For” y “Next” se van a repetir seis veces. El loop parte con i igual a 1. Así, VBA va a la Celda(1,1) y le asigna el valor 100 + 1. Cuando el código llega a “Next i”, va a hacer que i aumente en uno (i tomará el valor de 2) y vuelve a “For”. Luego, para i=2 VBA va a asignarle el valor de 100+2 a la Celda(2,1) y así sucesivamente hasta el 6. Recuerda que “i” que es el contador pudo haber tenido cualquier nombre, simpre y cuando en todo el código se use el mismo nombre para llamar a ese contador.

Al ejecutar esta macro se obtiene:

Primer Loop en VBA de Excel explicación

Tip Ninja: Acá para referirnos a las celdas usamos Cells(fila, columna). Así, Cells(1,1) es la celda A1 y Cells(2,3) es la celda C2.

Loops Dobles

Uno puede hacer la cantidad de loops que quiera, uno dentro del otro. Los “For” loops funcionan de manera secuencial: van desde afuera hacia adentro, y solo una vez que termina lo de adentro, vuelve a tomar lo que hay afuera.

Sub Loop_Doble()

Dim i As Integer, j As Integer

For i = 1 To 6
    For j = 1 To 2
        Cells(i, j).Value = 100 + i + j
    Next j
Next i

End Sub

La explicación es la siguiente:

  • En este ejemplo para i=1 y j=1 VBA va a poner en la celda (1,1) (que es A1) el valor de 100+1+1. Luego, el código llega a “Next j”, por lo que j aumenta en 1 y vuelve a “For j”.
  • Luego, con el valor i=1 y j=2 VBA va a poner en la celda(1,2) (que es B1) el valor 100+1+2. Después, el código llega a Next j, pero lo ignora ya que no hay más (j iba desde 1 a 2).
  • Así, el código llega a “Next i” y así aumenta el valor de i en 1 y vuelve a “For i” y por lo tanto se vuelven a contar j desde 1. Ahora con i=2 y j=1, VBA escribe en la celda (2,1) (que es A2) el valor 100+2+1. De esta manera sigue recorriendo toda la lista hasta i=2 y j=6.

Al ejecutar esta macro se obtiene:

Loop doble en VBA de Excel, ejemplo explicado

Do While Loops

Aparte de los loops “For Next”, hay otros tipos de loops en VBA que pueden ser útiles. Un ejemplo muy común es el “Do While” que, como lo dice su significado en inglés, se ejecutará el código del loop mientras se cumpla una condición que se establezca inicialmente.

Estructura Do-While Loop:

Do While  condición

Código

Loop

Argumentos:

“Do While”: significa “Hacer mientras”, queriendo decir que mientras se cumpla la condicion, va a hacer la acción del siguiente código.

“Loop”: Cierra el loop, es decir, marca que hasta ahí llega el código que se ejecuta si se cumple la condición. Al llegar al Loop, se vuelve a “Do While” todas las veces hasta que la condición no se cumpla.

Ve el siguiente ejemplo:

Sub DoWhile()

‘Se crea la variable i que es un entero
Dim i As Integer

‘Parte definida con el valor de 2
i = 2

‘Mientras i sea menor a 6.
Do While i < 6

‘Este Código se va a ejecutar para cada valor de i
    Cells(i, 3).Value = Cells(i,2).Value +i

‘Ahora se re-define i, como 1 más que el valor que tenía
    i = i + 1

‘Al llegar acá se vuelve a Do While hasta que no se cumpla la condición (i=6)
Loop

End Sub

La explicación es la siguiente:

  • Como podemos ver, i parte con el valor 2 al entrar al “Do While” loop y al cumplir la condición de ser menor a 6 (1<6), se ejecuta el código que está dentro del loop.
  • Este código quiera decir: la celda (2,3), es decir, C2 será igual a B2+2, ya que en este caso el valor de i es 2.
  • Luego viene la parte clave del loop: i se actualiza y toma el valor 3 (i=2+1=3).
  • Al llegar a “Loop”, VBA vuelve a “Do While” con i igual a 3. Como esto sigue siendo menor a 6, se ejecuta el código y la celda C3 será igual a B3+2 e i tomará el valor 3.
  • Así, sucesivamente hasta que i al tomar valor 5, define C5 =B5 + 5 e i toma el valor de 6. Al volver a “Do While”, ya no se cumple la condición: 6 no es menor a 6, por lo que termina el loop.

En la siguiente imagen podemos ver la situación inicial:

Situación inicial de ejemplo de Loop Do While en VBA de Excel

Luego correr el loop de VBA se obtiene:Resultado ejemplo de Loop Do While en VBA de Excel

Loops de rango definido

Dentro de los loops que usan el comando “For”, hemos visto que el loop “For Next” recorría una lista de valores. En caso de querer que el loop recorra distintas celdas de un rango (un rango es un conjunto de celdas) se usa “For In”.

Estructura For In Loop:

For celda In rango

Código

Next celda

Argumentos:

“For”: signfica “Para”, queriendo decir que “para esos valores” se repita el código.

“In”: significa “En”. Indica que la celda debe estar dentro del rango seleccionado para que se ejecute el código siguiente.

“Next”: significa “Siguiente”, cuando se llega a esta línea se redirige de nuevo a “For” para ejecutar todo de nuevo hasta que la siguiente celda no esté en el rango definido.

Sub RangoDefinido()

‘Se crean las variables rango y celda como un rango de celdas
Dim rango As Range, celda As Range

‘Se define rango desde B2 hasta B6
Set rango = Range("B2:B6")

‘Para cada celda en el rango se ejecuta el el código de abajo
For Each celda In rango
   celda.Value = celda.Value / 1000

‘Se pasa a la siguiente celda y vuelve a “For Each” hasta que la celda no esté en rango
Next celda

End Sub

En este código primero se define el rango, que es desde B2 hasta B6 y luego para todas las celdas de ese rango (que no tienen por qué ser un rango de una misma columna) se dividan en 1.000.

Si es que partimos con lo siguiente:

Situación inicial antes de ejecutar loop de rango definido en VBA de Excel

Luego de ejecutar el loop de VBA:Resultado luego de ejecutar loop de rango definido en VBA de Excel

Nota que sólo se va a ejecutar hasta la celda B6 porque eso pusimos en “Set rango = Range(“B2:B6”)”. Si es que nuestra planilla de Excel lucía así en un principio:

Error común en loop de rango definido en VBA de Excel

Y corremos el mismo código, va a llegar sólo hasta B6:

Resultado del error común en loop de rango definido en VBA de Excel

Tip Ninja: si quieres hacer que este loop recorra las celdas que tienes seleccionadas, puedes cambiar la línea de Set rango por la siguiente:

Set rango = Selection

Tip Ninja: si quieres hacer que este loop recorra las celdas desde la seleccionada hasta la última celda rellenada hacia abajo, puedes cambiar la línea de Set rango por la siguiente:

Set rango = Range(ActiveCell, ActiveCell.End(xlDown))

Loops para toda la columna

Si tienes el objetivo de usar el mismo código para todas las filas de una columna (hasta el final de Excel), puedes usar “For Next” para ejecutarlo.

Sub ColumnaLoop()

‘Se crea la variable i como número.
Dim i As Long

‘Para valores desde i hasta la cantidad de filas que hay en Excel se ejectua el código de abajo
For i = 2 To Rows.Count

‘Para la celda si es que su valor es mayor a D3, entonces ponerle color azul.
    If Cells(i, 1).Value > Range("D3").Value Then Cells(i, 1).Font.Color = vbBlue

‘Vuelve hasta “For” hasta que se termine la enumeración
Next i

End Sub

Tip Ninja: se define i como “Long” para definir a un número que puede tomar valores muy altos, como el es caso de la cantidad de filas que tiene Excel. 

En este código el contador (i) toma los valores desde 1 hasta el total de filas que hay en Excel (Rows.Count). Luego, el código que se ejecuta para esas celdas usa el comando “If”, que es un código condicional. Puedes ver lo básico del comando If en este artículo.

Si antes teníamos lo siguiente:

Situación inicial el loop columna completa en VBA de Excel
Situación inicial el loop columna completa en VBA de Excel, continuación

Y luego de ejecutar la macro obtenemos:Resultado del loop columna completa en VBA de ExcelResultado del loop columna completa en VBA de Excel, continuación

Tip Ninja:Para hacer un loop de VBA para toda una fila usa Columns.Count para el límite del contador y usa Cells(1,i) (en el caso de la fila 1).

Loops con “Step”

Puede ser que quieras recorrer una lista de números pero no quieres ir de uno en uno, sino que quieres que ese código se ejecute sólo para los impares o cada tres números. Para eso, vamos a usar igual “For Next” y le agregaremos el comando “Step” que significa “Paso”, es decir, la cantidad de pasos que da cada vez que llega a “Next”. El valor por defecto de Step es 1, es decir, va recorriendo uno a uno los valores del contador. Revisa el siguiente ejemplo:

Sub StepLoop()

‘Se crea la variable i como número entero
Dim i As Integer

‘Acá el loop recorre valores desde 1 a 8, pero de dos en dos.
For i = 1 To 8 Step 2
    Cells(2, i).Value = 10
Next i

End Sub

El código funciona de la siguiente manera:

  • Vemos que i comienza con el valor de 1 y va a ir de dos en dos hasta el valor 8.
  • Así, i comienza con el valor 1 y luego de dejar la celda A2 igual a 10, VBA llega a “Next i” e i toma el valor de 3 ya que el valor de “Step” es 2 (1+2=3).
  • Luego, la celda C2 toma el valor de 10 y al llegar a “Next i”, i toma el valor de 5 (3+2) y así sigue sucesivamente.
  • Finalmente, cuando i toma el valor 7 y se ejecuta quela celda G2 sea igual a 10, al llegar a “Next i”, esta toma el valor de 9, saliéndose de los valores que puede tomar i dentro del loop y, por lo tanto, se termina de ejecutar el loop.

Al ejecutar el loop de VBA se obtiene:

Ejemplo Loop con Step en VBA de Excel

Tip Ninja: También puedes hacer que la lista vaya “hacia atrás” haciendo que i tome valores de uno mayor a otro menor y poniendo pasos negativos, con en el siguiente código:

Sub StepNegLoop()

Dim i As Integer
For i = 8 To 3 Step -1
    Cells(6, i).Value = 50
Next i

End Sub

Ejemplo aplicado

En este ejemplo usaremos un loop para marcar en rojo el máximo de asistencia en un curso. Ve el siguiente código:

Sub RangoMaximoPuntaje()

‘Se crean las variables
Dim maximo As Double, rango As Range, celda As Range

‘Se define la variable rango como todo desde la celda seleccionada hasta el final
Set rango = Range(ActiveCell, ActiveCell.End(xlDown))

‘Luego, a todo el rango se rellena de blanco
rango.Interior.ColorIndex = 0

‘Se define maximo como el valor máximo de lo seleccionado
maximo = WorksheetFunction.Max(rango)

‘En lo siguiente se define: “si el valor de la celda es el máximo, rellénalo de color rojo”
For Each celda In rango
    If celda.Value = maximo Then celda.Interior.ColorIndex = 22
Next celda

End Sub

Tip Ninja: puedes usar las mismas funciones que usas en Excel como la función MAX escribiendo “WorksheetFunction.” y luego la función. En este ejemplo se usó WorksheetFunction.Max(rango)

Así, si se ejectua la macro teniendo seleccionada la celda C3, se revisa hasta C7 y se marca el máximo:

Ejemplo de aplicación de Loops de VBA en Excel. Encontrar y marcar el máximo.

Si es que se ejecuta la macro teniendo C5 seleccionado se obtiene, ya que parte desde C5 hasta C7 buscando el máximo:

Segundo ejemplo aplicado de Loops en VBA de Excel.
Logo ninja excel blanco

La plataforma líder de capacitación en Excel para empresas.

Compañía

Copyrigth © 2022 Ninja Excel.