Comprender las celdas de Excel frente a las funciones de rango en VBA

Excel es poderoso. Si lo usa mucho, probablemente ya conozca muchos trucos usando fórmulas o autoformatos, pero haciendo uso de Células y Distancia funciones en VBA, puede impulsar sus análisis de Excel a un nivel completamente nuevo.

El problema con el uso de las funciones de Celdas y Rango en VBA es que en los niveles avanzados, la mayoría de las personas tienen dificultades para comprender cómo funcionan realmente estas funciones. Usarlos puede ser muy confuso. Así es como puedes usarlos de una manera que probablemente nunca imaginaste.

La función de las células

Las funciones de Celdas y Rango le permiten decirle a su script de VBA 4 errores que puede evitar al programar macros de Excel con VBA 4 errores que puede evitar al programar macros de Excel con VBA El código simple y las macros son las claves de las superpotencias de Microsoft Excel. Incluso los no programadores pueden agregar fácilmente una funcionalidad impresionante a sus hojas de cálculo con Virtual Basics for Applications (VBA). ¡Solo evite estos errores de programación para principiantes! exactamente en qué parte de su hoja de trabajo desea obtener o colocar datos. La principal diferencia entre las dos celdas es a qué se refieren.

Células generalmente hace referencia a una sola celda a la vez, mientras que Distancia hace referencia a un grupo de células a la vez. El formato para esta función es Celdas (fila, columna).

Esto hace referencia a cada celda de la hoja completa. Es el único ejemplo en el que la función Celdas no hace referencia a una sola celda:

Hojas de trabajo ("Hoja1"). Celdas

Esto hace referencia a la tercera celda desde la izquierda, de la fila superior. Celda C1:

Hojas de trabajo ("Hoja1"). Celdas (3)

El siguiente código hace referencia a la celda D15:

Hojas de trabajo ("Hoja1"). Celdas (15,4)

Si quisiera, también podría hacer referencia a la celda D15 con “Celdas (15,”re”)”-puedes usar la letra de la columna.

Hay mucha flexibilidad para poder hacer referencia a una celda usando un número para columna y celda, especialmente con scripts que pueden recorrer una gran cantidad de celdas (y realizar cálculos sobre ellas) muy rápidamente. Llegaremos a eso con más detalle a continuación..

La función de rango

En muchos sentidos, la función Rango es mucho más poderosa que usar Celdas, porque le permite hacer referencia a una sola celda o a un rango específico de celdas, todo a la vez. No va a querer recorrer una función Range, porque las referencias para las celdas no son números (a menos que inserte la función Celdas dentro de ella).

El formato para esta función es Rango (Celda # 1, Celda # 2). Cada celda puede ser designada por un número de letra.

Veamos algunos ejemplos..

Aquí, la función de rango hace referencia a la celda A5:

Hojas de trabajo ("Hoja1"). Rango ("A5")

Aquí, la función de rango hace referencia a todas las celdas entre A1 y E20:

Hojas de trabajo ("Hoja1"). Rango ("A1: E20")

Como se mencionó anteriormente, no tiene que usar asignaciones de celdas de letras y números. En realidad, podría usar dos funciones de Celdas dentro de una función de Rango para identificar un rango en la hoja, de esta manera:

 Con hojas de trabajo ("Sheet1"). Rango (.Cells (1, 1), _ .Cells (20, 5)) Finalizar con 

El código anterior hace referencia al mismo rango que el Rango (“A1: E20”) la función sí. El valor de usarlo es que le permitiría escribir código que funcione dinámicamente con rangos usando bucles Cómo funcionan los bucles Do-While en la programación de computadoras Cómo funcionan los bucles Do-While en la programación de computadoras Los bucles son uno de los primeros tipos de control que usted ' Aprenderé en programación. Probablemente conozca los bucles while y for, pero ¿qué logra un bucle do-while?? .

Ahora que comprende cómo formatear las funciones de Celdas y Rango, veamos cómo puede hacer un uso creativo de estas funciones en su código VBA.

Procesamiento de datos con función de celdas

La función Celdas es más útil cuando tiene una fórmula compleja que desea realizar en múltiples rangos de celdas. Estos rangos también pueden existir en varias hojas.

Tomemos un ejemplo simple. Supongamos que gestiona un equipo de ventas de 11 personas, y cada mes desea ver su desempeño.

Es posible que usted tenga Hoja1 que rastrea su recuento de ventas y su volumen de ventas.

En Hoja2 es donde realiza un seguimiento de su calificación de comentarios de los últimos 30 días de los clientes de su empresa.

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

Una alternativa a esto es crear un script VBA que puede activar para ejecutar cada vez que abre la hoja, o mediante un botón de comando en la hoja para que pueda controlar cuándo se calcula. De todos modos, puede usar un script VBA para obtener todos los datos de ventas de un archivo externo.

Entonces, ¿por qué no simplemente desencadenar los cálculos para la columna de bonificación en el mismo script en ese momento??

La función de las células en acción

Si nunca antes ha escrito VBA en Excel, deberá habilitar el elemento del menú Desarrollador. Para hacer esto, vaya a Expediente > Opciones. Haga clic en Personalizar la cinta. Finalmente, elija Desarrollador en el panel izquierdo., Añadir hacia el panel derecho y asegúrese de que la casilla de verificación esté seleccionada.

Ahora, cuando haces clic Okay y regrese a la hoja principal, verá la opción del menú Desarrollador.

Puedes usar el Insertar menú para insertar un botón de comando, o simplemente haga clic Ver código para comenzar a codificar.

En este ejemplo, haremos que el script se ejecute cada vez que se abra el libro. Para hacer esto, solo haga clic Ver código desde el menú del desarrollador y pegue la siguiente función nueva en la ventana de código.

Private Sub Workbook_Open () End Sub

Su ventana de código se verá así.

Ahora está listo para escribir el código para manejar el cálculo. Con un solo bucle, puede pasar por los 11 empleados y, con la función Celdas, extraer las tres variables necesarias para el cálculo.

Recuerde que la función Celdas tiene filas y columnas como parámetros para identificar cada celda individual. Haremos “X” la fila, use 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 columna será 2 para el recuento de ventas, 3 para el volumen de ventas y 2 de la hoja 2 para el puntaje de comentarios.

El cálculo final utiliza los siguientes porcentajes para sumar hasta el 100 por ciento del puntaje de bonificación total. Se basa en un recuento de ventas ideal de 50, un volumen de ventas de $ 50,000 y un puntaje de retroalimentación de 10.

• (Recuento de ventas / 50) x 0,4
• (Volumen de ventas / 50,000) x 0.5
• (Puntuación de retroalimentación / 10) x 0.1

Este enfoque simple ofrece a los empleados de ventas una bonificación ponderada. Con un recuento de 50, un volumen de $ 50,000 y un puntaje de 10, obtienen el bono máximo completo del mes. Sin embargo, cualquier cosa que no sea perfecta en cualquier factor reduce la bonificación. Cualquier cosa mejor que ideal aumenta la bonificación.

Ahora veamos cómo se puede lograr toda esa lógica en un script VBA corto y muy simple:

Private Sub Workbook_Open () Para x = 2 a 12 hojas de trabajo ("Hoja1"). Celdas (x, 4) = (Hojas de trabajo ("Hoja1"). Celdas (x, 2). Valor / 50) * 0.4 _ + (Hojas de trabajo ("Hoja1"). Celdas (x, 3) .Valor / 50000) * 0.5 _ + (Hojas de trabajo ("Hoja2"). Celdas (x, 2) .Valor / 10) * 0.1 _ Siguiente x Fin Sub 

Así es como se verá la salida de este script.

Si desea que la columna Bono muestre el bono en dólares real en lugar del porcentaje, puede multiplicarlo por el monto máximo del bono. Mejor aún, coloque esa cantidad en una celda en otra hoja y haga referencia a ella en su código. Esto facilitaría cambiar el valor más adelante sin tener que editar su código.

La belleza de la función Celdas es que puedes construir una lógica bastante creativa para extraer datos de muchas celdas a través de muchas hojas de diferencia, y realice algunos cálculos bastante complejos con ellas.

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

Para explorar todo lo que puede hacer más, consulte la página de Microsoft MSDN para el objeto Celdas.

Formateo de celdas con función de rango

Para recorrer muchas celdas una a la vez, la función Celdas es perfecta. Pero si desea aplicar algo a un rango completo de celdas a la vez, la función Rango es mucho más eficiente.

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

Por ejemplo, supongamos que si el recuento de todos los volúmenes de ventas de todos los empleados de ventas supera los $ 400,000 en total, desea resaltar todas las celdas en la columna de bonificación en verde para indicar que el equipo ha obtenido una bonificación adicional..

Echemos un vistazo a cómo puede hacer eso con una declaración IF Cómo usar declaraciones IF en Microsoft Excel Cómo usar declaraciones IF en Microsoft Excel Ya sea que sea un experto experimentado o un principiante en hojas de cálculo, querrá consultar esto guía de declaraciones IF en Excel. .

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 ejecuta, si la celda está por encima del objetivo del equipo, todas las celdas en el rango se llenarán de verde.

Este es solo un ejemplo simple de las muchas acciones que puede realizar en grupos de celdas utilizando la función Rango. Otras cosas que puede hacer incluyen:

• Aplicar un esquema alrededor del grupo
• Verifique la ortografía del texto dentro de un rango de celdas
• Borrar, copiar o cortar celdas
• Busque en un rango con el “Encontrar” método
• Mucho más

Asegúrese de leer la página de Microsoft MSDN para el objeto Range para ver todas las posibilidades.

Lleva Excel al siguiente nivel

Ahora que comprende las diferencias entre las celdas y las funciones de rango, es hora de llevar sus secuencias de comandos VBA al siguiente nivel. El artículo de Dann sobre el uso de las funciones de conteo y adición en Excel le permitirá crear scripts aún más avanzados que pueden acumular valores en todos sus conjuntos de datos muy rápidamente..

Y si recién está comenzando con VBA en Excel, no olvide que tenemos una fantástica guía introductoria para Excel VBA. Tutorial de programación de Excel VBA para principiantes. Tutorial de programación de Excel VBA para principiantes. VBA es una herramienta poderosa de Microsoft Office. Puede usarlo para automatizar tareas con macros, establecer activadores y mucho más. Le presentaremos la programación visual básica de Excel con un proyecto simple. para ti también.