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: MS Excel

Distribuir datos en hojas Excel con macros.

En la nota anterior sobre cómo distribuir datos de una hoja a otras hojas de un cuaderno Excel, vimos como hacerlo con fórmulas.

En esa nota señalaba que el método con fórmulas es útil sólo si nuestro cuaderno contiene pocos datos. Uno de mis lectores intentó usar la solución con fórmulas pero, en sus porpias palabras "no he podido concluir mi base de datos porque la cantidad de data es mucha y con el peso de las formulas todas las maquinas se cuelgan".

Este mismo lector me pedía que le enseñara la forma de hacerlo con macros. En esta nota mostraré una solución posible con macros al mismo caso que mostrábamos en la nota anterior.
Debo aclarar que esta solución no es óptima desde el punto de vista de programación, sino que está orientada al usuario promedio cuyo objetivo es crear herramientas para el trabajo diario y no desarrollo de implementaciones en Excel.

El ejemplo se basa en el mismo archivo de la nota anterior. El archivo con las macros puede descargarse ">aquí.

Empezamos por agregar el encabezamiento "Transferido".

Esta columna nos servirá para chequear si la línea de la hoja Datos ha sido transferida o no.

En esta solución usaremos dos macros. Una de tipo evento y otra que hará el trabajo de copiar los datos a la hoja correspondiente.
La idea es que, una vez agregados los datos en la hoja "Datos", haciendo doble-clic en la celda correspondiente de la columna E, los datos sean transferidos a la hoja adecuada. Además queremos evitar que por error los datos sean transferidos más de una vez. Para eso usaremos el mecanismo de poner, automáticamente, la palabra "SI" en la columna Transferido, después de haber copiado los datos. Una vez que aparece la palabra SI en la columna E de la línea, la macro no permitirá volver a copiarlos.

Para disparar la macro que copie los datos, usaremos el evento BeforeDoubleClick de la hoja "Datos". Empezamos por abrir el editor de Vba y haciendo doble-clic en el icono de la hoja Datos, abrimos el módulo de Vb para el evento



En el módulo del evento ponemos este código


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rngAnotar As Range, Resp, Fila

Set rngAnotar = [E:E]

If IsEmpty(Target) = False Then
MsgBox "La línea ya fue transferida"
Exit Sub
End If

If Union(Target, rngAnotar).Address = rngAnotar.Address Then
Resp = MsgBox(prompt:="Transferir datos?", Buttons:=vbYesNo, _
Title:="Transferir Datos")
If Resp = vbNo Then
Cancel = True
Exit Sub
End If
Fila = Target.Row
Call dist_hojas
Target.Value = "SI"
End If
End Sub


Después de declara las variables y definir el rango E, empezamos por comprobar si la línea a sido transferida en el pasado. Si la palabra SI aparece en la celda correspondiente de la línea que queremos copiar, aparece un mensaje informándonos que ya hemos transferido los datos y la macro se interrumpe.
El resto del código produce un mensaje preguntándonos si queremos copiar los datos. Sólo si apretamos NO, la macro se interrumpe y no habrá ningún cambio en el cuaderno.



Si aceptamos, los datos serán copiados y en la celda de la columna E aparecerá la palabra SI



Si intentamos volver a copiar los datos de la línea, aparece este mensaje



El trabajo de copiar a la hoja correspondiente lo hace esta macro, que ponemos en un módulo corriente (Módulo1)


Sub dist_hojas()
Dim LastRow As Long, Hoja As String
Dim rngCopiar As Range

Hoja = "Ruta " & ActiveCell.Offset(0, -1).Value 'definir nombre de hoja

Set rngCopiar = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 3))

Application.ScreenUpdating = False

With Sheets(Hoja)
.Activate
LastRow = WorksheetFunction.CountA([A:A]) + 1
End With

rngCopiar.Copy Sheets(Hoja).Cells(LastRow, 1)
Sheets("Datos").Select

Application.ScreenUpdating = True

End Sub


Después de definir las variable, la primer línea del código "arma" el nombre de la hoja uniendo a la palabra "Ruta" el número que aparece en la columna D, con el operador "&".
La línea siguiente determina cuál es el rango a copiar en "Datos".
El paso siguiente es determinar cuál es la primer fila libre en la hoja en la que vamos a pegar los datos.
Una vez hecho esto, volvemos a "Datos", copiamos el rango adecuado y lo pegamos en la hoja correspondiente.

En este modelo no hemos puesto ningún método automático para borrar lpineas que han sido copiadas y cancelar la señal SI en la hoja Datos. Esto se deberá hacer manualmente.

Technorati Tags: MS Excel

Numerador automático para facturas o recibos en Excel

Con Excel es fácil crear plantillas para facturas o recibos. Más aún, se pueden descargar gratuitamente de varios sitios. La plantilla del ejemplo que usaré en esta nota fue descargada del sitio de Microsoft.

Actualización 25/08/2014: nuevo modelo mejorado

Una de las consultas que recibo frecuentemente, es cómo crear un numerador automático para facturas o recibos en hojas de Excel. Si observan la plantilla de la factura, verán que la celda C5 contiene el número de factura.




Lo que queremos hacer es que este número se actualice cada vez que emitimos una factura, de manera que la siguiente tenga el número consecutivo (el archivo con el ejemplo se puede descargar aquí aunque recomiendo el nuevo modelo que incluye base de datos de las facturas producidas).

Esto se puede hacer de varias maneras, pero todas implican usar macros.

Una solución sencilla es agregar un botón al que le asociamos una macro. El botón lo creamos copiándolo de la barra de formularios.



Pulsamos el botón Nuevo, lo que abre un módulo de Vba con el evento Sub Botón3_AlHacerClic(). Aquí escribimos este código

[C5] = [C5] + 1



Ahora, cuando apretamos el botón el valor en la celda C5 se incrementará en 1.

Esta técnica es muy sencilla, pero no ofrece ninguna ventaja frente al sencillo método de cambiar el valor en la celda manualmente. Además, si nos olvidamos de presionar el botón, la próxima factura saldrá con el mismo número.

Una forma de lograr algún tipo de control es ligar el botón a la acción de sumar los importes de la factura. Es decir, hasta que no apretemos el botón no aparecerá la suma total de la factura en la celda correspondiente (la celda C35 en nuestra plantilla).

Empezamos por eliminar la fórmula en la celda C35. Teniendo en cuenta que el rango de los montos de las líneas de la factura es C18:C34, modificamos nuestro código, para que sume los valores de las celdas del rango y luego cambie el número de factura

Sub Botón3_AlHacerClic()
[C35] = WorksheetFunction.Sum(Range("C18:C34"))
[C5] = [C5] + 1
End Sub



Otra alternativa es que el número de factura cambie automáticamente en las circunstancias debidas, por ejemplo antes de imprimirla.

Para esto debemos usar un tipo de macro especiales llamadas "eventos".
Eventos son macros que actúan cuando, como su nombre lo sugiere, algo sucede en la hoja, o el objeto, al cual están ligadas. Los eventos serán tema de una futura nota, como vengo prometiendo. En esta nota nos limitaremos a un ejemplo práctico.

Para saber qué eventos existen ligados al objeto, en nuestro caso el cuaderno que contiene la hoja con la factura, pasamos al editor de Vba y hacemos doble clic al icono ThisWorkbook,



Abrimos la lista desplegable de la ventanilla donde aparece General y elegimos Workbook



Abrimos la ventanilla contigua para ver qué eventos están a nuestra disposición



Como podemos ver, existe un evento BeforePrint, es decir "antes de imprimir". Ponemos el código anterior en este evento

Private Sub Workbook_BeforePrint(Cancel As Boolean)

[C35] = WorksheetFunction.Sum(Range("C18:C34"))

[C5] = [C5] + 1

End Sub

Ahora, cuando queramos imprimir la factura, ya sea con el icono de impresión o con el menú Archivo-Imprimir, se disparará el evento, el número de la factura aumentara en 1 y el total será recalculado.
Para mejorar nuestro evento, podemos incluir la posibilidad de cancelar la impresión. Lo que haremos es agregar algunas líneas de código


Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim Mensaje, Resp
    Dim dlgPrint As Boolean
    
    Mensaje = "El total es " & [C35] 'Total
    Mensaje = Mensaje & " Imprimir?"
    Resp = MsgBox(Mensaje, vbQuestion + vbYesNo)

    On Error GoTo errNoPrint

    If Resp = vbYes Then
    Application.EnableEvents = False
    [C5] = [C5] + 1
     dlgPrint = Application.Dialogs(xlDialogPrint).Show
        If dlgPrint = False Then
            [C5] = [C5] - 1
            Cancel = True
            Application.EnableEvents = True
            Exit Sub
        End If
    Else
        Cancel = True
        Application.EnableEvents = True
    End If

    Application.EnableEvents = True

    
    Exit Sub

errNoPrint:
[C5] = [C5] - 1
Cancel = True
Application.EnableEvents = True
End Sub


Primero calculamos el total; luego producimos un mensaje donde exponemos el total y damos la opción de cancelar la impresión



Si el usuario aprieta No, el proceso de impresión se detiene y el número de la factura no cambia.

Como se puede ver, con algunas líneas de código puestas en el lugar correspondiente, podemos lograr una aplicación bastante práctica. El cuaderno con la factura puede descargarse aquí

Si usamos el evento BeforePrint, el botón es innecesario y puede ser eliminado.



Technorati Tags: MS Excel