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

Excel es poderoso. Si lo usas mucho, probablemente ya conozcas muchos trucos usando fórmulas o autoformatear, pero haciendo uso de Células y Distancia En VBA, puede aumentar sus analíticas de Excel a un nivel completamente nuevo..

El problema con el uso de las funciones Cells and Range en VBA es que, en los niveles avanzados, a la mayoría de las personas les cuesta entender cómo funcionan realmente estas funciones. Su uso puede ser muy confuso. Aquí le mostramos cómo puede usarlos de maneras que probablemente nunca imaginó..

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 y las macros simples son las claves de los superpoderes de Microsoft Excel. Incluso los que no son programadores pueden agregar fácilmente una funcionalidad impresionante a sus hojas de cálculo con los conceptos básicos virtuales para aplicaciones (VBA). ¡Evita estos errores de principiante de programación! Lea más exactamente en qué lugar de la hoja de trabajo desea obtener, o coloque los datos. La principal diferencia entre las dos celdas es lo que hacen referencia..

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

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

Hojas de trabajo ("Sheet1"). Células

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 lo desea, también puede hacer referencia a la celda D15 con “Células (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 bucle a través un gran número de celdas (y realizar cálculos en ellas) muy rápidamente. Vamos a llegar a eso con más detalle a continuación.

La función de rango

En muchos sentidos, la función de Rango es mucho más poderosa que el uso de Celdas, porque le permite hacer referencia a una sola celda o un rango específico de celdas, todo al mismo tiempo. No querrá recorrer una función de Rango, porque las referencias de las celdas no son números (a menos que incruste la función Celdas).

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 números y letras. En realidad, podría usar dos funciones de Celdas dentro de una función de Rango para identificar un rango en la hoja, como esto:

 Con hojas de trabajo ("Hoja1"). Rango (.Células (1, 1), _ .Células (20, 5)) Terminar con 

El código anterior hace referencia al mismo rango que el rango (“A1: E20”) la función hace. El valor al usarlo, es que le permitiría escribir código que funcione dinámicamente con rangos usando bucles Cómo funcionan los loops en la programación de la computadora Cómo funcionan los bucles en la programación de la computadora Los bucles son uno de los primeros tipos de control que utiliza. Aprenderé en programación. Probablemente sepa sobre el tiempo y para los bucles, pero ¿qué hace un bucle do-while? Lee mas .

Ahora que entiende 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..

Procesando 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 múltiples hojas.

Tomemos un ejemplo simple. Digamos que gestionas un equipo de ventas de 11 personas y cada mes quieres ver su rendimiento.

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 clientes de su compañía durante los últimos 30 días.

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

Una alternativa a esto es crear una secuencia de comandos de VBA que puede activar cada vez que abra la hoja, o activada por un botón de comando en la hoja para que pueda controlar cuándo se calcula. Puede utilizar un script VBA para extraer todos los datos de ventas de un archivo externo de todos modos.

Entonces, ¿por qué no solo activar 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 ha escrito VBA en Excel anteriormente, deberá habilitar el elemento del menú Desarrollador. Para hacer esto, ve a Expediente > Opciones. Haga clic en Personalizar la cinta. Por último, elija desarrollador en el panel izquierdo, Añadir en el panel derecho y asegúrese de que la casilla de verificación esté seleccionada.

Ahora, cuando haces clic DE ACUERDO y vuelva 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 en Ver código para comenzar a codificar.

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

Private Sub Workbook_Open () End Sub

La ventana de tu código se verá así..

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

Recuerde que la función Celdas tiene una fila y una columna 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 la puntuación de comentarios.

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

• (Recuento de ventas / 50) x 0.4
• (Volumen de ventas / 50,000) x 0.5
• (Puntuación de votos / 10) x 0.1

Este enfoque simple le da a los empleados de ventas un bono ponderado. Para un recuento de 50, un volumen de $ 50,000 y una puntuación de 10, obtienen el bono máximo total para el mes. Sin embargo, cualquier cosa bajo perfecto en cualquier factor reduce la bonificación. Cualquier cosa mejor que el ideal aumenta la bonificación..

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

Private Sub Workbook_Open () Para x = 2 a 12 hojas de trabajo ("Sheet1"). Celdas (x, 4) = (Hojas de trabajo ("Sheet1"). Cells (x, 2) .Value / 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 Bonificación muestre la bonificación real en dólares en lugar del porcentaje, puede multiplicarla por la cantidad máxima de bonificación. Mejor aún, coloque esa cantidad en una celda en otra hoja y cámbiela en su código. Esto haría más fácil cambiar el valor más adelante sin tener que editar su código.

La belleza de la función Celdas es que puede crear una lógica bastante creativa para extraer datos de muchas celulas a través de muchas hojas diferentes, y realiza algunos cálculos bastante complejos con ellos.

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

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

Formato de celdas con función de rango

Para recorrer varias celdas de una en una, la función Células es perfecta. Pero si desea aplicar algo a un rango completo de celdas de una vez, la función Range es mucho más eficiente..

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

Por ejemplo, digamos que si el recuento de todo el volumen de ventas en todos los empleados de ventas supera los $ 400,000 en total, desea resaltar en verde todas las celdas de la columna de bonos para indicar que el equipo ha obtenido un bono extra por equipo..

Veamos cómo puede hacerlo con una declaración IF. Cómo usar declaraciones IF en Microsoft Excel. Cómo usar declaraciones IF en Microsoft Excel. Si es un experto experimentado o un principiante en hojas de cálculo, querrá ver esto. Guía de declaraciones IF en Excel. Lee mas .

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á sobre la meta del equipo, todas las celdas en el rango se llenarán en 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 puedes hacer incluyen:

• Aplicar un esquema alrededor del grupo.
• Compruebe la ortografía del texto dentro de un rango de celdas
• Borrar, copiar, o cortar celdas
• Busca a través de 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 scripts de VBA al siguiente nivel. El artículo de Dann sobre el uso de las funciones de contar y agregar 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. El tutorial de Excel VBA para principiantes. El tutorial de Excel VBA para principiantes. VBA es una herramienta poderosa de Microsoft Office. Puede usarlo para automatizar tareas con macros, establecer disparadores y mucho más. Te presentaremos la programación visual básica de Excel con un proyecto simple. Lee más para ti también.

.