sábado, enero 26, 2008

Programación de Eventos en Excel - Primera Nota

En mi nota sobre cómo crear un numerador automático para factura en Excel mostraba el uso de eventos en esta tarea. Ya en notas anteriores había mostrado o mencionado el uso de esta herramienta y también prometido que escribiría una nota sobre el tema. Aquí está la nota.

Debemos distinguir entre eventos y programación de eventos. Evento es, como su nombre lo indica, algo que ocurre en Excel. Programar un evento significa que hemos escrito una rutina que será ejecutada cuando ocurra el evento.

Excel monitorea constantemente lo que ocurre en la aplicación. Cuando existe un código asociado a un evento, este es disparado al darse las condiciones del evento.

Por ejemplo, en la nota mencionada más arriba, hacíamos uso del evento BeforePrint. A este evento le habíamos asociado una macro que colocaba un número consecutivo cuando el usuario decidía imprimir la factura.
En ese ejemplo el evento es iniciado por el usuario. Eventos pueden ser iniciados también por macros.

Existen eventos para casi todos los objetos de Excel. Podemos clasificar los eventos de la siguiente manera:

  • Eventos del cuaderno (Workbook events): responden a acciones en un cuaderno en particular. Por ejemplo Workbook_Open, que ocurre cuando abrimos un cuaderno.


  • Eventos de las hojas (Worksheet events): responden a acciones en una hoja en particular. Por ejemplo Worksheet_Calculate, que ocurre cuando la hoja es recalculada.


  • Eventos de gráficos (Charts events): responden a acciones en un gráfico en particular.


  • Eventos de la aplicación (Application events): responden a acciones a nivel de la aplicación (es decir, Excel). Un ejemplo es WorkbookBeforeClose, que ocurre cuando iniciamos el proceso de cerrar un cuaderno.


  • Eventos de formularios/controles (Userform events): por ejemplo, el botón de la barra de formulario tiene un evento Button_Click.



Existen distintas formas de investigar qué eventos existen para cada objeto. Por ejemplo, al poner un botón de la barra de formularios en una hoja de Excel se abre el diálogo Asignar macro




Si apretamos Aceptar, Excel abrirá el editor de Vb con una rutina Botón2_AlHacerClic() sin líneas.



Si escribimos esta macro



cada vez que apretemos el botón ("al hacer clic"), el valor de la celda C1 se incrementa en 1.

En este caso la macro está ubicada en un módulo corriente. Más adelante veremos que no siempre es así.

Hay varias formas de investigar qué eventos existen para cada objeto. Por ejemplo, podemos usar el Examinador de Objetos (en el editor de Vb, apretar F2). En la ventana Clases elegimos el objeto que queremos investigar (Worksheet en nuestro caso) y en la ventana Miembros de Worksheet nos deslizamos hasta que vemos nombres con un rayo amarillo a la izquierda



Esto nos permite ver los eventos disponibles para la hoja.

Otra forma es seleccionar el objeto en la ventana Explorador de proyectos con un doble clic. Si hacemos esto con la Hoja1, se abre un módulo especial asociado con esta hoja. Abrimos la lista de la ventanilla donde aparece General



y elegimos Worksheet. Excel pone automáticamente una Sub Worksheet_SelectionChange(ByVal Target As Range) sin líneas. Ahora podemos abrir la lista de la ventanilla de los eventos y elegir alguno de ellos



Señalemos que los nombres y los parámetros de los eventos están predeterminados y no deben ser cambiados.

Veamos ahora algunas técnicas útiles. Queremos que cada vez que el usuario abre un determinado cuaderno aparezca un mensaje que le recuerde realizar cierta tarea. Obviamente, este evento pertenece al libro (Workbook). En el editor de Vb, apretamos el icono ThisWorkbook y elegimos el evento Open. Escribimos una rutina como ésta



Guardamos el cuaderno. Al abrirlo, veremos el mensaje



Varios de los eventos de la hoja tienen una variable llamada Target. De acuerdo al evento, esta variable está definida como Rango, como en el evento Change


Sub Worksheet_Change(ByVal Target As Range)

o como enlace (Hyperlink)

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

e inclusive como tabla dinámica

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable).

En el caso de rangos, Target se refiere a cualquier celda de la hoja. Por eso, cuando trabajamos con alguno de estos eventos, surge la necesidad de definir cuál es el rango relevante.
Supongamos que en una hoja tenemos un rango llamado Ventas, B2:B13. Queremos que cada vez que el usuario ingrese las ventas de un mes, aparezca un mensaje que le muestre el total acumulado. Esto significa que el evento debe reaccionar cuando cambian los datos en el rango.
En este caso usamos el evento Change, al que le asociamos este código

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rngVentas As Range, vntTotal As Double

  Set rngVentas = Range("Ventas")
  vntTotal = WorksheetFunction.Sum("Ventas")

    If Union(Target, rngVentas).Address = rngVentas.Address Then
      MsgBox "Tottal acumulado de ventas " & vntTotal
    End If

End Sub

Cada vez que ingresamos un dato en el rango B2:B13, recibiremos un mensaje como este



La clave en esta rutina es el uso de la función Union. Esta función evalúa si la dirección de Target (la celda activa donde introducimos el dato) coincide con alguna de las direcciones del rango Ventas. En caso afirmativo, se dispara el evento que calcula el total y muestra el mensaje.
En la línea Set rngVentas = Range("B2:B13") es más conveniente usar la sintaxis Set rngVentas = Range("ventas"), usando el nombre definido "Ventas".

En la próxima nota tocaremos otros aspectos de los eventos, como jerarquía de eventos, cómo evitar "loops" y agregaremos algunos ejemplos más.


Technorati Tags:

24 comentarios:

  1. Revisando el código del evento Worksheet_Change, hay que hacer una corrección.

    En vez de
    vntTotal = WorksheetFunction.Sum("Ventas")

    se debería escribir

    vntTotal = WorksheetFunction.Sum(Range("Ventas"))


    Demasiado bueno el blog Jorge, saludos

    ResponderBorrar
  2. Hola Luis ALberto

    gracias por haber visto el error. En realidad la corrección tiene que ser

    vntTotal = WorksheetFunction.Sum(rngVentas)

    que para eso definimos la variable

    Qué hacemos con el blog? Lo arruinamos un poco? :)

    ResponderBorrar
  3. Hola Jorge; mi nombre es Carlos Aravena S. de Chile, y la verdad encuentro que eres el mejor aporte de excel en paginas en Castellano, ya que trabajas sobre problemas reales. Ojala microsoft implementara tu pagina en sus trabajos y soluciones sobre excel. Mira Jorge, te queria hacer una pregunta, ¿ hace mucho tiempo que deseo aprender macros en excel, pero a programarlas, no a grabarlas. Y la verdad no sé por donde empezar, ya que cuando aprendo algo, al instante se saltan a los codigos y me pierdo. Tu enseñaste sobre los eventos y empezare por alli, pero si me puedes dar algun consejo, te lo agradeceria.

    saludos cordiales y felicitaciones por abrirnos la cabeza a algunos.

    ResponderBorrar
  4. Hola Carlos

    gracias por los elogios. El mejor camino, en mi opinión, es a través de la práctica. Puedes empezar buscando soluciones en los distintos foros y sitios sobr el tema y tratando de implementarlas a tus problemas. En cuanto a libros, todo lo que he usado en mi aprendizaje está en inglés, y no se si será relevante para tí.

    ResponderBorrar
  5. Hola Jorge,
    En relación a los eventos que pueden aparecer cuando abrimos un libro. Tengo un libro en el que los usuarios tienen que completar datos partiendo de un mes concreto. Me gustaría saber cómo puedo hacer que al abrir dicho libro los usuarios deban elegir el mes desde el cual quieren partir. Básicamente, esto lo quiero hacer para que los usuarios no olviden cambiar una lista desplegable con los meses, que hay en una hoja del libro. Por tanto, simplmente se traría de cambiar esa celda de validación con el formulario de apertura del libro.
    Además, como yo voy a administrar el archivo, ¿sería posible que este formulario no me apareciera a mí, para poder obtenerlo todo tal y como lo han grabado los usuarios?
    Muchas gracias y enhorabuena por tu gran trabajo!

    ResponderBorrar
  6. Anònimo,
    ponte en contacto conmigo por correo electrònico

    ResponderBorrar
  7. Estimado jorge , escribo desde Perú. En primer lugar, felicitarte por tu blog y lo que haces por nosotros, tus seguidores asiduos. Mi consulta es acerca de cómo ver el contenido extenso de una celda con solo seleccionarla. Vi una entrada en tu blog sobre este tema pero lo programas para toda la columna. Yo solo deseo el evento para una celda.Agradezco tu atención y quedo a la espera de tus comentarios. Saludos cordiales. carlos.mallma@gmail.com

    ResponderBorrar
  8. Hola Carlos,
    gracias por los conceptos. En cuanto a tu consulta, se puede hacer poniendo el contenido en un formulario que se despliegue al activar un evento. En un futuro cercano le dedicaré una nota al tema.

    ResponderBorrar
  9. Hola Jorge, no imaginas lo mucho que me ayudado tu Blog en mi saber de Excel, muy interesantes y útiles todas tus aportaciones. Tengo una pregunta con este tema de Eventos, espero me puedas ayudar, yo tengo un rango de celdas vacío, pero quiero que cada vez que llene esta columna (poniendo un 1 por ejemplo), en otra columna pero en la misma fila vaya poniendo un número consecutivo, tienes alguna idea para programarlos con un evento de cambio?

    te agradezco tu respuesta, saludos!

    Armando R.

    ResponderBorrar
  10. La respuesta a tu consulta excede le marco de un comentario. POnte en contacto conmigo por mail privado.

    ResponderBorrar
  11. Hola. Quería saber si has escrito algo acerca del orden de ejecución de los eventos, o me puedes indicar donde encontrar información.

    Gracias. Un saludo.

    Ramón (Málaga).

    ResponderBorrar
  12. Hola Jorge,

    Ante todo, gracias por tus aportes... llevas la docencia en el alma, amigo :o)
    Ahora, la consulta : tengo una rutina que recoge datos de la planilla para armar un "nombre de archivo" y cuando invoco al SendKeys para pasarle el string como parámetro al SAVEAS, en Excel 2003 funciona perfectamente, pero en Excel 2007, me "come" las primeras letras del string !!! La he debugueado paso a paso y la variable llega perfectamente cargada al SendKeys... alguna sugerencia ante lo insólito del comportamiento?

    ResponderBorrar
  13. No conozco ese problema. Puedes mandarme el archivo?

    ResponderBorrar
  14. Hola Jorge,

    Como hago para que cuando la celda que calcula un resultado de una fórmula llegue a un valor determinado, se dispare un contador y vaya llevando el número de veces que la celda llega a ese valor.

    Para que la celda llegue a ese valor depende de un control que va aumentado progresivamente unas variables que alimentan la fórmula de la celda en mención. Por lo tanto dicha celda tendra un resultado que se incrementará progresivamente de cero hasta el valor seleccionado, activará el contador y regresará a cero para comenzar un nuevo ciclo. Mil gracias de antemano.

    Jorge A.

    ResponderBorrar
  15. Programando un evento que llame a un programa que haga la tarea. Si estás interesado en el desarrollo de código te sugiero que te pongas en contacto conmigo por mail privado (la dirección aparece en el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar
  16. Hola Jorge,

    Ya lo contacté por el mail...gracias

    Jorge A.

    ResponderBorrar
  17. Buen día, necesito un evento en el cual al momento de abrir el Excel la primera vez en el dia, se me ejecute una macro y no importanto la veces que abra de nuevo el excel no se vuelva a ejecutar,hasta que vuelva a abrir el excel la primera vez al dia siguiente.

    Esteré atento

    ResponderBorrar
  18. En el objeto ThisWorkbook usar el evento Open.

    ResponderBorrar
  19. Jorge gracias por ayudarnos a salir de apuros con tus apuntes. he aprendido mucho. Diseñe una factura para pago de servicios mensual y necesito que en el momento que la celda P8 supere cierto valor automaticamente en la celda S8 me salga el aviso servicio para corte. No se si sera muy exagerada mi consulta de antemano gracias

    ResponderBorrar
  20. Jeoffrey
    hay varias formas de hacerlo. Siguiendo con el tema de la nota podrías programar un evento. Otra forma sería usar formato condicional. En este caso el aviso en la celda S8 usarí el mismo color de fuente que el fondo de la celda, lo que lo vuelve "invisible". Si se cumple la condición (S8>valordeterminado), el color de la fuente en S8 se vuelve negro (o rojo o lo que se desee) volviéndose así "visible".
    La mejor forma de hacerlo depende del diseño del modelo.

    ResponderBorrar
  21. Gracias Jorge
    La factura esta diseñada en word pero tengo la base de datos en excel y los datos de la factura los manejo con conbinacion de correspondencia por eso te digo lo de las celdas.
    Gracias

    ResponderBorrar
  22. De todas maneras, los datos estás manejando los datos en Excel y es ahí donde tienes que hacer la validación, entes de transferir los datos a la factura en Word.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.