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”.

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”.

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

2. 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.
3. 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”.
4. Ahora, en el Explorador de Proyectos aparece Módulo 1. En este módulo puedes escribir tus macros.

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.

5. 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”.

6. ¡Listo! Ya está creada la macro. Puedes cerrar el editor de VBA.
7. Para ejecutarla ve al menú de Macros, selecciona la macro y haz click “Aceptar”.
8. Se esta manera se obtiene:

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.

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

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:

2. 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: 

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:

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

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:

2. 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:

3. 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:

4. 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:

5. 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:

Luego de ejecutar la macro obtenemos:

6. 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:

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:

Luego de ejecutar la macro resulta:

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:

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

2. 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:

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

3. 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:

Si se ejecuta la macro teniendo la celda C5 seleccionada se obtiene:

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:

2. 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:

Al ejecutar el código de VBA resulta:

3. 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:

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:

Al ejecutar la macro obtenemos:

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:

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.

2. 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:

Y en este caso:

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:

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

2. 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:

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

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

3. 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:

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

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.

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:

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.

2. 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:

3. 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:

Al ejecutar la macro resulta:

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:

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

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:

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

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:

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:

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:

Luego correr el loop de VBA se obtiene:

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:

Luego de ejecutar el loop de VBA:

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:

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

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:

Y luego de ejecutar la macro obtenemos:

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:

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:

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