Ejercicio práctico 7: Cómo utilizar los eventos Change e Intersect de Worksheet en macros.

Escrito por Administrador

25 de agosto de 2021

Continuamos el ciclo de casos prácticos con algo que ya vimos en su día, pero no le sacamos todo el potencial que escondía.

Hoy aprenderemos a utilizar los cambios en las hojas de cálculo, como puede ser la introducción de datos, para desencadenar operaciones automáticas.

Como ejemplo, he preparado un gráfico con la información anual del consumo de gasoil en una empresa de transportes desglosado por meses.

total-operaciones-matematicas-macros

Como se puede observar en la figura, la empresa cuenta con un objetivo mensual de 350€, es decir, debe optimizar los recorridos para realizar las entregas y no gastar más gasoil de la cuenta.

La última barra corresponde al promedio anual, que no es más que la media de los doce meses.

Este formato podría pegarse en el tablón de anuncios de la empresa para que los empleados tomaran conciencia sobre el consumo de combustible, les sirviera de motivación y actuaran en consecuencia.

Para ello, y a modo de resumen más allá de las cifras, vamos a resumir el estado en el que se encuentra el consumo, a una carita situada en el encabezado del informe, que será:

  • Verde: si el promedio se encuentra por debajo del 5% objetivo.
  • Amarilla: si el promedio se encuentra entre el 5% tanto por encima como por debajo del objetivo.
  • Roja: si el promedio se encuentra por encima del 5% del objetivo.

 

Así, conforme vayamos introduciendo la información de cada mes, la carita va a ir cambiando de color en función de cómo vaya el promedio anual.

Esto lo conseguimos usando el evento change de worksheet y el método Intersect, que detectarán el cambio en la hoja al introducir el importe mensual, evaluarán el promedio y mostrarán la carita correspondiente.

Os recuerdo que, para usar este procedimiento, el código lo introducimos directamente en la hoja de cálculo:

total-operaciones-matematicas-macros

Antes de continuar, os voy a explicar los “trabajos previos” que hay que realizar en la hoja antes de ponernos a programar.

En primer lugar, descargamos las tres imágenes con las caritas verde, amarilla y roja (las puedes descargar al final del tema) y las guardamos en el directorio raíz C.

Para insertarlas en la hoja, hay que crear previamente un control ActiveX de imagen. Para ello, nos vamos a la pestaña de desarrollador, y en insertar seleccionamos la opción imagen (Control ActiveX):

total-operaciones-matematicas-macros

Seleccionamos la zona de la hoja donde queremos crearlo, así como sus dimensiones:

total-operaciones-matematicas-macros

Si pulsamos en propiedades, podemos modificar las características del mismo, así como su nombre.

total-operaciones-matematicas-macros

En este caso le vamos a dejar el que está Image1, pero sí que vamos a cambiar algunas propiedades:

  • BackColor: &H8000000B& (color de fondo desactivado)
  • BackStyle: 0 (Transparente)
  • BorderColor: &H8000000B& (color del borde desactivado)
  • Height: 40,5 (alto de la imagen)
  • Locked: False (no bloqueado)
  • PictureSizeMode: 1 (para que se ajuste automáticamente al recuadro)
  • Width: 40,5 (ancho de la imagen)

 

Continuamos con el código.

Macro 1. Vigilancia de celdas.

Private Sub Worksheet_Change(ByVal Target As Range)

'Vigilamos las celdas donde introducimos datos, si hay algún cambio llamamos a la función foto

If Not Application.Intersect(Range("F50:Q51"), Target) Is Nothing Then Call foto

End Sub

Macro 2. Carga de la carita correspondiente en función del promedio anual.

Sub foto()

'Si el promedio se encuentra por debajo del 5% objetivo

If Cells(50, "R") < Cells(51, "R") * 0.95 Then

Image1.Picture = LoadPicture("C:\cara ok.jpg")

'Si el promedio se encuentra por encima del 5% objetivo

ElseIf Cells(50, "R") > Cells(51, "R") * 1.05 Then

Image1.Picture = LoadPicture("C:\cara nok.jpg")

'Si el promedio se encuentra entre el 5% tanto por encima como por debajo del objetivo

Else

Image1.Picture = LoadPicture("C:\cara neutra.jpg")

End If

End Sub

Supongamos que nos encontramos a principios del mes de octubre, y ya tenemos a nuestra disposición la información del consumo de gasoil del mes anterior.

total-operaciones-matematicas-macros

El rango vigilado es,

total-operaciones-matematicas-macros

Cuando introduzcamos la cantidad de gasoil consumida durante el mes de septiembre en la celda N50, al pertenecer al rango vigilado, se ejecutará automáticamente la función foto.

Private Sub Worksheet_Change(ByVal Target As Range)

'Vigilamos las celdas donde introducimos datos, si hay algún cambio llamamos a la función foto

If Not Application.Intersect(Range("F50:Q51"), Target) Is Nothing Then Call foto

End Sub

¿Qué hará la función foto?

Simplemente evaluar en que franja se encuentra el consumo respecto al objetivo.

Si el promedio se encuentra por debajo del 5% del objetivo, cargará la imagen con la carita verde mediante el comando LoadPicture en el control ActiveX Image1.

Sub foto()

'Si el promedio se encuentra por debajo del 5% objetivo

If Cells(50, "R") < Cells(51, "R") * 0.95 Then

Image1.Picture = LoadPicture("C:\cara ok.jpg")

En el ejemplo, introducimos en septiembre la cantidad 10. Vemos que el promedio baja a 331.67, que es inferior al 95% del objetivo y cómo la carita se convierte en verde.

total-operaciones-matematicas-macros

Si el promedio se encuentra por encima del 5% del objetivo, cargará la imagen con la carita roja,

'Si el promedio se encuentra por encima del 5% objetivo

ElseIf Cells(50, "R") > Cells(51, "R") * 1.05 Then

Image1.Picture = LoadPicture("C:\cara nok.jpg")

Introducimos ahora para el mes de octubre la cantidad 800. Vemos que el promedio sube a 378.50, que es superior al 105% del objetivo y cómo la carita se convierte en roja.

total-operaciones-matematicas-macros

En caso contrario, el promedio se encuentra en una franja del 5% por encima o por debajo del objetivo.

'Si el promedio se encuentra entre el 5% tanto por encima como por debajo del objetivo

Else

Image1.Picture = LoadPicture("C:\cara neutra.jpg")

End If

End Sub

Para el mes de noviembre, introducimos la cantidad 200. Vemos que el promedio baja a 362.27, que se encuentra entre el 95% y el 105% del objetivo y cómo la carita se convierte en amarilla.

total-operaciones-matematicas-macros

Con esto llegamos al final de la trigésimo primera lección. Espero que te esté gustando el curso y que me plantees cualquier duda que te surja. También puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario, soy todo oídos. Nos vemos en el próximo capítulo.

Descarga el archivo del ejemplo

« EP6. Cómo buscar errores con macros.

EP8. Cómo enviar un informe por email con macros. »

0 comentarios

Enviar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Quizá te interese,

Contacto