Excel es potente. Si lo usas mucho, probablemente ya conozcas un montón de trucos usando fórmulas o autoformateo, pero haciendo uso de la función Rangos y Celdas en VBA, puedes llevar tus análisis de Excel a un nivel completamente nuevo.

El problema de utilizar las funciones Celdas y Rangos en las macros de VBA es que, en los niveles avanzados, a la mayoría de la gente le cuesta entender cómo funcionan realmente estas funciones. Utilizarlas puede resultar muy confuso. A continuación te explicamos cómo puedes utilizarlas de una forma que probablemente nunca habías imaginado.


Función de las celdas de Visual Basic

Las funciones Celdas y Rango te permiten indicar a tu script VBA exactamente en qué lugar de tu hoja de cálculo quieres obtener o colocar los datos. La principal diferencia entre las dos celdas es a qué hacen referencia.

La función Celdas de VBA suele hacer referencia a una sola celda cada vez, mientras que Rango hace referencia a un grupo de celdas a la vez.

Este es el formato de la función celdas

Cells(row, column)

Esto hace referencia a cada una de las celdas de toda la hoja. Este es uno de los ejemplos en los que la función celdas de la macro VBA no hace referencia a una sola celda:

Worksheets("Sheet1").Cells

Por el contrario, este trozo de código hace referencia a la tercera celda desde la izquierda, en la fila número uno, es decir Celda C1:

Worksheets("Sheet1").Cells(3)

Aventurémonos un poco, remitiendo a la célula D15 utilizando la función de celdas de VBA:

Worksheets("Sheet1").Cells(15,4)

Si quisieras, también podrías hacer referencia a la celda D15 con el siguiente código VBA de la función celdas

Cells(15,"D")"

—puedes utilizar la letra de la columna.

Hay mucha flexibilidad al utilizar la función de celda de VBA, ya que puedes hacer referencia a una celda utilizando un número para la columna y la celda, especialmente con scripts que pueden hacer un bucle a través de muchas celdas (y realizar cálculos en ellas) muy rápidamente

La función Rango

En muchos sentidos, la función Rango es mucho más potente que el uso de Celdas, porque te permite hacer referencia a una sola celda o a un rango específico de celdas, todo a la vez. No vas a querer hacer un bucle a través de una función Rango, porque las referencias de las celdas no son números (a menos que incrustes la función de celdas de VBA Excel dentro de ella).


El formato de esta función es el siguiente

Range(Cell #1,Cell #2)

Cada celda puede designarse con un número de letra. Veamos algunos ejemplos.

El siguiente código es una función de rango que hace referencia a la celda A5:

Worksheets("Sheet1").Range("A5")

En este caso, la función rango hace referencia a todas las celdas entre A1 y E20:

Worksheets("Sheet1").Range("A1:E20")

Como se ha mencionado anteriormente, no tienes que utilizar asignaciones de celdas con números y letras. De hecho, puedes utilizar dos funciones de celda VBA dentro de una función Rango para identificar un rango en la hoja, de esta manera:


With Worksheets("Sheet1")
 .Range(.Cells(1, 1), _ 
 .Cells(20, 5))
End With

Este código hace referencia al mismo rango que Rango(«A1:E20») hace la función. El valor de utilizarla es que te permitiría escribir código que trabaje dinámicamente con rangos utilizando bucles.

Ahora que ya sabes cómo dar formato a las funciones Celdas y Rango, vamos a sumergirnos en cómo puedes hacer un uso creativo de estas funciones en tu código VBA.

Procesar datos con la función Celdas

La función Celdas de Visual Basic es bastante útil cuando tienes una fórmula compleja que quieres utilizar en varios rangos de celdas. Estos rangos también pueden existir en varias hojas.

Pongamos un ejemplo sencillo. Supongamos que diriges un equipo de ventas de 11 personas, y cada mes quieres ver su rendimiento.

Puede que tengas Hoja1 que hace un seguimiento de su recuento de ventas y de su volumen de ventas.

En Hoja2, tienes datos para hacer un seguimiento de su valoración de los últimos 30 días de los clientes de tu empresa.

Si quieres calcular la bonificación en la primera hoja utilizando los valores de las dos hojas, hay varias formas de hacerlo. Puedes escribir una fórmula en la primera celda que realice el cálculo utilizando los datos de las dos hojas y arrastrarla hacia abajo. Eso funcionará.

Una alternativa a esto es crear un script VBA que se ejecute cada vez que abras la hoja, o que lo actives mediante un botón de comando en la hoja, de modo que puedas controlar cuándo se calcula. De todos modos, podrías utilizar un script VBA para extraer todos los datos de ventas de un archivo externo.

Entonces, ¿por qué no desencadenar los cálculos de la columna de bonos en el mismo script en ese momento?

Función de macroceldas de Excel en acción

Si nunca has escrito código VBA en Excel, tendrás que activar la función Desarrollador elemento del menú. Para ello, ve a Archivo > Opciones. Haz clic en Personalizar la cinta. Por último, elige Desarrollador del panel izquierdo, Añade al panel derecho, y asegúrate de que la casilla está seleccionada.

Ahora, cuando hagas clic en OK y vuelve a la hoja principal, verás la opción del menú Desarrollador.

Puedes utilizar la opción Insertar para insertar un botón de comando, o simplemente haz clic en Ver código para empezar a codificar.

En este ejemplo, configurarás el script para que se ejecute cada vez que se abra el libro de trabajo. Para ello, sólo tienes que hacer clic en Ver Código en el menú del desarrollador, y pega la siguiente función nueva en la ventana de código.

Private Sub Workbook_Open() 
End Sub

Tu ventana de código tendrá el siguiente aspecto

Ahora estás preparado para escribir el código que se encargará del cálculo. Utilizando un único bucle, puedes recorrer los 11 empleados, y con la función de celdas de VBA de Excel, sacar las tres variables necesarias para el cálculo.

Recuerda el hojas de trabajo.celdas tiene como parámetros la fila y la columna para identificar cada celda individual. Tienes que sustituir x con la referencia de la fila y utiliza un número para solicitar los datos de cada columna. El número de filas es el número de empleados, por lo que será de 1 a 11. El identificador de la columna será el 2 para el Recuento de ventas, el 3 para el Volumen de ventas y el 2 de la Hoja 2 para la Puntuación de retroalimentación.


El cálculo final utiliza los siguientes porcentajes para sumar el 100% de la puntuación total de la bonificación. Se basa en un recuento de ventas ideal de 50, un volumen de ventas de 50.000 dólares y una puntuación de retroalimentación de 10.

  • (Sales Count/50) x 0.4
  • (Sales Volume/50,000) x 0.5
  • (Feedback Score/10) x 0.1

Este sencillo enfoque da a los empleados de ventas una bonificación ponderada. Para un recuento de 50, un volumen de 50.000 dólares y una puntuación de 10, obtienen toda la bonificación máxima del mes. Sin embargo, cualquier cosa por debajo de lo perfecto en cualquier factor reduce la bonificación. Todo lo que sea mejor que lo ideal aumenta la bonificación.

Ahora veamos cómo se puede llevar a cabo toda esa lógica en un sencillo y breve script VBA:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _
 + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _
 + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

Este es el aspecto que tendrá la salida de este script.

Si quieres que la columna de la bonificación muestre la bonificación real en dólares en lugar del porcentaje, puedes multiplicarla por el importe máximo de la bonificación. Mejor aún, coloca esa cantidad en una celda de otra hoja y haz referencia a ella en tu código. Así sería más fácil cambiar el valor más tarde sin tener que editar tu código.

La belleza del método de las celdas en VBA es que puedes construir una lógica bastante creativa para extraer datos de muchas celdas en muchas hojas y realizar algunos cálculos bastante complejos con ellos.

Puedes realizar todo tipo de acciones en las celdas utilizando la función Celdas: cosas como borrar las celdas, cambiar el formato de la fuente y mucho más.

Para explorar más a fondo todo lo que puedes hacer, consulta la página Página de Microsoft MSDN para el objeto Celdas.

Formatear celdas con la función de rango

Para recorrer muchas celdas de una en una, la función de celdas es perfecta. Pero si quieres aplicar algo a todo un rango de celdas a la vez, la función Rango es mucho más eficiente. En la actual comparación entre rango y celdas de VBA, hay mucho que esperar.

Un caso de uso para esto podría ser formatear un rango de celdas mediante un script si se cumplen ciertas condiciones.

Por ejemplo, digamos que si el recuento de todo el volumen de ventas de todos los empleados de ventas supera los 400.000 dólares en total, quieres resaltar todas las celdas de la columna de bonificación en verde para indicar que el equipo ha ganado una bonificación extra de equipo.

Veamos cómo puedes hacerlo con una sentencia IF.

Private Sub Workbook_Open()
If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then
 ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4
End If
End Sub

Cuando esto se ejecute, si la celda está por encima del objetivo del equipo, todas las celdas del rango se rellenarán en verde.

Éste es sólo un ejemplo sencillo de las muchas acciones que puedes realizar sobre grupos de celdas utilizando la función Rango. Otras cosas que puedes hacer son

  • Aplicar un contorno alrededor del grupo
  • Comprobar la ortografía del texto dentro de un rango de celdas
  • Borrar, copiar o cortar celdas
  • Busca en un rango con el botón Buscar método

Asegúrate de leer el Página de Microsoft MSDN del objeto Rango para ver todas las posibilidades.

Lleva a Excel al siguiente nivel

Ahora que entiendes las diferencias entre las funciones Celdas y Rango, es hora de llevar tus scripts VBA al siguiente nivel. El artículo de Dann sobre el uso de las funciones Contar y Sumar en Excel te permitirá construir scripts aún más avanzados que pueden acumular valores en todos tus conjuntos de datos muy rápidamente.