Rangos de datos

Esta página está en estado borrador.

Los rangos de datos, son áreas rectangulares de rangos de celdas delimitados por al menos una fila y una columna en blanco a las cuales se les establece un nombre y tienen características especiales que facilitan su uso como si de una base de datos se tratara.

No confundas estos nombres, con los que puedes definir en el cuadro de nombres de la barra de formulas, ya que son distintos. Las mayor parte de las opciones que estudiaremos en este capitulo, son las presentes en el menú Datos, de la interfaz del usuario.

El uso de hojas de calculo con datos tratados como bases de datos, es, según mi experiencia, el uso más cotidiano dado a esta herramienta, y no es gratuito, las herramientas disponibles para trabajar con datos estructurados de esta manera, presentes en Calc, sin hacer uso de macros, es bastante amplio, poderoso y versátil, ahora, imagínate lo que se puede hacer, automatizando estos procesos.

Si bien este libro no es de bases de datos explícitamente, te ayudará mucho recordar que cada columna de tu rango de datos, lo podemos llamar; campo, y cada fila de estos; registros. Así mismo, en la primer fila de estos, se establecen los “títulos de campo”, normalmente en un formato diferente del resto de los datos, aunque esta primer fila no es indispensable, es mucho mejor tenerla.

También, es recomendable, no dejar ninguna fila en blanco, entre esta y los datos, las filas, es decir, los registros, es mejor si están completos, es decir, que todos sus campos contienen datos. De nuevo, esto no es indispensable, pero una base de datos se hace para llenarse de datos. Tampoco es recomendable dejar filas completas en blanco. Trata de que tus datos sean “consistentes”, esto quiere decir que si una columna (campo) lo llamas Edad, efectivamente se capturen números, o fechas si la calculas. En la actualidad hay una amplia fuente de información para una buena construcción de tus bases de datos, de tarea, busca algo acerca del siguiente tema: “normalización de bases de datos”, veras que tema tan interesante es.

Definiendo rangos

En el siguiente ejemplo, definimos un rango de bases de datos, en la primer hoja del documento, en el rango A1:D24.

 Sub RangoDeDatos1()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDir As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     'Accedemos al conjunto de los rangos de bases de datos
     oRangosBD = oDoc.DataBaseRanges()
     'El nombre del nuevo rango
     sNombre = "Direcciones"
     'La dirección del nuevo rango
     With oDir
         .Sheet = 0            'La hoja
         .StartColumn = 0        'La columna de inicio
         .EndColumn = 3        'La columna final
         .StartRow = 0        'La fila de inicio
         .EndRow = 23        'La fila final
     End With
     'Lo agregamos a la colección
     oRangosBD.addNewByName( sNombre, oDir )
     'Regresamos el rango recién agregado
     oRBD = oRangosBD.getByName( sNombre )    
     With oRBD
         .MoveCells = True        'Para que se actualice al insertar o eliminar celdas
         .KeepFormats = True    'Para que mantenga los formatos
     End With
 
 End Sub

El nombre del nuevo rango no debe de existir o te dará un error, en el siguiente ejemplo, solicitamos al usuario el nombre del rango de datos y tomamos la selección actual como dirección para el nuevo rango de datos, también, verificamos que no exista el nuevo nombre.

 Sub RangoDeDatos2()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDir As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     'Nos aseguramos de que sea un rango de celdas
     If oSel.getImplementationNAme = "ScCellRangeObj" Then
         'Solicitamos el nuevo nombre
         sNombre = Trim( InputBox("Escribe el nombre del nuevo rango") )
         'Si no esta vacío
         If sNombre <> "" Then
             oRangosBD = oDoc.DataBaseRanges()
             'Verificamos que no exista el nombre
             If Not oRangosBD.hasByName( sNombre ) Then
                 'Y lo agregamos. Observa como tomamos la dirección de la selección
                 oRangosBD.addNewByName( sNombre, oSel.getRangeAddress() )
                 oRBD = oRangosBD.getByName( sNombre )    
                 With oRBD
                     .MoveCells = True        
                     .KeepFormats = True        
                 End With
             Else
                 MsgBox "Ya existe el nombre del rango"
             End If
         Else
             MsgBox "El nombre no puede estar vacío"
         End If
     Else
         MsgBox "No es un rango de celdas"
     End If
 
 End Sub

Para borrar un rango de datos usamos el método removeByName, toma en cuenta que lo único que se borra es el nombre del rango de datos y sus propiedades, las celdas y sus valores se mantienen.

 Sub RangoDeDatos3()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     'El nombre del rango a borrar
     sNombre = "Pruebas"
     'Si el nombre no existe obtendrás un error
     If oRangosBD.hasByName( sNombre ) Then
         'Lo removemos de la colección
         oRangosBD.removeByName( sNombre )
         MsgBox "Rango de datos borrado"
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Podemos cambiar las propiedades de un rango de datos existente, por ejemplo, cambiarle el nombre.

 Sub RangoDeDatos4()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     'El nombre del rango a modificar
     sNombre = "Pruebas"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )
         'Le cambiamos el nombre
         oRBD.setName ("Nuevo nombre") 
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

O cambiar su dirección.

 Sub RangoDeDatos5()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDir As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     'El nombre del rango a modificar
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )
         'Cambiamos la dirección
         With oDir
             .Sheet = 0            'Cambiamos la hoja
             .StartColumn = 2        'La columna de inicio
             .EndColumn = 5        'La columna final
             .StartRow = 5        'La fila de inicio
             .EndRow = 19        'La fila final
         End With
         oRBD.setDataArea( oDir )
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Cambia la dirección de un rango con cuidado, puede suceder que acabe apuntando a un rango vacío de celdas sin querer.

Ordenar datos

Para ordenar datos, no necesitas forzosamente un rango de datos con nombre, veamos como ordenar un rango de celdas con y sin nombre de rango de datos. Tomaremos como datos de ejemplo, los siguientes.

Nº	Nombre	Año	Edad
1	nikole	1976	33
2	gloria	1976	33
3	antonio	1977	32
4	lidia	1967	42
5	paola	1979	30
6	vanessa	1974	35
7	paola	1972	37
8	paola	1968	41
9	paola	1968	41
10	lizette	1978	31
11	lizette	1978	31
12	lizette	1978	31
13	nikole	1977	32
14	gloria	1975	34
15	antonio	1979	30
16	lidia	1977	32
17	paola	1968	41
18	vanessa	1978	31
19	lizette	1969	40
20	nikole	1970	39
21	gloria	1971	38
22	antonio	1973	36
23	lidia	1968	41

Vamos a ordenar los datos por nombre.

 Sub OrdenarDatos1()
 Dim oHoja As Object
 Dim oRango As Object
 Dim mCamposOrden(0) As New com.sun.star.table.TableSortField
 Dim mDescriptorOrden()
 
     'La hoja donde esta el rango a ordenar
     oHoja = ThisComponent.getSheets.getByName("agosto")
     'El rango a ordenar
     oRango = oHoja.getCellRangeByName("A1:D24")
     'Descriptor de ordenamiento, o sea, el "como"
     mDescriptorOrden = oRango.createSortDescriptor()
 
     'Los campos a orden, o sea, el "que"
     'Los campos empiezan en 0
     mCamposOrden(0).Field = 1
     'Orden ascendente                
     mCamposOrden(0).IsAscending = True
     'Sensible a MAYUSCULAS/minusculas
     mCamposOrden(0).IsCaseSensitive = False
     'Tipo de campo AUTOMATICO
     mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
 
     'Indicamos si el rango contiene títulos de campos
     mDescriptorOrden(1).Name = "ContainsHeader"
     mDescriptorOrden(1).Value = True
     'La matriz de campos a ordenar
     mDescriptorOrden(3).Name = "SortFields"
     mDescriptorOrden(3).Value = mCamposOrden
 
     'Ordenamos con los parámetros establecidos
     oRango.sort( mDescriptorOrden )
 
 End Sub

Los puntos a los que debes poner atención son; el tipo de campo FieldType, puede tomar los siguientes valores.

com.sun.star.table.TableSortFieldType	Valor	Valor en Interfaz
com.sun.star.table.TableSortFieldType.AUTOMATIC	0	Automático
com.sun.star.table.TableSortFieldType.NUMERIC	1	Numérico
com.sun.star.table.TableSortFieldType.ALPHANUMERIC	2	Alfanumérico

En mis pruebas, establecer en uno y en otro, no me ha dado muchas variantes en velocidad, esto es, supongo, por que he hecho pruebas con pocos datos, habría que hacer pruebas de rendimiento con grandes datos para ver su desempeño pues no creo que esta propiedad este de adorno.

En el descriptor de orden, si estableces la propiedad; ContainsHeader, en falso (False), y tu rango de datos efectivamente tiene títulos de campo, estos no serán tomados en cuenta y se ordenaran en relación con el resto de tus datos, mi recomendación es que siempre establezcas esta propiedad en verdadero (True) y efectivamente te asegures de tenerlos, claro, a menos de que no te sea indispensable o de plano no los necesites.

Si quieres ordenar por más de un campo (por ahora el limite es tres), no hay más que agregar el segundo criterio a la matriz de campos, como en el ejemplo siguiente que ordenamos por nombre ascendente y después por edad descendente.

 Sub OrdenarDatos2()
 Dim oHoja As Object
 Dim oRango As Object
 Dim mCamposOrden(1) As New com.sun.star.table.TableSortField
 Dim mDescriptorOrden()
 
     oHoja = ThisComponent.getSheets.getByName("agosto")
     oRango = oHoja.getCellRangeByName("A1:D24")
     mDescriptorOrden = oRango.createSortDescriptor()
 
     mCamposOrden(0).Field = 1
     mCamposOrden(0).IsAscending = True
     mCamposOrden(0).IsCaseSensitive = False
     mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
     'Agregamos un segundo campo
     mCamposOrden(1).Field = 3
     'Este es descendente
     mCamposOrden(1).IsAscending = False
     mCamposOrden(1).IsCaseSensitive = False
     mCamposOrden(1).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
 
     mDescriptorOrden(1).Name = "ContainsHeader"
     mDescriptorOrden(1).Value = True
     mDescriptorOrden(3).Name = "SortFields"
     mDescriptorOrden(3).Value = mCamposOrden
 
     'Ordenamos con los parámetros establecidos
     oRango.sort( mDescriptorOrden )
 
 End Sub

Una opción muy interesante, es la posibilidad de enviar el resultado a un destino diferente como en el siguiente ejemplo.

 Sub OrdenarDatos3()
 Dim oHoja As Object
 Dim oRango As Object
 Dim mCamposOrden(0) As New com.sun.star.table.TableSortField
 Dim oDestino As Object
 Dim mDescriptorOrden()
 
     oHoja = ThisComponent.getSheets.getByName("agosto")
     oRango = oHoja.getCellRangeByName("A1:D24")
     oDestino = oHoja.getCellRangeByName("G1")
     mDescriptorOrden = oRango.createSortDescriptor()
 
     mCamposOrden(0).Field = 1
     mCamposOrden(0).IsAscending = True
     mCamposOrden(0).IsCaseSensitive = False
     mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
 
     mDescriptorOrden(1).Name = "ContainsHeader"
     mDescriptorOrden(1).Value = True
     mDescriptorOrden(3).Name = "SortFields"
     mDescriptorOrden(3).Value = mCamposOrden
     'Establecemos que queremos copiar el resultado a otro lado
     mDescriptorOrden(5).Name = "CopyOutputData"
     mDescriptorOrden(5).Value = True
     'Establecemos el destino de la copia
     mDescriptorOrden(6).Name = "OutputPosition"
     mDescriptorOrden(6).Value = oDestino.getCellAddress()
 
     'Ordenamos con los parámetros establecidos
     oRango.sort( mDescriptorOrden )
 
 End Sub

Observa como establecemos el destino con una estructura getCellAddress, esta solo incluye la hoja destino, la columna y la fila de inicio. Si en el rango destino existen datos, estos serán totalmente reemplazados sin preguntarte nada.

Ahora, ordenamos, pero accediendo desde un rango de datos.

 Sub OrdenarDatos4()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oRango As Object
 Dim mCamposOrden(0) As New com.sun.star.table.TableSortField
 Dim mDescriptorOrden()
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     'Verificamos que exista el nombre del rango de datos
     If oRangosBD.hasByName( sNombre ) Then
         'Referencia al rango
         oRBD = oRangosBD.getByName( sNombre )
         'Referencia al origen del rango
         oRango = oRBD.getReferredCells()
         mDescriptorOrden = oRango.createSortDescriptor()
 
         mCamposOrden(0).Field = 1
         mCamposOrden(0).IsAscending = True
         mCamposOrden(0).IsCaseSensitive = False
         mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
 
         mDescriptorOrden(1).Name = "ContainsHeader"
         mDescriptorOrden(1).Value = True
         mDescriptorOrden(3).Name = "SortFields"
         mDescriptorOrden(3).Value = mCamposOrden
 
         oRango.sort( mDescriptorOrden )
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

La propiedad importante es; getReferredCells, que te da acceso al rango origen de los datos, esta propiedad te devuelve un objeto ScCellRangeObj, por lo que puedes tener acceso a todas las propiedades de manipulación y formato vistas de este objeto, por ejemplo, puedes aplicarle un autoformato de tabla a los datos.

Filtrar datos

Las opciones que estudiaremos en este capitulo, son las presentes en el menú Datos | Filtro -> y seguiremos usando los datos del tema anterior.

Doy por hecho que no tienes problemas, como usuario, con cada una de estas opciones, veamos como establecerlas por código. Primero, la más sencilla, el filtro automático, te agrega un control de lista desplegable en el encabezado de campo de tus datos, como en.

Para hacer lo mismo por código, usamos.

 Sub FiltrarDatos1()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         'Referencia al rango
         oRBD = oRangosBD.getByName( sNombre )        
         'Mostramos el autofiltro
         oRBD.AutoFilter = True        
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

¿Y para quitarlos?, muy bien deducido, solo la establecemos en falso (False)

 Sub FiltrarDatos2()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         'Referencia al rango
         oRBD = oRangosBD.getByName( sNombre )        
         'Mostramos el autofiltro
         oRBD.AutoFilter = False
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Pero cuidado, la macro anterior, solo te quitará las flechas para desplegar el filtro del campo, si tienes establecido un filtro automático, este permanecerá, para eliminar completamente un filtro, tienes que hacer dos cosas; primero, eliminar el filtro y después mostrar las filas ocultas como te muestro en el siguiente ejemplo.

 Sub FiltrarDatos3()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro() As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         'Obtenemos la descripción del filtro        
         oDesFiltro = oRBD.getFilterDescriptor()
         'Le pasamos una matriz vacía con una estructura de campo de filtro
         oDesFiltro.FilterFields = mCamposFiltro
         'Quitamos las flechas
         oRBD.AutoFilter = False
         'Mostramos las filas ocultas
         oRBD.getReferredCells.getRows.IsVisible = True
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Entonces, cuando filtramos por código, no es indispensable mostrar las flechas de los campos del filtro, podemos filtrar directamente como en el siguiente ejemplo.

 Sub FiltrarDatos4()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         'El campo por el que queremos filtrar
         mCamposFiltro(0).Field = 1
         'El tipo de comparación
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         'Si es un número
         mCamposFiltro(0).IsNumeric = False
         'El valor de comparación
         mCamposFiltro(0).StringValue = "lizette"
         'Le pasamos los campos
         oDesFiltro.FilterFields = mCamposFiltro
         'Refrescamos el rango para ver el resultado del filtro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Que comprobamos que lo hace correctamente:

Ahora, veamos como filtrar un campo con dos condiciones.

 Sub FiltrarDatos5()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(1) As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 1
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         mCamposFiltro(0).IsNumeric = False
         mCamposFiltro(0).StringValue = "lizette"
         'Agregamos la segunda condición al mismo campo
         mCamposFiltro(1).Field = 1
         'Establecemos la relación con la condición anterior
         mCamposFiltro(1).Connection = com.sun.star.sheet.FilterConnection.OR
         mCamposFiltro(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         mCamposFiltro(1).IsNumeric = False
         mCamposFiltro(1).StringValue = "paola"        
 
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Observa muy bien la propiedad Connection, es muy importante establecer correctamente esta, para obtener el resultado deseado, solo tiene dos posibilidades, una “O” (OR) de disyunción o una “Y” (AND) de conjunción, muchos errores de filtros incorrectos, son por la incorrecta aplicación de este sencillo parámetro, comprobamos que nuestro filtro esta correcto, y veamos más ejemplos.

Ahora filtraremos con condiciones en dos campos diferentes.

 Sub FiltrarDatos6()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(1) As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 1
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         mCamposFiltro(0).IsNumeric = False
         mCamposFiltro(0).StringValue = "lidia"
         'Agregamos la segunda condición a otro campo
         mCamposFiltro(1).Field = 3
         'Establecemos la relación con la condición anterior (Y)
         mCamposFiltro(1).Connection = com.sun.star.sheet.FilterConnection.AND
         mCamposFiltro(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         'Ahora si buscamos por número
         mCamposFiltro(1).IsNumeric = True
         mCamposFiltro(1).NumericValue = 32
 
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Y una vez más lo comprobamos.

En los siguientes ejemplos, ya no te mostraré el resultado, dando por entendido, que estas comprobando, como yo, que el resultado esperado es el correcto. En el siguiente ejemplo, filtramos a todos los que tengan entre 30 y 35 años.

 Sub FiltrarDatos7()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(1) As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 3
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
         mCamposFiltro(0).IsNumeric = True
         mCamposFiltro(0).NumericValue = 30
         'Agregamos la segunda condición a otro campo
         mCamposFiltro(1).Field = 3
         mCamposFiltro(1).Connection = com.sun.star.sheet.FilterConnection.AND
         mCamposFiltro(1).Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
         mCamposFiltro(1).IsNumeric = True
         mCamposFiltro(1).NumericValue = 35
 
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Observa como hemos cambiado la propiedad “operador” (Operator) para satisfacer la condición, los posibles valores para esta propiedad, viene condicionados por la enumeración com.sun.star.sheet.FilterOperator, cuyos valores son.

com.sun.star.sheet.FilterOperator	Valor	Valor en Interfaz
com.sun.star.sheet.FilterOperator.EMPTY	0	Vacío
com.sun.star.sheet.FilterOperator.NOT_EMPTY	1	No vacío
com.sun.star.sheet.FilterOperator.EQUAL	2	Igual
com.sun.star.sheet.FilterOperator.NOT_EQUAL	3	No igual
com.sun.star.sheet.FilterOperator.GREATER	4	Mayor que
com.sun.star.sheet.FilterOperator.GREATER_EQUAL	5	Mayor o igual que
com.sun.star.sheet.FilterOperator.LESS	6	Menor que
com.sun.star.sheet.FilterOperator.LESS_EQUAL	7	Menor o igual que
com.sun.star.sheet.FilterOperator.TOP_VALUES	8	El mayor valor
com.sun.star.sheet.FilterOperator.TOP_PERCENT	9	El mayor porcentaje
com.sun.star.sheet.FilterOperator.BOTTOM_VALUES	10	El menor valor
com.sun.star.sheet.FilterOperator.BOTTOM_PERCENT	11	El menor porcentaje

En el siguiente ejemplo, seleccionamos los cinco registros con más edad.

 Sub FiltrarDatos8()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 3
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.TOP_VALUES
         mCamposFiltro(0).IsNumeric = True
         'Los cinco de más edad
         mCamposFiltro(0).NumericValue = 5
 
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Ahora, filtremos todos los nombres que comiencen por la letra “L”.

 Sub FiltrarDatos9()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 1
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         mCamposFiltro(0).IsNumeric = False
         'Observa el parametro de la condición
         mCamposFiltro(0).StringValue = "l.*"
         'Establecemos que use expresiones regulares
         oDesFiltro.UseRegularExpressions = True
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Observa como hemos establecido la propiedad para usar expresiones regulares (UseRegularExpressions) del descriptor del filtro para que tenga efecto nuestra condición. Las expresiones regulares son un concepto muy poderoso como podrás averiguarlo en tu buscador favorito.

Otra posibilidad bastante interesante de los filtros, es poder copiar el resultado en otra posición y dejar el origen intacto, veamos como.

 Sub FiltrarDatos10()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
 Dim oDestino As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 1
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
         mCamposFiltro(0).IsNumeric = False
         mCamposFiltro(0).StringValue = "gloria"
         'Le indicamos que queremos el resultado en otro lugar
         oDesFiltro.CopyOutputData = True
         'Y le indicamos donde, observa como obtenemos la dirección de la primer celda del rango
         oDestino = oRBD.ReferredCells().getCellByPosition(0,0).getCellAddress()
         'Después sumamos el ancho y alto del rango para dejar una columna y fila en blanco
         oDestino.Column = oDestino.Column + oRBD.ReferredCells.getColumns.getCount + 1
         oDestino.Row = oDestino.Row + oRBD.ReferredCells.getRows.getCount + 1
         'Establecemos el destino
         oDesFiltro.OutputPosition = oDestino
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

En versiones anteriores a la 3.2, la propiedad para indicar que queremos el resultado en otra posición se llama: SaveOutputPosition, esta propiedad solo aplicaba para rangos de datos, a partir de la versión 3.2 se homologó para usar la misma propiedad tanto en rangos de datos como en rangos de celdas. Si las celdas destino no están vacías, estas, serán reemplazadas sin preguntarte nada, modifica la macro, para evaluar esto y se lo notifiques al usuario.

Otra característica muy poderosa de los filtros, es la posibilidad de filtrar los registros, omitiendo los duplicados, copia varios registros iguales para que notes la diferencia, se hace de la siguiente manera.

 Sub FiltrarDatos11()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim sNombre As String
 Dim oRBD As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
 Dim oDestino As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     sNombre = "Direcciones"
     If oRangosBD.hasByName( sNombre ) Then
         oRBD = oRangosBD.getByName( sNombre )        
         oDesFiltro = oRBD.getFilterDescriptor()
         mCamposFiltro(0).Field = 0
         'Seleccionamos los registros NO vacíos
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
         'Le indicamos que solo queremos registros únicos
         oDesFiltro.SkipDuplicates = True
         oDesFiltro.CopyOutputData = True
         oDestino = oRBD.ReferredCells.getCellByPosition(0,0).getCellAddress()
         oDestino.Column = oDestino.Column + oRBD.ReferredCells.getColumns.getCount + 1
         oDesFiltro.OutputPosition = oDestino
         oDesFiltro.FilterFields = mCamposFiltro
         oRBD.refresh()
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Hasta ahora, hemos aplicados filtros, desde un rango de datos, pero los filtros no están limitados a estas áreas, al ser un método de rango de celdas (ScCellRangeObj), puedes aplicar un filtro a cualquier rango de celdas, la siguiente macro, toma el rango de celdas seleccionado y filtra los datos únicos dos columnas a la derecha, esta macro es muy útil para dejar listados únicos de lo que sea, verifícalo.

 Sub FiltrarDatos12()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oDesFiltro As Object
 Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
 Dim oDestino As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     'Nos aseguramos de que sea un rango de celdas
     If oSel.getImplementationName = "ScCellRangeObj" Then
         'Creamos un nuevo descriptor de filtro vacio (True)
         oDesFiltro = oSel.createFilterDescriptor(True)
 
         'Establecemos los campos
         mCamposFiltro(0).Field = 0
         mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
 
         'Establecemos el destino
         oDestino = oSel.getCellByPosition(0,0).getCellAddress()
         oDestino.Column = oSel.getRangeAddress().EndColumn + 2
 
         'Establecemos las propiedades del filtro
         oDesFiltro.ContainsHeader = False
         oDesFiltro.SkipDuplicates = True
         oDesFiltro.CopyOutputData = True
         oDesFiltro.OutputPosition = oDestino
         oDesFiltro.FilterFields = mCamposFiltro
 
         'Y lo aplicamos
         oSel.filter( oDesFiltro )
     Else
         MsgBox "No es un rango de celdas"
     End If
 
 End Sub

Hay diferencias importantes en comparación con los filtros en rango de datos; la primera es la forma de crear el descriptor de filtro, para un rango de celdas se usa el método createFilterDescriptor, al cual se le pasa el parámetro verdadero (True) si queremos que el nuevo descriptor este vacío y falso (False) si toma el existente en dicho rango de celdas, observa que también hemos establecido que este rango no tiene encabezados de campos (ContainsHeader), también cambia la propiedad para decirle que queremos copiar el resultado (CopyOutputData) en otra posición (homologada a partir de la versión 3.2, se usa la misma tanto en rangos de datos como en rangos de celda), y por ultimo, no refrescamos el rango, si no que aplicamos el filtro (filter), pasándole como parámetro el descriptor de filtro creado. Al igual que con los rangos de datos, si el destino contiene datos, serán reemplazados sin consultarte.

Veamos como usar las opciones del filtro especial, este tipo de filtro, nos permite establecer un rango como origen de los criterios para el filtro, lo que nos da la posibilidad de poder llegar a usar como criterios “todos” los campos de nuestra base de datos, dándonos absoluto control sobre los registros filtrados. Para demostrar el poder y versatilidad de esta opción, prepárate una buena tabla de datos, con al menos cinco campos y varias decenas de registros, si lo prefieres, puedes usar la hoja llamada “Películas”, presente en los ejemplos (descargar) que acompañan a estos apuntes.

El rango de nuestros datos es: “A1:G243”, establecemos un nombre a este rango; “Videoteca” si te parece, después, copia los títulos de los campos a la fila 250, selecciona el rango “A250:G255” y nómbrala como “Criterios”, por ultimo, selecciona la celda “A260” y la bautizamos como “Destino”, si es de tu agrado, todo lo anterior puedes hacerlo por código que ya sabes hacerlo, ¿verdad?. Recuerda que esta definición de nombres es en el menú Datos > Definir rango...

Nuestra tabla debe verse más o menos así.

La definición de criterios para el filtro especial, se puede hacer como lo hemos venido aprendiendo, por lo que no lo repetiremos aquí, concentrándonos en estudiar la forma de establecer estos criterios desde un rango de datos con nombre. Te sugiero, con esta macro en especifico, asignarle una combinación de teclas o un botón en alguna barra de herramientas con la finalidad de que podamos ejecutarla varias veces desde la interfaz del usuario e ir viendo en vivo y en directo los resultados que nos arrogue. Aquí la macro.

 Sub FiltrarEspecial1()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim oVideoteca As Object
 Dim oDestino As Object
 Dim oCriterios As Object
 Dim oDesFiltro As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     'Nos aseguramos de que existen nuestros tres rangos
     If oRangosBD.hasByName( "Videoteca" ) And oRangosBD.hasByName( "Criterios" ) And oRangosBD.hasByName( "Destino" ) Then
         'Obtenemos una referencia a los rangos origen
         oVideoteca = oRangosBD.getByName( "Videoteca" ).ReferredCells()
         oCriterios = oRangosBD.getByName( "Criterios" ).ReferredCells()
     oDestino = oRangosBD.getByName( "Destino" ).ReferredCells.getCellByPosition( 0,0 ).getCellAddress()
 
         'Obtenemos el descriptor del filtro del rango de criterios a partir del rango de datos
         oDesFiltro = oCriterios.createFilterDescriptorByObject( oVideoteca )
         'Sin duplicados
         oDesFiltro.SkipDuplicates = True
         'Que pueda usar expresiones regulares
         oDesFiltro.UseRegularExpressions = True
         'Queremos el resultado en otra posición
         oDesFiltro.CopyOutputData= True
         'Le indicamos donde
         oDesFiltro.OutputPosition = oDestino
         'El rango contiene encabezados de campos
         oDesFiltro.ContainsHeader = True
 
         'Filtramos con las opciones seleccionadas        
         oVideoteca.filter( oDesFiltro )        
     Else
         MsgBox "El rango de datos no existe"
     End If
 
 End Sub

Es importante notar que para el filtro especial usamos en nuevo método llamado createFilterDescriptorByObject, que se invoca desde el rango de criterios (oCriterios), pasándole como argumento, el rango de datos (oVideoteca), las restantes propiedades usadas en el ejemplo ya las hemos tratado.

Ahora, los criterios tienes que establecerlos en las celdas correspondientes, por ejemplo, para saber que películas tenemos de mi director favorito, usamos.

Ejecuta la macro y vemos el resultado.

Recuerda que la finalidad de establecer criterios y realizar filtros, es la de responder preguntas como por ejemplo, ¿cuantas películas tenemos del año 1974?, establece la condición y ejecuta la macro para responderla.

Nº	Titulo	Director	Genero	Año	País	Duración
				1974

Si quieres establecer más de un criterio y lo haces en una misma fila, estas usando el operador de conjunción “Y” (And), como la respuesta a la pregunta; ¿cuantas películas tenemos, realizadas en Japón realizadas en 1980?

Nº	Titulo	Director	Genero	Año	País	Duración
				1980	Japón

Si usas diferentes filas, estas usando el operador de disyunción “O” (Or), por ejemplo, para responder la pregunta; ¿cuantas películas tenemos de la India o del director Peter Greenaway?

Nº	Titulo	Director	Genero	Año	País	Duración
					India
		Peter Greenaway

Como declaramos en el descriptor de filtro, que se usarán expresiones regulares, puedes responder preguntas como; ¿cuantas películas empiezan con la letra “D”?

Nº	Titulo	Director	Genero	Año	País	Duración
	d.*

Como habrás notado, las posibilidades son enormes. Te queda de tarea, establecer las condiciones para responder la siguiente pregunta; ¿cuales películas duran entre 60 y 90 minutos?, la primer pista es; nota que estamos solicitando un rango especifico, por lo tanto, tienes que usar el operador “Y” (AND) para resolverlo, y ya casi te dije todo, recuerda que el rango de criterios es solo eso un rango y no estamos limitados a cambiar lo que queramos en este rango, con lo que ya te resolví la tarea, a trabajar.

Nº	Titulo	Director	Genero	Año	País	Duración
						??

Subtotales

Los subtotales nos permiten obtener información de las áreas de datos, agrupar y realizar una operación determinada en un grupo de datos, en el siguiente ejemplo y continuando trabajando con nuestra base de datos de películas, obtenemos la cantidad de títulos por director.

 Sub Subtotales1()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim oPeliculas As Object
 Dim oRango As Object
 Dim oDesSubTotal As Object
 Dim mCamposSubTotal(0) As New com.sun.star.sheet.SubTotalColumn
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     'Nos aseguramos de que existe el rango
     If oRangosBD.hasByName( "Peliculas" ) Then
         'Obtenemos una referencia a los rangos origen
         oPeliculas = oRangosBD.getByName( "Peliculas" )
         'Referencia al rango de celdas origen
         oRango = oPeliculas.ReferredCells()
         'Creamos un nuevo subtotal
         oDesSubTotal = oRango.createSubTotalDescriptor( True )
         'Que ordene de forma ascendente el grupo seleccionado
         oDesSubTotal.EnableSort = True
         oDesSubTotal.SortAscending = True
         'La columna en la que se usara la función especificada
         mCamposSubTotal(0).Column = 1
         mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
         'Agregamos la operación, al grupo deseado
         oDesSubTotal.addNew( mCamposSubTotal,2 )
         'Aplicamos el subtotal
         oRango.applySubTotals( oDesSubTotal, True )        
     End If
 
 End Sub

El área de datos (DataBaseRanges), solo la utilizamos para acceder (ReferredCells) al rango de celdas origen, si al método del rango de celdas para crear el descriptor del subtotal (createSubTotalDescriptor), se le pasa como argumento un valor verdadero (True), te creara un nuevo descriptor vacío, si es falso (False), tomará el existente, si lo hay, del rango seleccionado. Es importante que establezcas que ordene el grupo, si es ascendente o descendente, queda a tu criterio y necesidades, pero si no ordenas el grupo, y dependiendo de como estén tus datos origen, te puede dar resultados incorrectos. En la matriz de columnas del subtotal (com.sun.star.sheet.SubTotalColumn), establecemos el campo (Column) en el cual queremos hacer una operación, determinada por la propiedad función (Function), a su vez, basada en la enumeración com.sun.star.sheet.GeneralFunction, que puede tomar los siguientes valores.

com.sun.star.sheet.GeneralFunction	Valor	Valor en Interfaz
com.sun.star.sheet.GeneralFunction.NONE	0	Ninguna
com.sun.star.sheet.GeneralFunction.AUTO	1	Automático
com.sun.star.sheet.GeneralFunction.SUM	2	Suma
com.sun.star.sheet.GeneralFunction.COUNT	3	Cuenta
com.sun.star.sheet.GeneralFunction.AVERAGE	4	Promedio
com.sun.star.sheet.GeneralFunction.MAX	5	Máximo
com.sun.star.sheet.GeneralFunction.MIN	6	Mínimo
com.sun.star.sheet.GeneralFunction.PRODUCT	7	Producto
com.sun.star.sheet.GeneralFunction.COUNTNUMS	8	Cuenta solo números
com.sun.star.sheet.GeneralFunction.STDEV	9	Desviación estándar (Muestra)
com.sun.star.sheet.GeneralFunction.STDEVP	10	Desviación estándar (Población)
com.sun.star.sheet.GeneralFunction.VAR	11	Varianza (Muestra)
com.sun.star.sheet.GeneralFunction.VARP	12	Varianza (Población)

Al añadir los campos (addNew) al descriptor de subtotal, le pasamos como parámetros, la matriz de campos de columna del subtotal donde hemos establecido que campos y que operación haremos con ellos, ahí mismo, con el segundo parámetro, le indicamos por que campo queremos agrupar, por último, aplicamos (applySubTotals) los subtotales, cuyos parámetros son; el descriptor de subtotal y un valor booleano indicándole si deseamos reemplazar el subtotal actual (True), si lo hay, o agregamos al existente (False), usa con cuidado este parámetro, pues si agregas criterios “inconsistentes”, el resultado puede ser impreciso y desastroso visualmente hablando.

La siguiente macro, borra cualquier subtotal existente en el rango del área de datos.

 Sub Subtotales2()
 Dim oDoc As Object
 Dim oRangosBD As Object
 Dim oPeliculas As Object
 Dim oRango As Object
 
     oDoc = ThisComponent
     oRangosBD = oDoc.DataBaseRanges()
     If oRangosBD.hasByName( "Peliculas" ) Then
         oPeliculas = oRangosBD.getByName( "Peliculas" )
         oRango = oPeliculas.ReferredCells()
         'Quitamos el subtotal
         oRango.removeSubTotals()        
     End If
 
 End Sub

Como se muestra en el siguiente ejemplo, no es indispensable usar un área de datos para hacer uso de los subtotales, al ser métodos implementados en rangos de celda, podemos invocarlos desde cualquiera de estos. La siguiente macro, nos da el total de títulos por genero y suma la cantidad de minutos del mismo.

 Sub Subtotales3()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oDesSubTotal As Object
 Dim mCamposSubTotal(1) As New com.sun.star.sheet.SubTotalColumn
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
 
     'Si es una sola celda o un rango de celdas
     If oSel.getImplementationName = "ScCellRangeObj" Or oSel.getImplementationName = "ScCellObj" Then
         oSel = oSel.getSpreadSheet.createCursorByRange(oSel)
         'Expandimos a la región actual
         oSel.collapseToCurrentRegion()
         'Creamos el descriptor a partir de la selección
         oDesSubTotal = oSel.createSubTotalDescriptor( True )
         oDesSubTotal.EnableSort = True
         oDesSubTotal.SortAscending = True
 
         'Columna de titulos
         mCamposSubTotal(0).Column = 1
         mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
         'Columna de minutos
         mCamposSubTotal(1).Column = 6
         mCamposSubTotal(1).Function = com.sun.star.sheet.GeneralFunction.SUM
         'Aplicamos al genero
         oDesSubTotal.addNew( mCamposSubTotal,3 )
         oSel.applySubTotals( oDesSubTotal, True )    
     Else
         MsgBox "No es un rango de celdas"
     End If
 
 End Sub

El siguiente ejemplo, nos muestra cuantos títulos tenemos por país, y después otro subtotal nos indica cuantos títulos por genero, “dentro” de cada país tenemos.

 Sub Subtotales4()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oDesSubTotal As Object
 Dim mCamposSubTotal(0) As New com.sun.star.sheet.SubTotalColumn
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
 
     'Si es una sola celda o un rango de celdas
     If oSel.getImplementationName = "ScCellRangeObj" Or oSel.getImplementationName = "ScCellObj" Then
         oSel = oSel.getSpreadSheet.createCursorByRange(oSel)
         oSel.collapseToCurrentRegion()
         oDesSubTotal = oSel.createSubTotalDescriptor( True )
         oDesSubTotal.EnableSort = True
         oDesSubTotal.SortAscending = True
 
         'Columna de títulos
         mCamposSubTotal(0).Column = 1
         mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
         'Aplicamos al país
         oDesSubTotal.addNew( mCamposSubTotal,5 )
 
         'Columna de títulos
         mCamposSubTotal(0).Column = 1
         mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
         'Aplicamos al genero
         oDesSubTotal.addNew( mCamposSubTotal,3 )
 
         oSel.applySubTotals( oDesSubTotal, True )    
     Else
         MsgBox "No es un rango de celdas"
     End If
 
 End Sub

Observa, que hemos establecido la matriz de campos dos veces y usado el método addNew también dos veces para lograr el resultado deseado. Solo puedes sacar subtotales por hasta un máximo de tres grupos. El orden en que establezcas y agregues los campos, será el orden de precedencia de los subtotales. La obtención de subtotales, tiene un valor agregado bastante útil, al realizar la operación establecida en algún campo, la herramienta hace una comparación “exacta” de cada valor, por lo que te podrás dar cuenta si tienes campos con, por ejemplo, espacios vacíos de más, al principio o al final de el, o en el caso de tener valores de campo muy similares, si un registro tiene solo un error de “dedo”, con los subtotales te podrás dar cuenta de forma visual muy fácilmente, sobre todo cuando haces uso de la función cuenta, revisa los registros que solo tengan un solo registro, por supuesto, tu debes determinar si es correcto o no. Para ejemplificar esto, si estas usando la misma base de datos que yo, saca un subtotal por genero, busca y observa los géneros “Comedia” y “Drama”, y cuéntame cual es el error. Esa es tu tarea que no es mucha.

Validando datos

A estas alturas ya te habrás dado cuenta de la importancia de validar la información que el usuario le proporciona a un programa, una gran cantidad de programas, fallan en este tema de, creo yo, alta prioridad. Calc cuenta con una herramienta llamada Validez, presente en el menú Datos, que nos permite establecer criterios en celdas para la captura de información por parte del usuario y limitarlo en la medida de lo posible, a capturar dados válidos en el programa, informando, si así lo deseas, con cuadros de mensaje para ayudarle a introducir la información correctamente.

Veamos como establecer estas opciones por código. En el siguiente ejemplo, establecemos que las celdas seleccionadas, solo puedan aceptar números enteros del 1 al 12, es decir, un entero correspondiente a un mes del año.

 Sub ValidarDatos1()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     'Validamos que sea una celda o un rango de celdas
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         'Obtenemos la estructura validación
         oValidacion = oSel.getPropertyValue("Validation")
         'Establecemos sus propiedades
         With oValidacion
             'El tipo de validación
             .Type = com.sun.star.sheet.ValidationType.WHOLE
             'El operador de la validación
             .setOperator ( com.sun.star.sheet.ConditionOperator.BETWEEN )
             'Establecemos la primer condición
             .setFormula1 ( "1" )
             'Establecemos la segunda
             .setFormula2 ( "12" )
             'Que ignore las celdas en blanco
             .IgnoreBlankCells = True
             'Que muestre un mensaje al seleccionar la celda
             .ShowInputMessage = True
             'El titulo del mensaje
             .InputTitle = "Introduce el mes del año"
             'El mensaje
             .InputMessage = "Captura un número entre 1 y 12"
             'Que muestre un mensaje si la condición no se cumple
             .ShowErrorMessage = True
             'El estilo del mensaje de alerta
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             'El titulo del mensaje de error
             .ErrorTitle = "Dato erroneo"
             'El mensaje de error
             .ErrorMessage = "El valor introducido no es un número entre 1 y 12"
         End With 
         'Reinsertamos la propiedad para que surtan efecto los cambios
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Observa como estamos obteniendo la estructura de validación (getPropertyValue), pasándole como argumento, el nombre de la propiedad que nos interesa. Las restantes propiedades son: el tipo (Type), se refiere al tipo de validación que haremos y esta condicionado por la enumeración com.sun.star.sheet.ValidationType, cuyos posibles valores son.

com.sun.star.sheet.ValidationType	Valor	Valor en Interfaz
com.sun.star.sheet.ValidationType.ANY	0	Cualquier valor
com.sun.star.sheet.ValidationType.WHOLE	1	Entero
com.sun.star.sheet.ValidationType.DECIMAL	2	Decimal
com.sun.star.sheet.ValidationType.DATE	3	Fecha
com.sun.star.sheet.ValidationType.TIME	4	Hora
com.sun.star.sheet.ValidationType.TEXT_LEN	5	Longitud de texto
com.sun.star.sheet.ValidationType.LIST	6	Lista
com.sun.star.sheet.ValidationType.CUSTOM	7	Formula

Después, establecemos el operador (setOperator), para evaluar la condición, esta propiedad puede tomar los siguientes valores.

com.sun.star.sheet.ConditionOperator	Valor	Valor en Interfaz
com.sun.star.sheet.ConditionOperator.NONE	0	Ninguna
com.sun.star.sheet.ConditionOperator.EQUAL	1	Igual
com.sun.star.sheet.ConditionOperator.NOT_EQUAL	2	Distinta de
com.sun.star.sheet.ConditionOperator.GREATER	3	Mayor que
com.sun.star.sheet.ConditionOperator.GREATER_EQUAL	4	Mayor o igual
com.sun.star.sheet.ConditionOperator.LESS	5	Menor que
com.sun.star.sheet.ConditionOperator.LESS_EQUAL	6	Menor o igual
com.sun.star.sheet.ConditionOperator.BETWEEN	7	Entre
com.sun.star.sheet.ConditionOperator.NOT_BETWEEN	8	No entre
com.sun.star.sheet.ConditionOperator.FORMULA	9	Formula

Dependiendo de que operador selecciones, puede que necesites establecer el primer valor de comparación (setFormula1), y también el segundo (setFormula2), comúnmente los operadores “entre” (BETWEEN), y “no entre” (NOT_BETWEEN), son los que requieren los dos valores. Podemos determinar si la validación ignora o no, las celdas en blanco (IgnoreBlankCells), pero debes de saber que esta propiedad no determina el validar celdas vacía o no, si no que trabaja en conjunto la utilidad Detective del menú Herramientas, para determinar si una celda vacía se considera error o no. Podemos mostrar un mensaje (ShowInputMessage), cuando el usuario seleccione una celda, establecer su titulo (InputTitle), y el mensaje que verá (InputMessage), este texto orienta al usuario sobre los valores a introducir. En caso de que el valor capturado por el usuario, no satisfaga las condiciones de la validación, podemos mostrar un mensaje de error (ShowErrorMessage), de determinado estilo (ErrorAlertStyle), este estilo, determinará la acción a tomar con el valor capturado, este estilo esta determinado por los siguientes valores.

com.sun.star.sheet.ValidationAlertStyle	Valor	Valor en Interfaz
com.sun.star.sheet.ValidationAlertStyle.STOP	0	Stop
com.sun.star.sheet.ValidationAlertStyle.WARNING	1	Advertencia
com.sun.star.sheet.ValidationAlertStyle.INFO	2	Información
com.sun.star.sheet.ValidationAlertStyle.MACRO	3	Macro

Si estableces el valor en “stop” (STOP), se mostrara al usuario un mensaje (ErrorMessage) con un titulo (ErrorTitle) para informarle del error, al aceptar, la celda regresará al valor inmediato anterior, en los casos de “advertencia” (WARNING) e “información” (INFO), quedará a criterio del usuario si acepta o no el nuevo valor aun y cuando no cumpla la condición y en el caso de la opción “macro” (MACRO), puedes escoger una macro a ejecutar, para, por ejemplo, mostrar un mensaje más elaborado o realizar una operación más compleja. Por ultimo, es importante, “reinsertar” (setPropertyValue) la propiedad al objeto para que los cambios surtan efecto.

Para quitar una validación, solo establece el tipo (Type) en cualquier valor (ANY) y deshabilita el mensaje de entrada (ShowInputMessage).

 Sub ValidarDatos2()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     'Validamos que sea una celda o un rango de celdas
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         'Obtenemos la estructura validación
         oValidacion = oSel.getPropertyValue("Validation")
         'El tipo de validación
         oValidacion.Type = com.sun.star.sheet.ValidationType.ANY
         'Que muestre un mensaje al seleccionar la celda
         oValidacion.ShowInputMessage = False
         'Reinsertamos la propiedad para que surtan efecto los cambios
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

El tipo decimal, te permite capturar números con decimales.

 Sub ValidarDatos3()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.DECIMAL
             .setOperator ( com.sun.star.sheet.ConditionOperator.BETWEEN )
             .setFormula1 ( "0" )
             .setFormula2 ( "10" )
             .IgnoreBlankCells = True
             .ShowInputMessage = True
             .InputTitle = "Introduce la calificacion"
             .InputMessage = "Puedes usar decimales"
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.INFO
             .ErrorTitle = "Dato erroneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         'Reinsertamos la propiedad para que surtan efecto los cambios
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

En el siguiente ejemplo validamos que no sea una fecha futura, nota el uso de la función incorporada de Calc.

 Sub ValidarDatos4()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.DATE
             .setOperator ( com.sun.star.sheet.ConditionOperator.LESS_EQUAL )
             'Usamos una formula como condición
             .setFormula1 ( "TODAY()" )
             .IgnoreBlankCells = True
             .ShowInputMessage = True
             .InputTitle = "Fecha de Nacimiento"
             .InputMessage = "La fecha no puede ser futura"
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.WARNING
             .ErrorTitle = "Dato erroneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Ahora, solo puede capturar una hora que no sea entre 1 p.m. 3 p.m.

 Sub ValidarDatos5()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.TIME
             .setOperator ( com.sun.star.sheet.ConditionOperator.NOT_BETWEEN )
             'Introducir una hora que no sea entre 1 y 3 p.m.
             .setFormula1 ( "TIME(13;0;0" )
             .setFormula2 ( "TIME(15;0;0" )
             .IgnoreBlankCells = True
             .ShowInputMessage = True
             .InputTitle = "Hora de salida"
             .InputMessage = "La hora de salir"
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Dato erróneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Observa que estamos haciendo uso de la función de Calc, TIEMPO (TIME), que te devuelve el número de serie de la hora pasada, esto es necesario para establecer los limites correctamente. En el siguiente ejemplo, establecemos que los valores los tome desde un rango de celdas, observa que las referencias al rango son absolutas.

 Sub ValidarDatos6()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.LIST
             'Introducir valores desde un rango de celdas
             .setFormula1 ( "$I$2:$I$8" )
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Dato erróneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

También podemos establecer el rango desde un rango de celdas con nombre.

 Sub ValidarDatos7()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.LIST
             'Introducir valores desde un rango de celdas
             .setFormula1 ( "valores" )
             .ShowList = 2
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Dato erróneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Recuerda que los rangos con nombre los establecemos, en la interfaz del usuario, desde el cuadro de nombres de la barra de formulas, o por código, como hemos aprendido en este libro, aunque también puedes usar un nombre de área de datos definida en el menú Datos > Definir..., de hecho, puedes usar cualquier texto o formula que te devuelva un rango de celdas válido, si el rango de celdas tiene más de una columna, solo se usara la primer columna.

Observa que hemos usado una nueva propiedad (ShowList), cuando el tipo de la validación esta establecida en “lista” (LIST), podemos determinar si mostramos la flecha de lista de selección, el valor 2 determina que se muestre con los valores ordenados de forma ascendente, el valor 1 que se muestre pero que no ordene los valores, que los muestre tal y como están en el origen, y el valor 0 que no se muestre la lista de selección, algo no muy recomendable cuando se usa una lista. Estos valores corresponden a.

com.sun.star.sheet.TableValidationVisibility	Valor	Valor en Interfaz
com.sun.star.sheet.TableValidationVisibility.INVISIBLE	0	No mostrar lista
com.sun.star.sheet.TableValidationVisibility.UNSORTED	1	Mostrar desordenada
com.sun.star.sheet.TableValidationVisibility.SORTEDASCENDING	2	Mostrar ordenada ascendente

En el siguiente ejemplo, establecemos el origen de celdas con formulas de Calc, esta formula debe estar bien construida, si no, no te dará el rango correcto.

 Sub ValidarDatos8()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.LIST
             'Introducir valores desde un rango de celdas
             .setFormula1 ( "INDIRECT(ADDRESS(1;1)&"":""&ADDRESS(10;1))" )
             .ShowList = 2
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Dato erroneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Recuerda que la función DIRECCION (ADDRESS), nos devuelve una referencia en forma de texto, de acuerdo a los parámetros pasados, después, la función INDIRECTO (INDIRECT), nos devuelve, de una cadena de texto que tenga un rango de celda válido, la referencia a dicha celda. Como a la función DIRECCION se le pueda establecer entre sus parámetros el nombre de una hoja, te queda de tarea modificar la macro anterior para que el rango de celdas de la validación, este en una hoja diferente. El poder de hacerlo con funciones o desde código, es que la lista de validación la puedes actualizar dinámicamente, por ejemplo, una lista de clientes que va creciendo confirme vamos agregando registros o cualquier otro listado que vaya creciendo en sentido vertical.

Otra posibilidad, es introducir una lista fija de valores, o semifija, pues desde código la podemos actualizar siempre que queramos, veamos como.

 Sub ValidarDatos9()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.LIST
             'Introducir valores fijos            
             .setFormula1 ( "VALOR1;VALOR2;VALOR3" )
             .ShowList = 2
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Dato erróneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Ve a la interfaz del usuario y observa como “aparentemente”, si agrego los valores, pero observa que están en minúsculas, cuando nosotros los agregamos en mayúsculas, verifica desde la interfaz de usuario, desde Datos > Validez..., que esta forma de introducir los datos fijos, la detecta como si fuera un intervalo de celdas, lo cual es incorrecto, para que realmente la detecte como una lista “fija” de valores, tienes que pasarle cada valor como una cadena, como en el siguiente ejemplo.

 Sub ValidarDatos10()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.LIST
             'Introducir valores fijos            
             .setFormula1 ( """VALOR1"""&";"&"""VALOR2"""&";"&"""VALOR3""" )
             .ShowList = 2
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Dato erroneo"
             .ErrorMessage = "El valor introducido no es válido"
         End With 
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Pero ve que cantidad de comillas, ¿y si son muchos valores?, podemos hacer una función que agregue las comillas por nosotros, como en el siguiente ejemplo.

 Sub ValidarDatos11
 Dim sTmp As String
 Dim m 
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             'Valores a agregar
             m = Array("Uno","Dos","Tres","Cuatro","Cinco")
             'Los juntamos
             sTmp = JuntarEnLista(m)
             .Type = com.sun.star.sheet.ValidationType.LIST
             .ShowList = 2
             .setFormula1( sTmp )
         End With
         oSel.setPropertyValue("Validation", oValidacion)    
     End If
 End Sub
 
 'Toma una matriz y le agrega comillas a cada elemento
 'Devuelve una cadena con los elementos, separados por ";"
 Function JuntarEnLista( ByVal m ) As String
 Dim co1 As Long
 
     For co1 = LBound(m) To UBound(m)
         m(co1) = """" & m(co1) & """"
     Next
     JuntarEnLista = Join( m, ";" )
 
 End Function

Ahora si, es un poco más fácil agregar valores a una lista. El siguiente ejemplo te limita la entrada a una palabra de entre 5 y 10 letras, no te acepta solo números, solo letras o alfanuméricas.

 Sub ValidarDatos12()
 Dim oDoc As Object
 Dim oSel As Object
 Dim oValidacion As Object
 
     oDoc = ThisComponent
     oSel = oDoc.getCurrentSelection()
     If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
         oValidacion = oSel.getPropertyValue("Validation")
         With oValidacion
             .Type = com.sun.star.sheet.ValidationType.TEXT_LEN
             .setOperator ( com.sun.star.sheet.ConditionOperator.BETWEEN )
             .setFormula1 ( "5" )
             .setFormula2 ( "10" )
             .IgnoreBlankCells = True
             .ShowErrorMessage = True
             .ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
             .ErrorTitle = "Longitud errónea"
             .ErrorMessage = "La palabra debe ser de entre 5 y 10 caracteres"
         End With 
         'Reinsertamos la propiedad para que surtan efecto los cambios
         oSel.setPropertyValue("Validation", oValidacion)        
     End If
 
 End Sub

Tanto en la interfaz del usuario y aun más desde código, la validación de datos es un tema central en la programación, no lo tomes como un tema menor, es preferible agregar unas cuantas líneas más de código a correr el riesgo de que un dato inconsistente nos devuelva información inconsistente. Tienes instrucciones limitadas pero suficientes para evitarlo y tienes algo ilimitado; imaginación, inteligencia, pero sobre todo, sentido común, explótalos.

Agrupando datos

Cuando se maneja una gran cantidad de información, encontrar una manera simple y rápida de agrupar datos, puede ser la diferencia entre eficiencia e ineficiencia, los filtros, el ordenar y los subtotales, son herramientas que pueden apoyarnos. Cuando simplemente necesitemos agrupar por un rango de columnas o filas (que no es más que ocultarlas y mostrarlas de acuerdo a nuestras necesidades) podemos probar lo siguiente.

 Sub Agrupar1()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
     'Establecemos las propiedades del grupo
     With oRango
         'La hoja donde se creará
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         'La columna de inicio
         .StartColumn = 0
         'La columan de fin
         .EndColumn = 4
     End With
     'Creamos el grupo, 0 = por columnas
     oHojaActiva.group( oRango, 0 )
 
 End Sub

La macro anterior nos agrupará las columnas 1 a 5 en la interfaz del usuario, es muy importante que validez que la hoja y el rango de columnas establecidas, estén dentro de rangos válidos, por ejemplo, que el índice de hoja exista, si no, puedes provocar una caída de la aplicación, estamos verificando el ¿por que? de esto.

Observa como al agrupar (group), le pasamos una estructura de dirección de un rango (CellRangeAddress), vista muchas veces en este libro y como segundo argumento, si queremos que se agrupe por columnas (0) o por filas (1).

Ahora, agrupamos las primeras diez filas de la hoja activa.

 Sub Agrupar2()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         'Ahora establecemos el rango de filas
         .StartRow = 0
         .EndRow = 9
     End With
     'Y agrupamos, 1 = por filas
     oHojaActiva.group( oRango, 1 )
 
 End Sub

Por supuesto puedes establecer con la misma estructura, tanto las columnas como las filas como en el siguiente ejemplo.

 Sub Agrupar3()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartColumn = 9
         .EndColumn = 19
         .StartRow = 9
         .EndRow = 19
     End With
     'Agrupamos por columnas 10 a la 20
     oHojaActiva.group( oRango, 0 )
     'Agrupamos por filas 10 a 20
     oHojaActiva.group( oRango, 1 )
 
 End Sub

Desagrupar (ungroup) es trivial, usa los mismos argumentos que para agrupar.

 Sub Agrupar4()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartColumn = 0
         .EndColumn = 4
         .StartRow = 0
         .EndRow = 9
     End With
 
     'Deagrupamos filas y columnas
     oHojaActiva.ungroup( oRango, 0 )
     oHojaActiva.ungroup( oRango, 1 )
 
 End Sub

El rango usado para desagrupar (ungroup), no tiene que coincidir exactamente, en tamaño al rango original de agrupamiento, es suficiente con que este “dentro” del rango con que se agrupó, antes de comprobarlo, desde la hoja donde estés haciendo estas pruebas, ve al menú Datos > Agrupar y Esquema > Eliminar, lo que borrara cualquier grupo creado, después, ejecuta la primer macro siguiente e inmediatamente después, la segunda.

 Sub Agrupar5()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartColumn = 0
         .EndColumn = 9
         .StartRow = 0
         .EndRow = 19
     End With
 
     'Agrupamos filas (1-20) y columnas (1-10)
     oHojaActiva.group( oRango, 0 )
     oHojaActiva.group( oRango, 1 )
 
 End Sub

 Sub Agrupar6()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         'Solo indicamos la primer columna y fila
         .StartColumn = 0
         .EndColumn = 0
         .StartRow = 0
         .EndRow = 0
     End With
 
     'Desagrupamos filas y columnas
     oHojaActiva.ungroup( oRango, 0 )
     oHojaActiva.ungroup( oRango, 1 )
 
 End Sub

Observa como agrupamos por un rango y desagrupamos por otro, donde solo nos tenemos que asegurar que, el rango a desagrupar, este “dentro” del rango agrupado.

Cuando eliminas los grupos desde la interfaz del usuario, si el grupo esta contraído, las columnas o filas ocultas, se mostrarán inmediatamente, no sucede lo mismo cuando lo haces por código, vuelve a realizar la prueba anterior, pero asegúrate, de que, antes de desagrupar, contrae el grupo para ocultar su contenido, ahora sí, ejecuta la macro para desagrupar, notaras que el rango usado seguirá oculto, para evitar esto, antes de desagrupar, asegúrate de mostrar el detalle del rango como en el siguiente ejemplo.

 Sub Agrupar7()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         'Solo indicamos la primer columna y fila
         .StartColumn = 0
         .EndColumn = 9
         .StartRow = 0
         .EndRow = 19
     End With
 
     'Expandimos el grupo contenido en el rango
     oHojaActiva.showDetail( oRango )
 
     'Desagrupamos filas y columnas
     oHojaActiva.ungroup( oRango, 0 )
     oHojaActiva.ungroup( oRango, 1 )
 
 End Sub

El ejemplo anterior funcionará, siempre y cuando, el rango agrupado (group), corresponda “exactamente” con el rango mostrado (showDetail) y con el rango desagrupado (ungroup), lo cual, en ocasiones, podría no ser tan fácil de conocer. Si lo que quieres es eliminar cualquier grupo existente en una hoja, usamos.

 Sub Agrupar8()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
     'Eliminamos cualquier grupo de la hoja    
     oHojaActiva.clearOutline()
 
 End Sub

Con lo cual, es mucho más sencillo, eliminar cualquier grupo y volver a crear el necesario. Cuando agregas grupos, dependiendo de si exista o no previamente uno en el rango pasado, sucederá cualquiera de las siguientes acciones; si el rango ya contiene un grupo, se creará un nuevo nivel de agrupamiento, puedes crear hasta ocho niveles de ellos, si el rango no contiene un grupo, este, se agregará al mismo nivel, veámoslo con ejemplos, en el primero comprobamos que se agregan los grupos en niveles.

 Sub Agrupar9()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
     oHojaActiva.clearOutline()
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartColumn = 0
         .EndColumn = 1
     End With
     'Agrupamos las columnas 1-2
     oHojaActiva.group( oRango, 0 )
 
     With oRango
         .StartColumn = 0
         .EndColumn = 3
     End With
     'Agrupamos las columnas 1-4
     oHojaActiva.group( oRango, 0 )
 
     With oRango
         .StartColumn = 0
         .EndColumn = 5
     End With
     'Agrupamos las columnas 1-6
     oHojaActiva.group( oRango, 0 )
 
 End Sub

Ahora veamos como agregar al mismo nivel.

 Sub Agrupar10()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
     oHojaActiva.clearOutline()
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartRow = 0
         .EndRow = 3
     End With
     'Agrupamos la fila 1 a 4
     oHojaActiva.group( oRango, 1 )
 
     With oRango
         .StartRow = 5
         .EndRow = 8
     End With
     'Agrupamos la fila 4 a 7
     oHojaActiva.group( oRango, 1 )
 
     With oRango
         .StartRow = 10
         .EndRow = 13
     End With
     'Agrupamos la fila 9 a 12
     oHojaActiva.group( oRango, 1 )
 
 End Sub

Y lo comprobamos:

Puedes crear un grupo y ocultarlo inmediatamente, como en.

 Sub Agrupar11()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartColumn = 0
         .EndColumn = 9
     End With
     oHojaActiva.group( oRango, 0 )
 
     'Ocultamos el grupo contenido en el rango
     oHojaActiva.hideDetail( oRango )
 
 End Sub

A diferencia de mostrar un grupo (showDetail), cuando lo ocultas (hideDetail), el rango no necesariamente debe ser del mismo tamaño, con que apunte a cualquier celda dentro del rango a ocultar, funcionará. También tienes la posibilidad de mostrar cualquier nivel de agrupamiento, los demás niveles hacia arriba, si los hay, se cerraran.

 Sub Agrupar12()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oRango As New com.sun.star.table.CellRangeAddress
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
 
     With oRango
         .Sheet = oHojaActiva.getRangeAddress.Sheet
         .StartRow = 0
         .EndRow = 4
     End With
     oHojaActiva.group( oRango, 1 )
 
     With oRango
         .StartRow = 0
         .EndRow = 9
     End With
     oHojaActiva.group( oRango, 1 )
 
     With oRango
         .StartRow = 0
         .EndRow = 14
     End With
     oHojaActiva.group( oRango, 1 )
 
     With oRango
         .StartRow = 0
         .EndRow = 19
     End With
     oHojaActiva.group( oRango, 1 )
     'Mostramos el nivel 2
     oHojaActiva.showLevel( 2, 1 )    
 
 End Sub

El método para mostrar un nivel especifico (showLevel), solo usa dos parámetros, el nivel a mostrar como primero y como segundo parámetro, si el nivel a mostrar esta en columnas (0) o en filas (1). Para terminar este tema, veamos un ejemplo practico bastante útil, para que veas su uso, procura llamarla, teniendo el cursor dentro de un rango de celdas que tenga al menos dos pantallas de datos en sentido vertical, es decir, de filas, si tienes más filas, es mucho mejor pues se paginara más.

 Sub Agrupar13()
 Dim oDoc As Object
 Dim oHojaActiva As Object
 Dim oSel As Object
 Dim oCursor As Object
 Dim iNumFilPag As Integer
 Dim oRango As New com.sun.star.table.CellRangeAddress
 Dim co1 As Long
 
     oDoc = ThisComponent
     oHojaActiva = oDoc.getCurrentController.getActiveSheet()
     oSel = oDoc.getCurrentSelection()
     'Obligamos a seleccionar solo una celda
     If oSel.getImplementationName = "ScCellObj" Then
         'Creamos un cursor a partir de la celda seleccionada
         oCursor = oHojaActiva.createCursorByRange( oSel )
         'Expandimos a la región actual
         oCursor.collapseToCurrentRegion()
         'Obtenemos el número de filas visibles por página
         iNumFilPag = oDoc.getCurrentController.getVisibleRange.EndRow - oDoc.getCurrentController.getVisibleRange.StartRow - 1
         'Establecemos el primer nivel de agrupamiento en el total de filas
         With oRango
             .Sheet = oHojaActiva.getRangeAddress.Sheet
             .StartRow = 0
             .EndRow = oCursor.getRangeAddress.EndRow
         End With
         oHojaActiva.group( oRango, 1 )
 
         'Agregamos un segundo nivel de agrupamiento, página por página
         For co1 = 0 To oCursor.getRangeAddress.EndRow Step iNumFilPag
             oRango.StartRow = co1 + 1
             oRango.EndRow = co1 + iNumFilPag - 1
             oHojaActiva.group( oRango, 1 )
         Next
         'Para el ultimo rango si no es exacto
         If co1 > oCursor.getRangeAddress.EndRow Then
             oRango.StartRow = co1 - iNumFilPag + 1
             oRango.EndRow = oCursor.getRangeAddress.EndRow
             oHojaActiva.group( oRango, 1 )        
         End If
         oHojaActiva.ShowLevel( 1, 1 )
     Else
         MsgBox "Selecciona solo una celda"
     End If
 
 End Sub

Y mira que bien queda.

Claro que el área no esta limitada a que tenga datos, puedes establecer tus criterios pero sobre todo tus necesidades particulares para agrupar.

Si tienes dudas acerca de lo aquí explicado, tienes algún problema con AOO, o quieres ampliar la información, no dudes en dirigirte al Foro Oficial en español de Apache OpenOffice para Macros y API UNO