Referencia a rangos
Seguro que sabes, si eres usuario habitual de una hoja de calculo, que el trabajo con rangos es esencial en estos documentos, por lo mismo, el trabajo con rangos desde código OOo Basic es igualmente importante, ya vimos como aseguramos que estamos trabajando en una hoja de calculo, así que dejo a tu criterio esta validación. En la siguientes secciones nos centraremos en aprender como hacer referencia a distintos tipos de rangos para después poder manipularlos, darles formato o hacer con ellos lo que queramos.
Contents
Referencia a celdas individuales
Podemos acceder a las celdas de una hoja de calculo de varias maneras, principalmente por su nombre o por su posición, pero muy importante, primero tienes que acceder a la hoja donde están las celdas que te interesa manipular, como acceder a hojas es un tema que ya hemos tratado, pero en cada ejemplo podrás notar que repasamos estos conocimientos, la forma más simple de hacer referencia a una celda es por su nombre.
Sub AccesoCeldas1() Dim oHojaActiva As Object Dim oCelda As Object 'Referencia a la hoja activa oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia a la celda E5 oCelda = oHojaActiva.getCellRangeByName( "E5" ) 'Mostramos el contenido de la celda MsgBox oCelda.getString() 'Mostramos la hoja, columna y fila correspondiente a esta celda MsgBox "Hoja: " & oCelda.getCellAddress.Sheet & Chr(13) & _ "Columna: " & oCelda.getCellAddress.Column & Chr(13) & _ "Fila: " & oCelda.getCellAddress.Row End Sub
Observa como comprobamos en la ultima linea, que efectivamente hemos hecho referencia a la celda que nos interesa, es decir a la celda E5, que en columna y fila es la 4, por que recordamos que los números de columna y fila empiezan en 0, observa la estructura getCellAddress, esta, es muy importante pues a muchos métodos para manipular celdas, se les tienen que pasar estructuras como esta, solo tiene tres propiedades, la hoja (valor tipo integer, este, también empieza en 0) donde esta la celda referenciada, la columna (long) y la fila (long) de esta celda.
Ahora accedemos a una celda por su posición, recuerda que los índices de inicio desde código empiezan en 0, por lo que para hacer referencia a la celda E5, tenemos que poner la columna 4 y fila 4, el primer valor es para la columna y el segundo para la fila, no esta de más comentarte que tengas cuidado de no establecer una posición fuera de la hoja, pues te dará un error, por ejemplo, establecer el valor de la columna en 256 o superior si trabajos con la versión 2.x de OpenOffice.org, en la versión 3.x tenemos 1024 columnas para trabajar, por supuesto, si el valor de la fila y columna se la solicitas al usuario, “deberías” de validar que los valores proporcionados son correctos.
Sub AccesoCeldas2() Dim oHojaActiva As Object Dim oCelda As Object Dim Col As Long Dim Fil As Long oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Nos aseguramos que sea un valor con Val y que sea positivo con Abs Col = Abs(Val(InputBox("Dame la columna"))) Fil = Abs(Val(InputBox("Dame la fila"))) 'Nos aseguramos que estén dentro de los rangos correctos If Col < oHojaActiva.Columns.Count And Fil < oHojaActiva.Rows.Count Then 'Accedemos a la celda oCelda = oHojaActiva.getCellByPosition( Col,Fil ) MsgBox oCelda.getString() Else MsgBox "Valores de celda incorrectos" End If End Sub
Es frecuente que el acceso por nombre a una celda se use para establecer valores preestablecidos, como títulos de campos por ejemplo, y el acceso por posición es muy útil para realizar ciclos, como el ejemplo siguiente que inserta el año como titulo en la celda A1 y los meses del año de la celda A2 a la A13.
Sub AccesoCeldas3() Dim oHojaActiva As Object Dim oCelda As Object Dim co1 As Integer oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia a celda por nombre oCelda = oHojaActiva.getCellRangeByName( "A1" ) oCelda.setString( Year(Date) ) For co1 = 1 To 12 'Referencia a celda por posicion oCelda = oHojaActiva.getCellByPosition( 0,co1 ) oCelda.setString( Format( DateSerial(Year(Date),co1,1) ,"mmmm") ) Next End Sub
Observa que hemos estado usando el método getString() para obtener el contenido de una celda y setString(Valor As String) para establecerlo, más adelante veremos todas las posibilidades que tenemos para introducir u obtener datos de las celdas de nuestra hoja de calculo, así como sus diferencias.
Referencia a un rango de celdas
Podemos acceder a un rango de celdas por su nombre, usando el mismo método usado para acceder a una celda.
Sub AccesoRango1() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango A1:E5 oRango = oHojaActiva.getCellRangeByName( "A1:E5" ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub
Para acceder a un rango de celdas por su posición, hay que usar un método diferente: getCellRangeByPosition, que requiere de cuatro argumentos.
Sub AccesoRango2() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Seleccionamos el rango B1:B10 oRango = oHojaActiva.getCellRangeByPosition( 1,0,1,9 ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub
Observa que ahora usamos el método getCellRangeByPosition, los argumentos pasados a este método son cuatro, la columna y fila donde empieza nuestro rango y la columna y fila donde termina, recuerda que los números de fila y columna empiezan en 0, algunos piensan que los dos últimos argumentos son el ancho y alto del rango a usar, no es así, estos argumentos también son números de índices de columna y fila respectivamente y tienes que tener la precaución de establecer los segundos iguales o mas grandes que los primeros, sino, te dará un error en tiempo de ejecución y por supuesto sin sobrepasar el máximo de filas y columnas de la hoja de calculo. Observa también, como en la ultima línea seleccionamos el rango referenciado.
Otra posibilidad, es usar nombres definidos de rangos, es decir, aquellos que establecemos desde el “Cuadro de nombre” en la hoja de calculo, ya sabes, ese cuadro de lista desplegable (combobox) que esta al lado de la barra de formulas, que también puedes establecer desde el menú Insertar | Nombres | Definir... cuya teclas de acceso rápido son Ctrl + F3 . En el siguiente ejemplo, seleccionamos el rango de celdas llamado Datos. Toma nota de que si el rango no existe en la hoja desde donde se intenta referenciar, te dará un error.
Sub AccesoRango3() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Seleccionamos el rango por su nombre definido oRango = oHojaActiva.getCellRangeByName( "Datos" ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub
Por supuesto, si el nombre del rango no existe, te dará un error en tiempo de ejecución, este método no es sensible a mayúsculas o minúsculas. Para hacer referencia a la hoja correcta donde exista el rango con nombre, observa como obtenemos la hoja donde se encuentra. Si el usuario es el que proporciona el nombre del rango, como siempre, es mejor validar que el rango exista.
Sub AccesoRango4() Dim oHoja As Object Dim oRango As Object Dim oRangosPorNombre As Object Dim sNombreRango As String 'Referencia a todos los rangos con nombre en la hoja de calculo oRangosPorNombre = ThisComponent.NamedRanges() sNombreRango = Trim( InputBox( "Escribe el nombre del rango a seleccionar" ) ) 'Comprobamos que el rango exista If oRangosPorNombre.hasByName( sNombreRango ) Then oHoja = ThisComponent.getSheets.getByIndex(oRangosPorNombre.getByName(sNombreRango).getReferredCells.getRangeAddress.Sheet) 'Seleccionamos el rango por su nombre definido oRango = oHoja.getCellRangeByName( sNombreRango ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) Else MsgBox "El rango " & sNombreRango & " no existe en el documento" End If End Sub
No confundas estos nombres de rangos, con los que puedes establecer en el menú Datos | Definir rango..., ya que estos últimos se refieren a rangos considerados como una tabla de datos, de hecho, puedes tener un mismo nombre para un rango de celdas y para un rango de datos, pero son dos cosas diferentes, los segundos, los veremos más adelante.
De los rangos de celdas, también es posible obtener información, para ello se hace uso de la estructura CellRangeAddress a través del método getRangeAddress que te devuelve información de: la hoja donde esta el rango, la columna y fila donde comienza y la columna y fila donde acaba.
Sub AccesoRango5() Dim oHojaActiva As Object Dim oRango As Object Dim oDirCelda As Object Dim sTmp As String oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() oRango = oHojaActiva.getCellRangeByName( "Nombres" ) 'Obtenemos la información de la dirección oDirCelda = oRango.getRangeAddress() 'Construimos el texto informativo sTmp = "El rango esta en la hoja: " & oDirCelda.Sheet & Chr(13) & _ "Columna Inicio: " & oDirCelda.StartColumn & Chr(13) & _ "Fila Inicio: " & oDirCelda.StartRow & Chr(13) & _ "Columna Fin: " & oDirCelda.EndColumn & Chr(13) & _ "Fila Fin: " & oDirCelda.EndRow MsgBox sTmp End Sub
Esta estructura (getRangeAddress) también es usada por varios métodos para manipular rangos que veremos más adelante, por lo que es importante que la tengas presente.
Referencia a varios rangos de celdas
Cuando en la interfaz del usuario, hacemos la selección de un rango y mantenemos presionada la tecla Ctrl y hacemos un segunda selección de un rango y así sucesivamente, estamos hablando de un conjunto de rangos que tiene sus características particulares para su control y manejo. Para seleccionar varios rangos desde código, primero debemos crear el “contenedor” de estos rangos, veamos como.
Sub Acceso_A_Rangos1() Dim oHojaActiva As Object Dim oRangos As Object Dim oRango As Object Dim oDirRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Creamos el contender para los rangos oRangos = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges") 'Creamos la estructura CellRangeAddress necesaria oDirRango = oHojaActiva.getCellRangeByName( "A1:B2" ).getRangeAddress() 'Y lo agregamos al contenedor de rangos oRangos.addRangeAddress( oDirRango ,False ) 'Aquí solo hacemos referencia al rango oRango = oHojaActiva.getCellRangeByName( "E1:G2" ) 'Y lo agregamos al contenedor de rangos, nota como tenemos que usar 'el método getRangeAddress para pasarle el argumento correcto oRangos.addRangeAddress( oRango.getRangeAddress() ,False ) 'Aquí agregamos un rango directamente al contenedor 'toma en cuenta que se van acumulando oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "D4:E5" ).getRangeAddress() ,False ) 'Comprobamos que están correctos seleccionándolos ThisComponent.getCurrentController.select(oRangos) End Sub
No te confundas, los tres líneas donde se agrega el rango con el método addRangeAddress() son iguales, lo que cambia es la forma en que hacemos referencia a la dirección del rango, si vas empezando a programar, te recomiendo ir desglosando cada línea, con el tiempo y la experiencia, podrás concentrar código de forma más natural, por ahora, usa todas las líneas que consideres pertinentes para que tu código lo entiendas con solo verlo.
Por supuesto, también de estos rangos podemos obtener información, por ejemplo, los nombres de los rangos que contiene,
Sub Acceso_A_Rangos2() Dim oHojaActiva As Object Dim oRangos As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Creamos el contender para los rangos oRangos = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges") 'Agregamos los rangos que queramos oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "A1:A2" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "B4:B5" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "C7:C8" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "D10:D11" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "E13:E14" ).getRangeAddress() ,False ) 'Mostramos las direcciones de los rangos MsgBox oRangos.getRangeAddressesAsString() End Sub
Nota como los rangos es una cadena larga de las direcciones de los rangos separados por un “ ; ”, si lo quieres mejor presentable, reemplaza los puntos y comas por un salto de línea con el siguiente código.
'Lo único que hacemos es reemplazar los ; por saltos de linea sTmp = Join( Split(oRangos.getRangeAddressesAsString(),";"),Chr(13)) 'Mostramos el resultado MsgBox sTmp
Los rangos también se pueden remover de la colección.
Sub Acceso_A_Rangos4() Dim oHojaActiva As Object Dim oRangos As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Creamos el contender para los rangos oRangos = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges") 'Agregamos los rangos que queramos oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "A1:A2" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "B4:B5" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "C7:C8" ).getRangeAddress() ,False ) oRangos.addRangeAddress( oHojaActiva.getCellRangeByName( "D10:D11" ).getRangeAddress() ,False ) 'Comprobamos que estén los rangos MsgBox oRangos.getRangeAddressesAsString() 'Removemos el rango deseado oRangos.removeRangeAddress( oHojaActiva.getCellRangeByName( "D10:D11" ).getRangeAddress() ) 'Volvemos a verificar que se haya removido MsgBox oRangos.getRangeAddressesAsString() End Sub
Cuidado, si el rango que quieres remover no existe en la colección, te dará un error en tiempo de ejecución, lo mejor es validar antes que existe...
'Validamos que el rango a remover exista en la colección If oRangos.hasByName("Hoja1.D10:D11") Then 'Removemos el rango deseado oRangos.removeRangeAddress(oHojaActiva.getCellRangeByName( "D10:D11" ).getRangeAddress()) End If
El código anterior tiene un problema que tal vez te pueda causar un pequeño inconveniente, cuando consultamos con hasByName si el rango existe, observa que le pasamos el nombre del rango como texto ("Hoja1.D10:D11") y que incluye el nombre de la hoja donde esta el rango, pero observa como al método removeRangeAddress, le pasamos un objeto (oHojaActiva) que no necesariamente tiene que corresponder con el nombre de hoja (Hoja1), de tarea te queda garantizar que se corresponde uno con el otro, es decir, que el objeto que apunta a la hoja, efectivamente corresponda en nombre con el rango pasado.
Referencia a filas y columnas
Las filas y columnas de una hoja de calculo siguen siendo rangos de celdas, lo único que las caracteriza, en función de hacer referencia a ellas, es que abarcan la totalidad de celdas que contienen, como en.
Sub AccesoColumna1() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia a la columna A oRango = oHojaActiva.getCellRangeByName( "A1:A65536" ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub
Nota como tomamos de la fila 1 y hasta la 65536 que es el número total de filas con que por ahora cuentan las hojas de calculo de Calc, si bien el número de filas cambia realmente poco y hasta ahora, siempre hacia arriba, no es buena idea usar estos valores, es mejor usar un nombre y hacer referencia a la columna completa como veremos más adelante. Para acceder al nombre de una columna previamente establecido, usamos.
Sub AccesoColumna2() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia a la columna A que se nombró previamente oRango = oHojaActiva.getCellRangeByName( "Claves" ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub
Podemos hacer lo mismo con las filas.
Sub AccesoFilas1() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia a la fila 10 oRango = oHojaActiva.getCellRangeByName( "A10:AMJ10" ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub Sub AccesoFilas2() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia a la fila 15 que se nombró previamente oRango = oHojaActiva.getCellRangeByName( "Registros" ) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oRango) End Sub
OOo Basic cuenta con métodos específicos para manipular filas y columnas, pero propiamente hablando, al acceder por medio de estos métodos, dejan de ser rangos de celdas y pasan a ser objetos ScTableRowsObj y ScTableColumnsObj respectivamente como lo demostramos a continuación.
Sub AccesoColumna3() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango A1:E1 oRango = oHojaActiva.getCellRangeByName( "A1:E1" ) 'Mostramos el tipo de objeto MsgBox oRango.getImplementationName() 'Renombramos el objeto oRango accediendo a sus columnas oRango = oRango.getColumns() 'Verificamos el tipo de objeto MsgBox oRango.getImplementationName() End Sub
Trata de seleccionar el rango tal y como queda al final del código y veras que te da un error, también, trata de acceder a la información de depuración como se vio en capítulos anteriores y nota como implementan métodos y propiedades diferentes. Es el mismo caso para las filas.
Sub AccesoFilas3() Dim oHojaActiva As Object Dim oRango As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango A1:A10 oRango = oHojaActiva.getCellRangeByName( "A1:A10" ) 'Mostramos el tipo de objeto MsgBox oRango.getImplementationName() 'Renombramos el objeto oRango accediendo a sus filas oRango = oRango.getRows() 'Verificamos el tipo de objeto MsgBox oRango.getImplementationName() End Sub
Por lo anterior, la recomendación es que mantengas tu referencia original al rango y uses una segunda variable si necesitas acceder a las filas o columnas completas como te muestro en el siguiente ejemplo.
Sub AccesoFilasColumnas1() Dim oHojaActiva As Object Dim oRango As Object Dim oFil As Object Dim oCol As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango C5:E10 oRango = oHojaActiva.getCellRangeByName( "C5:E10" ) 'Mostramos el tipo de objeto MsgBox oRango.getImplementationName() 'Creamos una nueva referencia a las filas y columnas oCol = oRango.getColumns() oFil = oRango.getRows() 'Verificamos el tipo de objeto MsgBox oCol.getImplementationName() & " - " & oFil.getImplementationName() End Sub
Los métodos getColumns y getRows, siempre (aunque en el rango exista solo una fila o columna) te devolverá un conjunto de Columnas y Filas, para poder tener acceso a los métodos y propiedades disponibles en los rangos, así como para poder hacer selecciones, tenemos que acceder a cada fila o columna de forma individual o construir un contenedor de rangos para acceder a varias filas o columnas, veamos los dos casos.
Para seleccionar la primer columna del rango.
Sub AccesoFilasColumnas2() Dim oHojaActiva As Object Dim oRango As Object Dim oCol As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango C5:E10 oRango = oHojaActiva.getCellRangeByName( "C5:E10" ) 'Creamos una nueva referencia a la primer columna del rango la C oCol = oRango.getColumns.getByIndex(0) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oCol) End Sub
Para seleccionar la primer fila del rango.
Sub AccesoFilasColumnas3() Dim oHojaActiva As Object Dim oRango As Object Dim oFil As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango C5:E10 oRango = oHojaActiva.getCellRangeByName( "C5:E10" ) 'Creamos una nueva referencia a la segunda fila del rango la 6 oFil = oRango.getRows.getByIndex(1) 'Y lo seleccionamos ThisComponent.getCurrentController.select(oFil) End Sub
Por supuesto, podemos seleccionar una fila y una columna al mismo tiempo.
Sub AccesoFilasColumnas4() Dim oHojaActiva As Object Dim oRangos As Object oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Creamos el contender para los rangos oRangos = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges") 'Agregamos la columna E y la fila 10 al contenedor oRangos.addRangeAddress( oHojaActiva.getColumns().getByIndex(4).getRangeAddress(),False ) oRangos.addRangeAddress( oHojaActiva.getRows().getByIndex(9).getRangeAddress(),False ) 'Comprobamos que están correctos seleccionándolos ThisComponent.getCurrentController.select(oRangos) End Sub
Como ya notaste, podemos acceder directamente a la fila o columna que queramos de cualquier hoja por medio de la colección de estas (getColumns y getRows) y por medio del índice (getByIndex) a la fila o columna deseada.
oHojaActiva.getColumns.getByIndex(4) 'Columna E oHojaActiva.getRows.getByIndex(9) 'Fila 10
Para seleccionar todas las columnas o filas del rango indicado, usamos.
Sub AccesoFilasColumnas5() Dim oHojaActiva As Object Dim oRango As Object Dim oRangos As Object Dim oCol As Object Dim co1 As Long oHojaActiva = ThisComponent.getCurrentController.getActiveSheet() 'Referencia al rango A2:A8 oRango = oHojaActiva.getCellRangeByName( "A2:E8" ) 'Contenedor de rangos oRangos = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges") For co1 = 0 To oRango.getColumns.getCount() - 1 'Creamos una nueva referencia a cada columna oCol = oRango.getColumns.getByIndex(co1) 'La agregamos al contenedor de rangos oRangos.addRangeAddress( oCol.getRangeAddress(),False ) Next 'Y lo seleccionamos ThisComponent.getCurrentController.select( oRangos ) End Sub
Con un poco de ingenio, puedes hacerte tus propias funciones que te devuelvan filas o columnas enteras, pero esa es tu tarea.
Por ahora hemos visto como hacer referencia a diferentes tipos de rangos, en capítulos posteriores, veremos como manipular estos rangos, mover, insertar, copiar, etc., para terminar este tema de hacer referencia a rangos, veamos uno muy importante, la selección actual.
Referencia a la selección actual
Trabajar con la selección actual, es decir, con lo que el usuario tenga seleccionado al momento de llamar a una macro, es una actividad muy común y también, muy propensa a errores (ya sabes como son los usuarios), trataremos de mantener el control de la selección, siempre que sea posible.
Para acceder a la selección actual dentro de nuestra hoja de calculo usamos el método getCurrentSelection() de la siguiente manera.
Sub SeleccionActual1() Dim oSel As Object oSel = ThisComponent.getCurrentSelection() MsgBox oSel.getImplementationName() End Sub
Pero cuidado, nuestra selección pueden ser muchas cosas, vuelve a ejecutar la macro anterior teniendo cada vez seleccionado los siguientes elementos:
- Una celda
- Un rango de celdas
- Varios rangos de celdas
- Un objeto de dibujo
Debes de obtener, si lo hiciste en el mismo orden que yo, la siguiente lista de mensajes:
- ScCellObj
- ScCellRangeObj
- ScCellRangesObj
- com.sun.star.drawing.SvxShapeCollection
Observa la diferencia entre una sola celda (1) y un rango (2 y 3), y nota también la diferencia en un rango (2) y varios (3), esto es muy importante para actuar en consecuencia por que son objetos diferentes y por lo tanto implementan métodos y propiedades diferentes, por ejemplo, vamos a tratar de ver el contenido de una celda, ejecuta la macro siguiente, teniendo seleccionado cada uno de los tres primeros rangos mencionados.
Sub SeleccionActual2() Dim oSel As Object oSel = ThisComponent.getCurrentSelection() MsgBox oSel.getString() End Sub
Si seleccionaste una sola celda y esta contenía algo, la macro anterior te tiene que mostrar ese contenido, en las dos siguientes selecciones, de un rango y varios rangos, te tuvo que haber dado el error “Propiedad o método no encontrado”, por que efectivamente, el método getString() solo esta implementado en celdas individuales, por ello es muy importante discriminar la selección que haya hecho el usuario, primer intento.
Sub SeleccionActual3() Dim oSel As Object oSel = ThisComponent.getCurrentSelection() Select Case oSel.getImplementationName() Case "ScCellObj" MsgBox "Muy bien seleccionaste solo una celda" Case "ScCellRangeObj" MsgBox "Te pasaste un poco" Case "ScCellRangesObj" MsgBox "No tienes remedio" Case Else MsgBox "No se ni que seleccionaste" End Select End Sub
Ahora ya sabemos que es lo que el usuario selecciono, pero dependerá de que queramos hacer con la selección para actuar en consecuencia, por ejemplo, supongamos que nuestra macro requiere que el usuario seleccione una y solo una celda, podemos solo informarle como en el siguiente ejemplo.
Sub SeleccionActual4() Dim oSel As Object Dim oCelda As Object oSel = ThisComponent.getCurrentSelection() Select Case oSel.getImplementationName() Case "ScCellObj" oCelda = oSel MsgBox oCelda.getString() Case Else MsgBox "Se requiere seleccionar solo UNA celda" End Select End Sub
O podemos tomar solo la primer celda del rango o de los rangos que haya seleccionado como en.
Sub SeleccionActual5() Dim oSel As Object Dim oCelda As Object oSel = ThisComponent.getCurrentSelection() Select Case oSel.getImplementationName() Case "ScCellObj" oCelda = oSel Case "ScCellRangeObj" 'Si es un solo rango, accedemos a la primer celda oCelda = oSel.getCellByPosition( 0,0 ) Case "ScCellRangesObj" 'Si son varios rangos, primero accedemos al primer rango 'con getByIndex(0) y despues a la primer celda oCelda = oSel.getByIndex(0).getCellByPosition( 0,0 ) Case Else MsgBox "Se requiere seleccionar una celda" End Select If Not IsNull(oCelda) Then MsgBox oCelda.getString() End If End Sub
Nota que aun tenemos que evaluar que oCelda, apunte a un rango válido, recuerda que en la interfaz del usuario, puede haber más elementos seleccionables, como recomendación malévola, nunca confíes en el usuario, siempre válida sus datos. Es importante que recuerdes que en el caso de un rango, la primer celda siempre será la superior izquierda, y en un grupo de rangos, el rango 0 también siempre será el que este más arriba y a la izquierda, no importa si fue el ultimo rango en ser seleccionado. También, recuerda que la posición 0,0 en relación con la hoja siempre se refiere a la celda A1, pero en relación al rango seleccionado, puede ser cualquier otra.
En algunos casos, tal vez sea valido cualquiera de los tres tipos de rangos, es decir, que el método a usar esta implementado en los tres, por ejemplo.
Sub SeleccionActual6() Dim oSel As Object Dim oRango As Object oSel = ThisComponent.getCurrentSelection() Select Case oSel.getImplementationName() Case "ScCellObj", "ScCellRangeObj", "ScCellRangesObj" oRango = oSel Case Else MsgBox "Se requiere seleccionar un rango" End Select If Not IsNull(oRango) Then 'Borramos el contenido de las celdas oRango.clearContents(31) End If End Sub
El método clearContents, esta implementado en los tres tipos de rangos, por ello podemos llamarlo sin problemas, más adelante veremos a detalle este método.
Lo que he querido ilustrarte, es que, dependiendo de tus necesidades, tienes que discriminar una u otra cosa, pero siempre, y disculpa la necedad, siempre válida los datos.
Obteniendo información de rangos
En diversas ocasiones, es necesario saber donde estamos ubicados, es decir, en que hoja, fila y columna, los rangos cuentas con métodos y estructuras con esta información, algunas de ellas ya las hemos usado, también, muchos métodos de manipulación de rangos, requieren se les pasen las estructuras correctas de la dirección de los rangos, por ello es importante saber cuales son y como están estructuradas, veamos cuales son.
Sub InfoRangos1() Dim oSel As Object Dim oDir As Object Dim sTmp As String Dim co1 As Integer oSel = ThisComponent.getCurrentSelection() Select Case oSel.getImplementationName() Case "ScCellObj" 'Obtenemos la dirección de la celda oDir = oSel.getCellAddress() 'Mostramos sus datos, observa como hacemos uso del índice de la hoja 'oDir.Sheet para obtener el nombre de la hoja MsgBox "Hoja: " & ThisComponent.getSheets().getByIndex(oDir.Sheet).getName() & Chr(13) & "Columna: " & oDir.Column & Chr(13) & "Fila: " & oDir.Row Case "ScCellRangeObj" 'Si es un solo rango, obtenemos sus datos oDir = oSel.getRangeAddress() 'Construimos el texto informativo sTmp = "El rango esta en la hoja: " & oDir.Sheet & Chr(13) & _ "Columna Inicio: " & oDir.StartColumn & Chr(13) & _ "Fila Inicio: " & oDir.StartRow & Chr(13) & _ "Columna Fin: " & oDir.EndColumn & Chr(13) & _ "Fila Fin: " & oDir.EndRow MsgBox sTmp Case "ScCellRangesObj" 'Si son varios rangos, podemos obtener los datos de cada rango For co1 = 0 To oSel.getCount()-1 oDir = oSel.getByIndex(co1).getRangeAddress() sTmp = "El rango " & co1 & " esta en la hoja: " & oDir.Sheet & Chr(13) & _ "Columna Inicio: " & oDir.StartColumn & Chr(13) & _ "Fila Inicio: " & oDir.StartRow & Chr(13) & _ "Columna Fin: " & oDir.EndColumn & Chr(13) & _ "Fila Fin: " & oDir.EndRow MsgBox sTmp Next 'O podemos acceder a las direcciones de todos los rangos sTmp = Join( Split(oSel.getRangeAddressesAsString(),";"),Chr(13)) 'Mostramos el resultado MsgBox sTmp Case Else MsgBox "Se requiere seleccionar un rango de celdas" End Select End Sub
Observa que si es una sola celda se tiene que usar getCellAddress que obtiene: la hoja donde esta la celda, la columna y fila, no se te olvide que todo empieza en 0, cuando es un rango de celdas se usa getRangeAddress, que contiene, la hoja por índice donde esta el rango, la columna y fila donde empieza y la columna y fila donde termina, reitero, todo desde 0. Observa como en el caso de varios rangos, podemos acceder a cada uno, pero estos si cuentan con una propiedad de texto (getRangeAddressesAsString) que directamente nos devuelve las direcciones de los rangos en un formato claro para “casi” por cualquier usuario, por ejemplo “Datos.A2:E5”. No he encontrado, desconozco si existe, una propiedad o método similar para los casos de una celda y un rango, pero podemos crearnos nuestra propia función que lo haga por nosotros.
Sub InfoRangos2() Dim oSel As Object oSel = ThisComponent.getCurrentSelection() 'Usamos nuestra función personalizada MsgBox DireccionRango(oSel) End Sub 'Que linda quedo esta función Function DireccionRango(Rango As Object) As String Dim oSFA As Object Dim oDir As Object Dim mDatos 'Nos apoyamos en la funcion de Calc DIRECCION (ADDRESS) oSFA = createUnoService( "com.sun.star.sheet.FunctionAccess" ) 'Validamos el tipo de rango pasado Select Case Rango.getImplementationName() Case "ScCellObj" 'Si es una sola celda usamos getCellAddress oDir = Rango.getCellAddress 'Construimos la matriz de datos para la función mDatos = Array( oDir.Row+1, oDir.Column+1,4,1,Rango.getSpreadsheet.getName() ) DireccionRango = oSFA.callFunction("ADDRESS",mDatos()) Case "ScCellRangeObj" 'Si es un rango de celdas usamos getRangeAddress oDir = Rango.getRangeAddress() 'Obtenemos la celda superior izquierda del rango mDatos =Array(oDir.StartRow+1,oDir.StartColumn+1,4,1,Rango.getSpreadsheet.getName()) DireccionRango = oSFA.callFunction("ADDRESS",mDatos()) & ":" 'Ahora la celda inferior derecha del rango mDatos = Array( oDir.EndRow+1, oDir.EndColumn+1, 4 ) DireccionRango = DireccionRango & oSFA.callFunction("ADDRESS",mDatos()) Case "ScCellRangesObj" 'Esta ya la vimos DireccionRango = Join( Split(Rango.getRangeAddressesAsString(),";"),Chr(13)) End Select End Function
La función DIRECCION (ADDRESS) de Calc, nos devuelve en formato de texto, la referencia a la celda, indicándole la fila, columna, si queremos la referencia absoluta o relativa y la hoja, si usamos las funciones de Calc desde OOo Basic, el nombre de estas tenemos que indicarlas en ingles y pasarle los argumentos de la función en forma de matriz de datos, es muy importante pasarle, mínimo, los argumentos requeridos y estos, deben estar en el formato (string, integer, etc.) que “espera” la función, sino, tal y como sucede en la hoja de calculo, la función te retornara un error. Más adelante veremos un poco más detallado el uso de funciones incorporadas de la hoja de calculo, directamente en nuestras macros.
Una segunda versión de esta función, puede ser la siguiente, nota que no hacemos uso de ninguna función de hoja de calculo, sino que obtenemos los datos solo con propiedades del rango, puedes usar indistintamente cualquiera de las dos.
Function DireccionRango2(Rango As Object) As String Dim sTmp As String Select Case Rango.getImplementationName() Case "ScCellObj" sTmp = Rango.getSpreadsheet.getName() & "." & _ Rango.getColumns().getByIndex(0).getName() & _ Rango.getCellAddress.Row + 1 Case "ScCellRangeObj" sTmp = Rango.getSpreadsheet.getName() & "." & _ Rango.getColumns().getByIndex(0).getName() & _ Rango.getRangeAddress.StartRow + 1 & ":" & _ Rango.getColumns().getByIndex(Rango.getColumns().getCount()-1).getName() & _ Rango.getRangeAddress.EndRow + 1 Case "ScCellRangesObj" sTmp = Join( Split(Rango.getRangeAddressesAsString(),";"),Chr(13) ) End Select DireccionRango2 = sTmp End Function
Por ultimo, podemos saber el número de filas y columnas que tiene un rango usando los métodos getRows y getColumns respectivamente, estos métodos solo están disponibles cuando el rango es una sola celda y un rango, cuando son varios rangos tienes que ingeniártelas que no es complicado.
Sub InfoRangos3() Dim oSel As Object oSel = ThisComponent.getCurrentSelection() Select Case oSel.getImplementationName() Case "ScCellObj","ScCellRangeObj" MsgBox "Filas = " & oSel.getRows().getCount() & Chr(13) & _ "Columnas = " & oSel.getColumns().getCount() End Select End Sub
Los rangos, también, cuentan con una propiedad que nos devuelve la dirección del rango seleccionado, se llama AbsoluteName y el resultado como su nombre lo indica es con las referencias absolutas.
Sub InfoRangos4() Dim oSel As Object oSel = ThisComponent.getCurrentSelection() MsgBox oSel.AbsoluteName End Sub
Trabajando con Cursores
Un cursor, es un objeto que nos permite movernos por la hoja entera o dentro de un rango especificado sin afectar o modificar al rango a partir del cual se crea. Lo más usual es crearlo a partir de un rango para desplazarse dentro de el, pero además tiene métodos muy útiles para expandir por ejemplo a la región actual, que como sabes, es el rango de celdas “con datos” delimitado por filas y columnas en blanco, la región actual es un concepto sumamente, útil y poderoso cuando se programa, por ello le daremos un poco de énfasis, supongamos la siguiente tabla de datos.
Observa que el cursor esta en la celda D8 y que la región actual de este rango de datos es B2:F11, ahora, ejecuta la siguiente macro donde obligamos al usuario a seleccionar solo una celda, lo cual sabes, no necesariamente es así y solo es para fines didácticos, nota que usaremos una función creada en tema anterior que se llama DireccionRango, pero muy importante, cuando le pasemos como argumento una celda, esta trabajara bien, pero cuando intentemos pasarle un cursor, al ser un objeto diferente, te devolverá una cadena vacía, para solucionar esto, tenemos que agregar el tipo de objeto "ScCellCursorObj", busca y corrige la siguiente línea en dicha función personalizada.
Case "ScCellRangeObj", "ScCellCursorObj"
Observa que ahora evaluara si es un rango de celdas o un cursor, ahora si, ejecuta la siguiente macro.
Sub Cursores1() Dim oSel As Object Dim oCursor As Object 'Partimos de la selección oSel = ThisComponent.getCurrentSelection() 'Solo si es una sola celda If oSel.getImplementationName() = "ScCellObj" Then 'Mostramos la direccion de la celda seleccionada MsgBox DireccionRango( oSel ) 'Creamos un cursor a partir de esta celda oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Verificamos que apunten a la misma celda MsgBox DireccionRango( oCursor ) 'Nos movemos al inicio de la region actual oCursor.gotoStart() MsgBox DireccionRango( oCursor ) 'Nos movemos al final oCursor.gotoEnd() MsgBox DireccionRango( oCursor ) 'Expandimos el cursor a toda la región actual oCursor.collapseToCurrentRegion() MsgBox DireccionRango( oCursor ) 'Mostramos de nuevo la dirección de la celda seleccionada 'observa que esta no se a movido MsgBox DireccionRango( oSel ) Else MsgBox "Selecciona solo una celda" End If End Sub
Los cursores, al compartir la mayoría de los servicios de las celdas y los rangos, heredan la mayoría de sus métodos y propiedades, pero sus métodos particulares son lo que los hacen especiales e interesantes. Una pregunta recurrente en las listas de correo, es ¿como encuentro la siguiente fila libre?, con un cursor, esto es muy sencillo.
Sub Cursores2() Dim oSel As Object Dim oCursor As Object 'Partimos de la selección oSel = ThisComponent.getCurrentSelection() 'Solo si es una sola celda If oSel.getImplementationName() = "ScCellObj" Then 'Creamos un cursor a partir de esta celda oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Nos movemos al final oCursor.gotoEnd() 'La siguiente fila libre, sera esta fila mas 1 MsgBox oCursor.getRangeAddress().EndRow + 1 Else MsgBox "Selecciona solo una celda" End If End Sub
Por supuesto, algo muy practico es tener una función personalizada que nos encuentre la siguiente fila libre, algo como.
Function FilaLibre( Rango As Object) As Long Dim oCursor As Object oCursor = Rango.getSpreadSheet.createCursorByRange( Rango ) oCursor.gotoEnd() FilaLibre = oCursor.getRangeAddress.EndRow + 1 End Function
Y que usamos de una forma muy sencilla.
Sub Cursores3() Dim oSel As Object Dim oCursor As Object 'Partimos de la selección oSel = ThisComponent.getCurrentSelection() 'Solo funciona con celdas individuales o rangos de celdas Select Case oSel.getImplementationName() Case "ScCellObj", "ScCellRangeObj" MsgBox "La siguiente fila libre es la: " & FilaLibre( oSel ) End Select End Sub
Otros métodos con que cuentan los cursores son.
Sub Cursores4() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Nos movemos a la siguiente celda oCursor.gotoNext() MsgBox DireccionRango( oCursor ) 'Nos movemos a la celda anterior oCursor.gotoPrevious() MsgBox DireccionRango( oCursor ) 'Nos movemos dos columnas a la derecha y tres filas abajo oCursor.gotoOffset(2,3) MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Estos métodos hay que usarlos con cuidado, pues puedes “salirte” del rango donde estas trabajando y ya no podrás moverte con otros métodos como gotoStart o gotoEnd, por ejemplo, si te mueves al final de un rango (gotoEnd) y después te desplazas con gotoOffset(3,3), o sea tres columnas a la derecha y tres filas abajo, te saldrás del rango y ya no podrás regresar al inicio (gotoStart) como lo demostramos en el siguiente ejemplo, usa estos métodos con precaución.
Sub Cursores5() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) oCursor.gotoStart() 'Toma nota de esta dirección MsgBox DireccionRango( oCursor ) oCursor.gotoEnd() MsgBox DireccionRango( oCursor ) 'Nos movemos tres columnas a la derecha y tres filas abajo oCursor.gotoOffset(3,3) MsgBox DireccionRango( oCursor ) 'Intentamos regresar al inicio, nota como NO es la 'misma de la primer linea oCursor.gotoStart() MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Observa atentamente la siguiente imagen, nota en la barra de formulas que la formula mostrada esta encerrada en unas llaves, como sabes, estas llaves nos indican que dicha formula forma parte de una formula matricial, como no es el tema de este libro las formulas matriciales no entraremos en detalle alguno, pero como sabes, estas se introducen con la combinación de teclas Ctrl + ⇧ Shift + ↵ Enter y que ya creada, no puedes modificar solo una celda de dichas matrices, sino tienes que hacerlo con toda la matriz.
Con un cursor, podemos saber cual es el rango de una formula matricial, como lo demostramos en el siguiente ejemplo, probado en los datos mostrados en la imagen anterior.
Sub Cursores6() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet.createCursorByRange( oSel ) 'Expandimos el cursor a la matriz actual oCursor.collapseToCurrentArray() MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
En la siguiente imagen, observa las celdas combinadas, cuando establecemos el cursor en celdas combinadas y tratamos de obtener dicha dirección, solo te devolverá la celda superior izquierda de dicho rango, pero con un cursor podemos saber el rango completo de estas celdas combinadas.
Sub Cursores7() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Comprobamos que solo devuelve una celda MsgBox DireccionRango( oSel ) 'Expandimos el cursor a todas las celdas combinadas oCursor.collapseToMergedArea() MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Podemos expandir el cursor para que abarque todas las columnas de la región actual.
Sub Cursores8() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet.createCursorByRange( oSel ) 'Expandimos el cursor a la región actual oCursor.collapseToCurrentRegion() MsgBox DireccionRango( oCursor ) 'Y luego a todas las columnas oCursor.expandToEntireColumns() MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Intuirás que también es posible hacerlo en las filas.
'Expandimos el cursor a la región actual oCursor.collapseToCurrentRegion() MsgBox DireccionRango( oCursor )
'Y luego a todas las columnas oCursor.expandToEntireRows() MsgBox DireccionRango( oCursor )
Ten cuidado cuando uses los dos métodos anteriores, si los ejecutas uno detrás de otro, tendrás un cursor con tu hoja completa, que no descartes usar un día, lo importante es que siempre tengas control y sepas lo que estas haciendo.
Sub Cursores10() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Expandimos el cursor a la region actual oCursor.collapseToCurrentRegion() MsgBox DireccionRango( oCursor ) 'Y luego a todas las filas oCursor.expandToEntireRows() MsgBox DireccionRango( oCursor ) 'Nota como devuelve la hoja entera oCursor.expandToEntireColumns() MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
También puedes ampliar tu cursor el número de columnas y filas que necesites, toma en cuenta que la celda superior izquierda no cambia.
Sub Cursores11() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Expandimos el cursor a la región actual oCursor.collapseToCurrentRegion() MsgBox DireccionRango( oCursor ) 'Y luego cambiamos el cursor a 2 columnas y 5 filas oCursor.collapseToSize( 2,5 ) MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Nota que estamos “cambiando” el tamaño del cursor, si lo que quieres realmente es expandirlo, tienes que sumarle el ancho y alto de la región actual, como en el siguiente ejemplo.
Sub Cursores12() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet.createCursorByRange( oSel ) 'Expandimos el cursor a la región actual oCursor.collapseToCurrentRegion() MsgBox DireccionRango( oCursor ) 'Y luego ampliamos el cursor 1 columna y 5 filas oCursor.collapseToSize( oCursor.getColumns.getCount()+1, oCursor.getRows.getCount()+5 ) MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Nota como hemos usado algunos métodos vistos en el tema anterior ( getColumns.getCount ), como ya lo mencionamos, esto es posible por que los cursores comparten la mayoría de los servicios con los rangos. Casi para finalizar, veamos dos métodos más de los cursores.
Sub Cursores13() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) 'Vamos al inicio del área usada, el argumento False, evita que se 'expanda la selección del rango oCursor.gotoStartOfUsedArea( False ) MsgBox DireccionRango( oCursor ) 'Ahora vamos al final oCursor.gotoEndOfUsedArea( False ) MsgBox DireccionRango( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
En una hoja de calculo, el “área de usuario”, es aquel rango “efectivamente” usado, es muy dinámico pues varia de acuerdo a su uso y eliminación, en una hoja nueva el inicio y el final de esta área siempre será el mismo, la celda A1, si tienes “una” sola celda usada, esta, será el inicio y el fin de tu área de usuario, la cosa cambia en cuanto tienes dos o más celdas usadas, la regla es: el inicio de tu área de usuario será la celda (“con datos”) que este más cercana al extremo superior izquierdo de la hoja y el final será la celda (“con datos”) que este más cercana al extremo inferior derecho de dicha hoja, y digo que es dinámica, por que si eliminas datos o celdas, esta área se ira ajustando conforme a estos cambios.
Ahora si, para terminar, un cursor también lo puedes usar para establecer una selección, como en el ejemplo siguiente donde seleccionamos la región actual del rango.
Sub Cursores14() Dim oSel As Object Dim oCursor As Object oSel = ThisComponent.getCurrentSelection() If oSel.getImplementationName() = "ScCellObj" Then oCursor = oSel.getSpreadSheet().createCursorByRange( oSel ) oCursor.collapseToCurrentRegion() ThisComponent.getCurrentController.select( oCursor ) Else MsgBox "Selecciona solo una celda" End If End Sub
Con lo cual observamos que estos cursores con bastante versátiles.
Selecciones especiales
Los rangos cuentan con unos métodos bastante interesantes (y útiles) para hacer algunas selecciones especiales, por ejemplo, podemos seleccionar las celdas vacías de un rango como en.
Sub SeleccionesEspeciales1() Dim oSel As Object Dim oSelEsp As Object 'Accedemos a la selección actual oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas vacías oSelEsp = oSel.queryEmptyCells() 'Y las seleccionamos ThisComponent.getCurrentController.select( oSelEsp ) End Sub
Como hemos visto a lo largo de estas notas, no es siempre necesario que selecciones, puedes manipular el rango obtenido sin que esto sea necesario, si una celda tiene aunque sea solo un espacio o algún carácter especial no visible (tabuladores, saltos de página), esta, ya no será vacía y no la obtendrás por este método. Un punto importante, es que este método siempre retorna un conjunto de rangos (ScCellRangesObj), los cuales tienes que manipular como ya lo hemos aprendido.
También podemos seleccionar celdas de acuerdo a su contenido, en el siguiente ejemplo seleccionamos las celdas con texto.
Sub SeleccionesEspeciales2() Dim oSel As Object Dim oSelEsp As Object 'Accedemos a la seleccion actual oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas con texto oSelEsp = oSel.queryContentCells( 4 ) 'Y las seleccionamos ThisComponent.getCurrentController.select( oSelEsp ) End Sub
Ahora, las celdas con formulas.
Sub SeleccionesEspeciales3() Dim oSel As Object Dim oSelEsp As Object oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas con formulas oSelEsp = oSel.queryContentCells( com.sun.star.sheet.CellFlags.FORMULA ) ThisComponent.getCurrentController.select( oSelEsp ) End Sub
Los valores posibles para el argumento de este método son los siguientes, valores que también se usan para borrar datos como se ve en el tema [#6.4.3.Borrando datos|outline 6.4.3.Borrando datos]
Constante | Valor |
---|---|
com.sun.star.sheet.CellFlags.VALUE | 1 |
com.sun.star.sheet.CellFlags.DATETIME | 2 |
com.sun.star.sheet.CellFlags.STRING | 4 |
com.sun.star.sheet.CellFlags.ANNOTATION | 8 |
com.sun.star.sheet.CellFlags.FORMULA | 16 |
com.sun.star.sheet.CellFlags.HARDATTR | 32 |
com.sun.star.sheet.CellFlags.STYLES | 64 |
com.sun.star.sheet.CellFlags.OBJECT | 128 |
com.sun.star.sheet.CellFlags.EDITATTR | 256 |
com.sun.star.sheet.CellFlags.FORMATTED | 512 |
Puedes usar de forma indistinta el valor numérico o su constante como se ve en los dos ejemplos anteriores y también sumar los valores para tener múltiples combinaciones, como en el ejemplo siguiente donde seleccionamos las celdas con texto y valores.
Sub SeleccionesEspeciales4() Dim oSel As Object Dim oSelEsp As Object oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas con texto y valores oSelEsp = oSel.queryContentCells( 5 ) ThisComponent.getCurrentController.select( oSelEsp ) End Sub
Como ya se mencionó, estos métodos siempre devuelven un conjunto de rangos (ScCellRangesObj), por lo que es muy sencillo saber si hay o no resultados, en el siguiente ejemplo, ya no seleccionamos, pero informamos si hubo o no celdas con el criterio especificado, para este ejemplo, las celdas que tengan notas, observa que usamos una función personalizada (DireccionRango2), que ya hemos usado anteriormente.
Sub SeleccionesEspeciales5() Dim oSel As Object Dim oSelEsp As Object oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas con notas oSelEsp = oSel.queryContentCells( 8 ) If oSelEsp.getCount = 0 Then MsgBox "No hay celdas con notas" Else 'Mostramos la dirección de los rangos encontrados MsgBox DireccionRango2( oSelEsp ) End If End Sub
Ya vimos como seleccionar formulas, pero también podemos seleccionar formulas de acuerdo a su resultado, por ejemplo, seleccionemos las celdas que contengan error.
Sub SeleccionesEspeciales6() Dim oSel As Object Dim oSelEsp As Object oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas con formulas cuyo resultado sea error oSelEsp = oSel.queryFormulaCells( 4 ) If oSelEsp.getCount = 0 Then MsgBox "No hay celdas con errores" Else MsgBox DireccionRango2( oSelEsp ) End If End Sub
El argumento de este método, solo acepta los tres valores siguiente.
Constante | Valor |
---|---|
com.sun.star.sheet.FormulaResult.VALUE | 1 |
com.sun.star.sheet.FormulaResult.STRING | 2 |
com.sun.star.sheet.FormulaResult.ERROR | 4 |
Los cuales, podemos combinar.
Sub SeleccionesEspeciales7() Dim oSel As Object Dim oSelEsp As Object oSel = ThisComponent.getCurrentSelection() 'Recuperamos las celdas con formulas cuyo resultado sea texto o valor oSelEsp = oSel.queryFormulaCells( 3 ) If oSelEsp.getCount = 0 Then MsgBox "No hay celdas con texto o valor" Else MsgBox DireccionRango2( oSelEsp ) End If End Sub
Los siguiente métodos requieren una atención especial, supongamos la siguiente lista de datos.
Fila/Columna | A | B | C | D | E |
---|---|---|---|---|---|
1 | 3 | 3 | 5 | 3 | 4 |
2 | 5 | 2 | 1 | 2 | 4 |
3 | 3 | 1 | 2 | 5 | 5 |
4 | 1 | 1 | 3 | 4 | 5 |
5 | 1 | 5 | 5 | 5 | 4 |
6 | 4 | 1 | 4 | 1 | 3 |
7 | 1 | 3 | 1 | 1 | 5 |
8 | 5 | 1 | 1 | 5 | 3 |
9 | 5 | 3 | 3 | 2 | 3 |
10 | 3 | 2 | 4 | 5 | 1 |
Selecciona el rango A1:A10 y ejecuta la siguiente macro:
Sub SeleccionesEspeciales8() Dim oHojaActiva As Object Dim oRango As Object Dim oSelEsp As Object Dim oCelda As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() 'El rango a evaluar oRango = oHojaActiva.getCellRangeByName("A1:A10") 'La celda de comparación oCelda = oHojaActiva.getCellRangeByName("A1") 'Buscamos las celdas diferentes por columna oSelEsp = oRango.queryColumnDifferences( oCelda.getCellAddress ) If oSelEsp.getCount = 0 Then MsgBox "No se encontraron celdas" Else 'Las seleccionamos ThisComponent.getCurrentController.select( oSelEsp ) End If End Sub
Que te tiene que seleccionar.
Observa como seleccionó las celdas con un valor diferente al de la celda de comparación, es muy importante entender que, de la celda de comparación, “únicamente toma la fila” de la dirección para la comparación, intenta pasarle a esta misma macro la celda C1, observa el resultado, después pásale la celda C2 y observa el resultado. Con el siguiente ejemplo, creo, queda bastante claro como trabaja este método, observa el rango pasado y la celda de comparación, el valor comparado es el valor de la fila respectiva para cada columna.
Sub SeleccionesEspeciales9() Dim oHojaActiva As Object Dim oRango As Object Dim oSelEsp As Object Dim oCelda As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() oRango = oHojaActiva.getCellRangeByName("A1:C10") oCelda = oHojaActiva.getCellRangeByName("B10") oSelEsp = oRango.queryColumnDifferences( oCelda.getCellAddress ) If oSelEsp.getCount = 0 Then MsgBox "No se encontraron celdas" Else ThisComponent.getCurrentController.select( oSelEsp ) End If End Sub
Que nos devuelve.
Por ultimo, ejecuta la macro, llamando al rango completo de pruebas, con la celda de comparación que quieras, como en.
Sub SeleccionesEspeciales10() Dim oHojaActiva As Object Dim oRango As Object Dim oSelEsp As Object Dim oCelda As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() oRango = oHojaActiva.getCellRangeByName("A1:E10") oCelda = oHojaActiva.getCellRangeByName("A3") oSelEsp = oRango.queryColumnDifferences( oCelda.getCellAddress ) If oSelEsp.getCount = 0 Then MsgBox "No se encontraron celdas" Else ThisComponent.getCurrentController.select( oSelEsp ) End If End Sub
Y comprueba que es correcto el resultado. El mismo tipo de comparación lo podemos hacer pero por filas, como en el siguiente ejemplo, usando la misma tabla de datos.
Sub SeleccionesEspeciales11() Dim oHojaActiva As Object Dim oRango As Object Dim oSelEsp As Object Dim oCelda As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() oRango = oHojaActiva.getCellRangeByName("A10:E10") oCelda = oHojaActiva.getCellRangeByName("C10") 'Buscamos las celdas diferentes por filas oSelEsp = oRango.queryRowDifferences( oCelda.getCellAddress ) If oSelEsp.getCount = 0 Then MsgBox "No se encontraron celdas" Else ThisComponent.getCurrentController.select( oSelEsp ) End If End Sub
Y el resultado.
La lógica es exactamente la misma de hacerlo por columnas, solo que ahora, “solo toma la columna” de la celda de comparación, así que solo hagamos un ejemplo más con el rango completo de datos.
Sub SeleccionesEspeciales12() Dim oHojaActiva As Object Dim oRango As Object Dim oSelEsp As Object Dim oCelda As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() oRango = oHojaActiva.getCellRangeByName("A1:E10") oCelda = oHojaActiva.getCellRangeByName("D1") 'Buscamos las celdas diferentes por filas oSelEsp = oRango.queryRowDifferences( oCelda.getCellAddress ) If oSelEsp.getCount = 0 Then MsgBox "No se encontraron celdas" Else ThisComponent.getCurrentController.select( oSelEsp ) End If End Sub
Que nos selecciona.
Lo interesante de estos métodos, es que los valores de las celdas, no tienen que ser necesariamente texto, puede ser cualquier tipo de dato, incluyendo formulas, en cuyo caso, se tomará el tipo de resultado de esta.
El siguiente método nos sirve para saber el rango de celdas comunes a dos rangos, observa la siguiente imagen, la macro de ejemplo, nos tiene que seleccionar el rango en verde.
Sub SeleccionesEspeciales13() Dim oHojaActiva As Object Dim oRango1 As Object Dim oRango2 As Object Dim oSelEsp As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() oRango1 = oHojaActiva.getCellRangeByName("B2:I11") oRango2 = oHojaActiva.getCellRangeByName("G8:N17") 'Buscamos las celdas comunes oSelEsp = oRango1.queryIntersection( oRango2.getRangeAddress ) If oSelEsp.getCount = 0 Then MsgBox "No hay nada en común" Else ThisComponent.getCurrentController.select( oSelEsp ) End If End Sub
Para terminar este tema, veamos un método muy interesante, nos permite seleccionar, del rango invocado, solo las celdas visibles. Observa la siguiente imagen.
Nota en los encabezados de fila que tenemos muchas filas ocultas, con la siguiente macro, mostramos la dirección de los rangos visibles.
Sub SeleccionesEspeciales14() Dim oHojaActiva As Object Dim oRango As Object Dim oVisibles As Object oHojaActiva = ThisComponent.getCurrentController().getActiveSheet() oRango = oHojaActiva.getCellRangeByName("A1:G243") 'Buscamos las celdas visibles oVisibles = oRango.queryVisibleCells() If oVisibles.getCount = 0 Then MsgBox "No hay celdas ocultas" Else 'Mostramos la dirección de los rangos visibles MsgBox DireccionRango2( oVisibles ) End If End Sub
Cuando haces un filtro automático o especial y cuando calculas subtotales e intentas hacer operaciones de copiado, la copia resultante, solo contendrá las celdas visibles, cuando agrupas u ocultas filas o columnas manualmente, la copia incluirá aun las celdas ocultas, pero con este método siempre obtendrás las celdas visibles, que, en conjunto con los conocimientos del siguiente capitulo, puedes hacer tu propia versión de copiar solo las filas visibles.
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 |