Agregar datos en la primer fila libre de la tabla

Excel no es la herramienta más adecuada para manejar bases de datos. Pero cuando se trata de cantidades limitadas de datos y sin vínculos complicados, Excel puede ayudarnos.

Por lo general, lo que hacemos es construir en una hoja una tabla (o lista) a la cual iremos agregando datos. También podemos crear una especia de formulario en otra hoja usando controles y también usar validación de datos.

Un ejemplo de este tipo de "aplicación" ya hemos mostrado en la nota Agrupar controles botones de opción en hojas Excel.

Una de las consultas que recibo con cierta frecuencia es: "cómo hago para que al agregar datos a mi base de datos, lo haga en la primer fila libre de la tabla?"

El archivo del ejemplo de la nota mencionada incluye una macro (de hecho dos, una para cada método de crear el formulario) que realiza esa tarea.

La macro es muy sencilla y fácil de adaptar a todo tipo de aplicaciones. Para los lectores sin experiencia en Vba, daremos aquí una explicación de la macro.


Sub clientes_form()
    Dim linea_libre As Long

    Application.ScreenUpdating = False

    Sheets("Clientes").Select
       linea_libre = WorksheetFunction.CountA(Range("A:A")) + 1
       Cells(linea_libre, 1).Value = Sheets("Formularios").[I2]
       Cells(linea_libre, 2).Value = Sheets("Formularios").[I3]
       Cells(linea_libre, 3).Value = Sheets("Formularios").[I4]
    Sheets("Formularios").Select

    Application.ScreenUpdating = True

    MsgBox "Se ha agregado el cliente " & Sheets("Formularios").[I2]

End Sub

Empezamos declarando una variable que contendrá el número de la primer fila disponible

Dim linea_libre As Long

Para calcular cuál es la primer fila libre, seleccionamos la hoja Clientes

Sheets("Clientes").Select

y usamos en el código la función CONTARA de Excel para establecer el valor de la variable

linea_libre = WorksheetFunction.CountA(Range("A:A")) + 1

Luego fijamos los valores de las celdas en clientes

Cells(linea_libre, 1).Value = Sheets("Formularios").[I2]
Cells(linea_libre, 2).Value = Sheets("Formularios").[I3]
Cells(linea_libre, 3).Value = Sheets("Formularios").[I4]


y volvemos a la hoja Formulario

Sheets("Formularios").Select

Para evitar que Excel "parpadee" durante la corrida de la macro, usamos la orden Application.ScreenUpdating = False y al final de la macro, antes de la aparición el mensaje, reponemos Application.ScreenUpdating = True.

Esta macro está ligada al botón "Agregar Cliente" en la hoja Formulario.

Technorati Tags: MS Excel

Operaciones con colores en Excel

En la nota Usando funciones XLM (Excel 4) en hojas de cálculo, mostraba cómo se podía obtener el número de color del fondo de una celda. Esto implicaba el uso de "macrofunciones" (funciones del lenguaje de macro XLM que existió hasta la versión 4) dentro de nombres. Esta técnica nos permitía realizar operaciones como sumar o contar, basándonos en los colores del fondo de un rango de celdas.
No tenía intenciones de volver sobre el tema, hasta que hace unos días un compañero de trabajo me manda un cuaderno Excel con una lista de cerca de 4.000 clientes de la empresa. La tabla estaba ordenada alfabéticamente y cada nombre de cliente tenía un color distinto de acuerdo a las condiciones de crédito (al contado: azul; 30 días: verde; 60 días: amarillo; etc.). Su tarea era sumar los saldos de los clientes por condiciones de crédito y calcular el promedio, para lo cual había que contar el número de clientes en cada grupo.
Después de señalarle que esa hoja era uno de los mejores ejemplos de lo que no se debe hacer en Excel (preferir la estética a la utilidad) intenté explicarle la técnica a usar con las macrofunciones. Como podrán imaginar mi compañero no estaba del mejor ánimo para explicaciones, después de haber invertido horas en poner fondos de color por tipo de crédito para descubrir al final que no puede hacer nada con la lista.
Decidí que lo mejor sería escribir unas UDF (funciones definidas por el usuario) que hagan la tarea.
Empezamos por una función que de cómo resultado el color del fondo de la celda:

Function extraer_color(miCelda As Range)
    extraer_color = miCelda.Interior.ColorIndex
End Function

Aplicamos la fórmula a algunas celdas con fondo de color

La celda A6 no tiene ningún fondo y de ahí el resultado. Podemos cambiar el código de esta manera para que en caso de no haber fondo el resultado sea 0

Function extraer_color(miCelda As Range)
   Select Case miCelda.Interior.ColorIndex
     Case xlNone
       extraer_color = 0
     Case Else
       extraer_color = miCelda.Interior.ColorIndex
   End Select
End Function

Nuestra próxima función nos permitirá contar por color:

Function contar_por_color(RangoColor As Range, CeldaColor As Range)
 Dim rngCelda As Range

 For Each rngCelda In RangoColor
  If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
   contar_por_color = contar_por_color + 1
  End If
 Next
End Function

La función tiene dos variables: RangoColor, que es el rango dónde queremos contar por color y CeldaColor, que es la celda que contiene el color del criterio.

Finalmente, una función para contar por color:

Function sumar_por_color(RangoColor As Range, CeldaColor As Range, RangoSumar As Range)
Dim rngCelda As Range
Dim colOffset As Long

colOffset = RangoSumar.Column - RangoColor.Column

For Each rngCelda In RangoColor
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_por_color = sumar_por_color + rngCelda.Offset(0, colOffset).Value
End If
Next
End Function



En esta función usamos un tercer argumento para el rango que contiene los valores a sumar. Los rangos RangoColor y RangoSumar deben tener la filas en común.
La variable colOffset calcula la distancia, en número de columnas, entre el rango con los valores y el rango con los fondos de color. Naturalmente, este número puede ser positivo, si los números están a la derecha de los colores, o negativo si lo están a la izquierda.

El cuaderno con las funciones puede descargarse aquí

Technorati Tags: MS Excel

Programación de eventos en Excel – Segunda Nota

En la primera nota sobre programación de eventos en Excel vimos la importancia y el poder de esta herramienta. En esta nota daremos algunos ejemplos, poniendo el énfasis al aspecto práctico. Pero empecemos por señalar algunas características importantes de los eventos.
Existe una jerarquía de eventos:

Eventos de la aplicación (Application events)

Eventos del cuaderno (Workbook events)

Eventos de la hoja (Worksheets events)

En el tope de la jerarquía están los eventos de la aplicación, luego los del cuaderno y finalmente los de la hoja. Cada objeto contiene sus propios eventos y los de de los objetos que se encuentran por debajo de él en la jerarquía. Por ejemplo el cuaderno tiene un evento Worksheet_Change que responde a cada cambio en una celda de la hoja. El cuaderno, a su vez, tiene un evento Workbook_SheetChange que responde a un cambio en una celda de cualquiera de las hojas del cuaderno. Ante un cambio en una celda, los eventos de los tres niveles entran en acción.

Ciertas acciones disparan más un evento, pero en cierto orden preestablecido. Por ejemplo, agregar una nueva hoja a un cuaderno (la acción) dispara una serie de eventos exactamente en este orden:

Workbook_SheetDeactivate

Workbook_SheetActivate

Workbook_NewSheet

Un tercer punto, y el más importante del punto de vista práctico, es cómo evitar generar "loops" infinitos al programar eventos. Por ejemplo, consideremos este código

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Target.Value + 1
End Sub

Ante un cambio en la hoja, el valor de la celda activa (Target) aumenta en 1. Esta acción en sí misma constituye un cambio, lo que produce un evento Change que vuelva a agregar 1 al valor de la celda. Y así sucesivamente hasta el infinito (o hasta que pulsemos Ctrl+Break).
Si queremos ejecutar este código sin entrar en un "loop" infinito, debemos usar la propiedad EnableEvents de la Aplicación. En nuestro ejemplo:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Target.Value + 1
Application.EnableEvents = True
End Sub

Primero llevamos el valor de la propiedad a False, ejecutamos la (o las) línea y volvemos a poner el valor de EnableEvents a True. Esto es importante, ya que el valor de la propiedad no vuelve al valor de defecto (True) una vez concluido el código.

Ya hemos visto algunos ejemplos de eventos en la nota anterior y también en la nota sobre el numerador automático de facturas.

Ejemplos de eventos de cuaderno (Workbook events):

Seleccionar la celda A1 al activar una hoja

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   [A1].Select
End Sub

Dado que usamos un evento a nivel de cuaderno, al seleccionar cualquier hoja, la celda A1 será la celda activa (excepto que seleccionemos una hoja de gráfico, en cuyo caso ocurrirá un error).

Al agregar una nueva hoja al cuaderno, le fecha y hora aparecen en la celda A1

Private Sub Workbook_NewSheet(ByVal Sh As Object)

If TypeName(Sh) = "Worksheet" Then
   [A1] = "La hoja fue agregada el " & Now
End If

End Sub

Ejemplos de eventos de hoja

En la nota anterior mostramos un ejemplo de Worksheet_Change. Otro ejemplo interesante de este evento es el siguiente que pone un fondo de color a la fila y la columna de la celda activa

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
 With Target
   .EntireRow.Interior.ColorIndex = 35
   .EntireColumn.Interior.ColorIndex = 35
 End With
End Sub

Otros ejemplos pueden encontrarse en notas anteriores en este blog, como Limitar el área de trabajo en Excel o en cómo crear un menú en Excel.

Technorati Tags: MS Excel