Macros en OOo Calc: ¿Cómo crear tus propias funciones?

Una vez más me encuentro colaborando con este excelente blog de IT. Actualmente estoy capacitando a personal de una importante empresa con casa central en bahía blanca en el uso de OpenOffice. Esta empresa está migrando todos sus sistemas a GNU/Linux y como un aficionado a OOo tuve la oportunidad de dictar seminarios y cursos acerca de esta maravillosa suite de oficina open-source.

Esto también me sirvió para incorporar conocimientos avanzados de estas aplicaciones, principalmente en Calc, por ejemplo en temas como funciones, vincular datos de diferentes libros, piloto de datos (tablas dinámicas de Excel), filtros y áreas de bases de datos, etc. Pero en esta ocasión voy a escribir sobre lo que me resultó más interesante: macros y como crear funciones.

Para comenzar, una macro es una secuencia de comandos guardada para uso posterior. Las macros de OOo se escriben en un lenguaje de programación llamado OpenOffice.org Basic, esto implica que estas macros no funcionan en Excel así como tampoco funcionan las macros de Excel en Calc. Las macros de OOo se organizan en diferentes contenedores de librerías: "Mis macros" contiene macros creadas por el usuario; "Macros de OpenOffice.org" contiene macros incluidas en OOo y no deben modificarse; el resto de los contenedores son los archivos actualmente abiertos. Las macros incluidas en contenedores de documentos abiertos son sólo visibles por los archivos a los que pertenecen, mientras que las macros incluidas en "Mis Macros" y "Macros de OpenOffice.org" son visibles por todos los archivos.

Dentro de cada contenedor, las macros se organizan en librerías de macros. Las librerías incluidas en el contenedor "Macros de OpenOffice.org" y las librerías "Standard" se cargan en memoria automáticamente cuando inicia OOo. El resto de las librerías creadas por el usuario deben cargarse en memoria explícitamente como explicaré más adelante.

La forma más sencilla de crear una macro es utilizando el grabador de macros. Iniciamos el grabador desde "Herramientas > Macros > Grabar macro" el cual abre un cuadro de diálogo para detener el proceso de grabación. Una vez que terminamos de grabar la secuencia de comandos, pulsamos "Finalizar grabación" y se abre el cuadro de diálogo del organizador de macros. Bajo la librería "Standard" en el contenedor "Mis macros" creamos un nuevo módulo llamado "Modulo1" y guardamos la macro con el nombre "miNuevaMacro". Luego podemos ejecutar la macro desde "Herramientas > Macros > Ejecutar macro...".


Este método para crear macros es sencillo, aunque es limitado, ya que por ejemplo las macros grabadas no aceptan parámetros y además el grabador de macros suele tener problemas. Por lo tanto lo mejor es crear y escribir nuevas macros directamente desde el organizador de macros ("Herramientas > Macros > Organizar macros > OpenOffice.org Basic..."). Esto además nos permite escribir nuevas funciones para utilizar luego dentro de las fórmulas.


Manos a la obra...

NOTA: Para los ejemplos se utilizará OpenOffice.org versión 3.0.1.

Primero vamos a crear un nuevo documento llamado "testMacros.ods".

Abrimos el cuadro de diálogo de macros desde "Herramientas > Macros > Organizar macros > OpenOffice.org Basic..." donde se listan los contenedores de librerías y presionamos el botón "Administrar...". En la pestaña "librerías" seleccionamos el documento que creamos como contenedor y presionamos el botón "Nuevo..." para crear una nueva librería llamada "libTestMacros".


A continuación seleccionamos la nueva librería y presionamos "Editar", Calc automáticamente crea un nuevo módulo llamado "Module1" y una macro llamada "Main".


Modificamos el código para incorporar una nueva función:

REM ***** BASIC *****

Sub Main

End Sub

Function porTres(x)
  porTres = x*3
End Function

En el documento recientemente creado, utilizamos la fórmula "=porTres(A1)" para multiplicar por 3 la celda A1. Calc encuentra la macro y ejecuta la función.


Cabe destacar que los nombres de función en Calc no son case sensitive.

Luego de probar la función, guardamos el documento, lo cerramos y lo volvemos a abrir. Si la configuración de seguridad de macros se establece como "Medio" (se cambia desde "Herramientas > Opciones... > OpenOffice.org > Seguridad > Seguridad de macros..."), Calc nos advierte que el documento contiene macros y pueden ser peligrosas, presionamos "Activar Macros" para permitir la ejecución de las macros o "Desactivar Macros" si la macro es un virus.


Cuando el documento carga, Calc no encuentra la función (en la celda muestra "#NAME?"). Esto sucede porque cuando un documento se crea, automáticamente contiene una librería llamada "Standard", ésta librería es cargada en memoria automáticamente cuando se carga el documento, pero el resto no (incluyendo nuestra librería "libTestMacros"). Calc no contiene una función llamada "porTres", por lo tanto busca la función en todas las librerías de macros visibles. Como la librería "testMacros" no está visible (porque aún no ha sido cargada) Calc no encuentra la función.

Desde "Herramientas > Macros > Organizar macros > OpenOffice.org Basic..." expandimos el documento testMacros.ods y observamos que el ícono de la librería "libTestMacros" es diferente (notar grisado), esto significa que no está cargada en memoria. Si presionamos el signo '+' del ícono, vemos que éste cambia, lo que nos indica que se ha cargado en memoria.


Desafortunadamente, la celda que utiliza la función "porTres" sigue mostrando error, esto se debe que Calc no recalcula automáticamente los valores luego de cargar la librería, a menos que se modifique el contenido (podemos probar agregando +0 luego de la llamada a la función). La solución habitual es guardar las funciones en la librería "Standard", aunque si la macro es muy grande podemos crear una función stub con el nombre deseado dentro de la librería "Standard". Esta función stub carga automáticamente la librería deseada (la primera vez que es invocada) y llama a la función original. Para probar esto renombramos nuestra función "porTres" a "porTres_implementacion":

Function porTres_implementacion(x)
  porTres_implementacion = x*3
End Function

Luego creamos la función stub dentro de la librería "Standard" con el siguiente contenido:

Function porTres()
  If NOT BasicLibraries.isLibraryLoaded("libTestMacros") Then
    BasicLibraries.LoadLibrary("libTestMacros")
  End If
  porTres = porTres_implementacion()
End Function

La sentencia condicional verifica que la librería esté cargada, si no es así la carga en memoria y luego llama a la función original de la librería "libTestMacros". Una vez terminado guardamos, cerramos y reabrimos el documento, esta vez la función trabaja correctamente.


Consideraciones adicionales:

La función creada utiliza un parámetro 'x' el cual no es verificado dentro del código, es decir, antes de ser utilizado no se verifica que exista, por lo tanto, si llamamos a la función "porTres" sin parámetros el resultado será un error de ejecución de la macro. Si se desea que un parámetro sea opcional, debe utilizarse el calificador "OPTIONAL". También puede verificarse el parámetro utilizando la función "IsMissing(x)" que retorna verdadero si falta el parámetro. También debe verificarse que el valor sea numérico. El programador de macros (y de cualquier lenguaje) puede ser tan cuidadoso como desee. Cuanto más cuidadoso sea más robusto será el código, aunque tratándose de macros, cuanto mayor sea la cantidad de chequeos más lenta será la ejecución.
Los argumentos pasados a macros son siempre parámetros por valor, si se desea utilizar un parámetro por referencia, debe enviarse la referencia como un string (por ejemplo: "A1:C3") y luego se debe parsear el contenido. Se puede acceder a las celda A1 de la Hoja 1 del documento a través de una llamada:

ThisComponent.getSheets().getByIndex(0).GetCellByPosition(0,0)

Para más información sobre programación de macros de Calc referirse a:

http://www.pitonyak.org/oo.php