Funciones y macros
En este apartado
se incluirán algunas técnicas referentes a la programación del
lenguaje Basic integrado en las hojas de cálculo. La gran mayoría de
los textos se referirán a OpenOffice.org Calc, aunque algunos serán de
aplicación a Microsoft Excel.
Las técnicas incluidas no
seguirán un orden estricto, sino que se irán publicando según surjan
las cuestiones que motiven su estudio.
Define tus
propias funciones
Macros de apertura
Cómo sumar datos
dispersos etiquetados
Parpadeo de un dato elegido
Define tus propias
funciones
En ocasiones desearás definir funciones
que la hoja de cálculo no ofrece. Por ejemplo, en Electricidad nos puede
convenir definir la resistencia equivalente a otras dos situadas en paralelo, o
en Geometría, la función que devuelve una hipotenusa en función de los dos
catetos. Mediante un uso sencillo de las macros puedes lograrlo.
Secuencia para definir tus propias
funciones
1) Abrir el Editor de Basic
En
OpenOffice
Sigue el menú
Herramientas > Macros > Organizar macros > OpenOffice.org Basic para
abrir el editor.
Si es la primera
función que defines, busca la carpeta Standard correspondiente al
nombre de tu modelo (si lo acabas de crear, se llamará Sin Nombre).
No señales la otra carpeta Standard, que es más general.
Una vez elegida la
carpeta, pulsa el botón Nuevo para abrir un módulo contenedor.
Se te ofrecerá el nombre de module1, module2 u otro similar. Acepta el
nombre o cámbialo según tu criterio. Al aceptar el nombre se abrirá el
editor de macros. Por defecto aparecerá la macro Main, que puedes borrar o
ignorar.
Escribe debajo el
código de tu función, según se explica en el siguiente apartado. |
En Excel
Sigue el menú
Herramientas > Macro > Editor de Visual Basic, o pulsa Alt + F11
Si es la primera
función que defines, la pantalla aparecerá en gris. Debes crear un módulo
nuevo con Insertar - Módulo, y Excel le dará el nombre de Módulo 1.
Escribe debajo el
código de tu función, según se explica en el siguiente apartado.
|
2) Escritura del código
Terminada la secuencia
anterior, comienza a escribir el código de una función-
Debes comenzar con
Function nombre de la función ( argumento )
y terminar con
End function
y entre ambas, el
código de la función
En ese código debemos
usar el nombre de la función seguida del signo igual y de su definición
Es mejor verlo con un ejemplo:
Function
cubo ( numero )
cubo=numero*numero*numero
End function
En el ejemplo, el
nombre de la función es
cubo,
y su argumento
numero (lo traduciríamos como
"Cubo de un número")
Después volvemos a escribir cubo, el signo igual, y su definición.
3) Uso de la
función
Una vez escrito el
código, cierra el Editor de Basic y usa tu función en cualquier celda. En la
imagen puedes ver una celda definida mediante la nueva función CUBO.
Con esto ya tienes
definida la función.
Con la técnica explicada, esa función sólo estará activa en la hoja de cálculo
en la que la has creado, no en otras. Al cerrar la hoja ya no podrás usarla.
Función con
varios argumentos
Una función puede
actuar sobre varios argumentos, por ejemplo la resistencia equivalente a la que
se aludía en el primer párrafo. En ese caso, se deberán separa mediante una
coma:
Function
resisequiv(r1, r2)
resisequiv = r1 * r2 / (r1 + r2)
End Function
Cuando uses esta función
en una celda, debes sustituir la coma
por un punto y coma.,
por ejemplo resisequiv(4;6).
Estudia el ejemplo de la imagen:
Variables
auxiliares
En una definición
puedes usar las estructuras del Basic: FOR...NEXT, SELECT CASE, etc. Aquí sólo
usaremos DIM, para crear variables auxiliares. Observa este ejemplo
Function
area6(lado)
Dim perimetro, apotema
perimetro = 6 * lado
apotema = lado * Sqr(3) / 2
area6 = perimetro * apotema / 2
End Function
que devuelve el área
de un hexágono en función del lado. El nombre de la función, en este caso
area6, debe figurar en la definición, aunque uses otras variables.
Macros de apertura
En ocasiones podemos desear que se
ejecute cualquier operación al abrir una hoja de cálculo, como borrar un rango,
abrir una hoja determinada, dar un valor a una celda, etc.
Para lograrlo debes, en primer lugar, escribir o grabar una macro con las
operaciones que deseas. Una vez escrita, el procedimiento para que se ejecute al
abrir una hoja cambia mucho si trabajas en Excel o si lo haces en
OpenOffice.org.
En Excel
Basta con entrar en el
Editor de Visual Basic
(Alt - F11), buscar la macro que has escrito y cambiarle el nombre por
Auto_Open. Nada más.
Ejemplos
Sub
Auto_Open()
Sheets("Hoja3").Select
Range("E6").Select
ActiveCell.FormulaR1C1 = "22"
End Sub
|
Esta macro, al abrir el archivo,
seleccionará la Hoja3, situará el cursor en la celda E6 y escribirá en ella
un 22
|
Sub Auto_Open
Range("A1:D20").Select
Selection.ClearContents
End Sub
|
Esta otra selecciona el rango A1:D20 y
borra su contenido
|
En OpenOffice.org
Aquí el procedimiento es totalmente
distinto.
Escribes o grabas una macro y le das
el nombre que desees. Supongamos que fuera Sub inicio().
 |
Una vez construida la macro, abres el
menú Herramientas - Personalizar, eliges la pestaña
Eventos y señalas con el
ratón el evento de Abrir Documento.
Pulsas sobre el botón
Asignar macro, y la buscas (por ejemplo
inicio). Debes saber dónde está. En la imagen estaría en el documento
Sin
nombre, macros Standard, módulo
Module2. Aceptas dos veces y ya tienes una
macro de inicio.
|
en cuyo caso se
considerará un desplazamiento de +1 fila y 0 columnas.
Se desarrollan a
continuación posibles códigos para resolver la situación. Se escriben en negro
las líneas que funcionan indistintamente en Excel y OpenOffice, en verde las que
hay que usar sólo en Excel, y en rojo las que sólo se escribirán en
OpenOffice. Finalmente, lo escrito en azul y en cursiva corresponderá a
comentarios. Para editar este código se deberán seguir las instrucciones del
apartado Define tus propias funciones.
Cómo sumar datos
dispersos etiquetados
En ocasiones se usan
tablas de recogida de datos en las que existen algunos de la misma naturaleza
pero que aparecen dispersos. Por ejemplo, calificaciones correspondientes a
preguntas aisladas en una clase.
Como no se pregunta cada día a los mismos
alumnos o alumnas, sus notas aparecerán en la tablas de forma desordenada. Sin
embargo, lo usual es que vengan acompañadas de una etiqueta que recuerde a quién
pertenece la calificación. Si después se desean contar o sumar estos datos,
ninguna función de Excel u OpenOffice nos resolvería el problema de forma
satisfactoria.
Un ejemplo típico se da
cuando la organización de los datos se efectúa mediante múltiples fichas
personales, que pueden llenar toda una hoja. En la imagen se registran los
pedidos de botellas que ha efectuado un socio de un Club de Vinos. A la derecha
figuran los totales mensuales, que se habrán obtenido con las funciones
generales de Excel.
Imaginemos que existen
numerosas fichas de este tipo y que se desea sumar o contar todas las botellas
enviadas en el mes de Abril. En ese caso se deberá explorar toda la hoja, y
cuando se encuentre la etiqueta "Abril", sumar la cantidad que figure a su
derecha. Para lograrlo podemos definir en Basic dos funciones nuevas. Habrá que
tener en cuenta cuántas filas y columnas separan la etiqueta del dato. En este
ejemplo sería +1 columna (está a su derecha) y 0 filas, pero la etiqueta puede
estar escrita superiormente al dato, como en
en cuyo caso se
considerará un desplazamiento de +1 fila y 0 columnas.
Se desarrollan a
continuación posibles códigos para resolver la situación. Se escriben en negro
las líneas que funcionan indistintamente en Excel y OpenOffice, en verde las que
hay que usar sólo en Excel, y en rojo las que sólo se escribirán en
OpenOffice. Finalmente, lo escrito en azul y en cursiva corresponderá a
comentarios. Para editar este código se deberán seguir las instrucciones del
apartado Define tus propias funciones.
Sumar
los datos de todas las apariciones de una etiqueta
Public Function
sumar_etiq(a$,filas, columnas) Esta función sumaría
los datos de las apariciones de la etiqueta: mes, alumno/a, marca de coche, etc.
a$ representa a la etiqueta que estamos buscando
Los parámetros filas y columnas representan el desplazamiento que existe entre
etiqueta y dato. En el ejemplo de los vinos serían 0 y +1 respectivamente.
Dim i, j, suma, suma0
Dim
g$
suma = 0
For i = 1 To 3000 filas que abarcan los
datos. Hay que cambiar el 3000 por el número de cada ejemplo concreto
For j = 1 To 20 columnas que abarquen
los datos
g$ = ActiveWorkbook.Sheets(1).Cells(i, j).Value
Línea sólo para Excel. Lee el contenido de las
celdas para descubrir la etiqueta y recogerla en la variable g$
g$=
StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(j-1,i-1).String
Línea sólo para
OpenOffice. Similar a la anterior.
If g$ = a$ Then Comprueba si la
celda contiene la etiqueta solicitada
suma0 = ActiveWorkbook.Sheets(1).Cells(i+filas, j+columnas).Value
Lee el dato asignado a la etiqueta (para Excel)
suma0= StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(j-1`columnas,i-1+filas).String
Línea
sólo para OpenOffice. Similar a la anterior. Las filas y columnas presentan
orden inverso
suma = suma + suma0
End If
Next j
Next i
sumar_etiq = suma
End Function
Una vez definida, se
puede usar en cualquier celda. En el ejemplo de los vinos, para buscar Abril
deberíamos escribir en cualquier celda
=SUMAR_ETIQ("Abril",0,1)
El 1 representa el
desplazamiento de una columna a la derecha y el 0 que el dato se encuentra en la
misma fila.
Contar las
apariciones de una etiqueta
Se puede definir una función CONTAR_ETIQ de forma similar, con pocos
cambios:
Public Function
contar_etiq(a$,filas, columnas) Esta función
sumaría los datos de las apariciones de la etiqueta: mes, alumno/a, marca de
coche, etc. a$ representa a la etiqueta que estamos buscando
Los parámetros filas y columnas representan el desplazamiento que existe entre
etiqueta y dato. En el ejemplo de los vinos serían 0 y +1 respectivamente.
Dim i, j, conta, conta0
Dim g$
conta = 0
For i = 1 To 3000 filas que abarcan los
datos. Hay que cambiar el 3000 por el número de cada ejemplo concreto
For j = 1 To 20 columnas que abarquen
los datos
g$ = ActiveWorkbook.Sheets(1).Cells(i, j).Value
Línea sólo para Excel. Lee el contenido de las
celdas para descubrir la etiqueta y recogerla en la variable g$
g$=
StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(j-1,i-1).String
Línea sólo para
OpenOffice. Similar a la anterior.
If g$ = a$ Then Comprueba si la
celda contiene la etiqueta solicitada
suma0 = ActiveWorkbook.Sheets(1).Cells(i+filas, j+columnas).Value
Lee el dato asignado a la etiqueta (para Excel)
suma0= StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(j-1`columnas,i-1+filas).String
Línea
sólo para OpenOffice. Similar a la anterior. Las filas y columnas presentan
orden inverso
if conta0>0 then conta = conta + 1
End If
Next j
Next i
contar_etiq
= conta
End Function
Una vez definida, se
puede usar en cualquier celda. En el ejemplo de los vinos, para buscar Abril
deberíamos escribir en cualquier celda
=SUMAR_ETIQ("Abril",0,1)
El 1 representa el
desplazamiento de una columna a la derecha y el 0 que el dato se encuentra en la
misma fila.
if conta0>0 then conta = conta + 1
End If
Next j
Next i
contar_etiq
= conta
End Function
En algunas situaciones prácticas podemos tener una gran abundancia de
datos que hagan casi imposible su exploración visual. Entre ellos
pueden existir algunos cuya ubicación nos interese. Por ejemplo, un
lector me indicaba que para él sería útil que se destacaran los pagos
que vencieran en la fecha actual. Así, cada vez que abriera la hoja,
encontraría parpadeando los que hubieran llegado a su fecha.
No es fácil conseguir que parpadeen las celdas que contengan un dato
que nos interese. Por eso nos vamos a tener que basar en unos datos
previos que le indiquen a la hoja de cálculo qué tiene que buscar,
dónde y cuántos segundos ha de mantener el parpadeo. Con más calma
quizás se pudiera prescindir de alguno de ellos, pero el tema no
merece más atención.
En la imagen vemos una posible cabecera. En las celdas situadas debajo
podemos imaginarnos que existen grandes cantidades de datos, y que
entre ellos está el elegido. En este caso hemos usado una fecha
07/11/45 y deberán parpadear todas las celdas inferiores que la
contengan.
La macro que construyamos deberá leer
las celdas y asignarles una variable. Supongamos que en E6 se lee
Valor, en G6 Fila, H6 Columna y J6 Pausa. Esas son las variables que
usaremos en el código Basic.
Una vez leídas se inician también t0, que leerá el reloj interno (timer)
e indi, que llevará la cuenta de las celdas que contienen el dato
elegido. También hay que preparar memorias fil y col que nos indiquen
dónde está situado el dato.
Así, la estructura de nuestra macro podría ser:
(A) Se leen las variables
Leemos Valor, Fila, Columna y Pausa, el
reloj en t0 y se pone a cero el contador de veces que aparece el dato
(indi) y las memorias fil y col
pausa=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(9,5).value
valor=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(4,5).value
fila=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(6,5).value
columna=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(7,5).value
t0=timer
t1=t0
indi=0
for i=0 to 50:fil(i)=0:col(i)=0:next i
(B) Se recorren las filas y columnas.
Cuando se encuentre el dato elegido se
incrementa indi y se rellenan fil(indi) y col(indi) con la referencia
de la celda.
(C) Se programa una pausa temporal.
Se puede hacer con esta rutina:
t1=t0
while t1
‘Se realiza el trabajo de recorrer celdas y parpadear
t1=timer
wend
La hora del reloj está almacenada en t0. La otra variable t1 va
leyendo el timer y cuando sobrepasa la pausa se detiene. Dentro del
bucle se realiza el parpadeo.
(D) Parpadeo de las celdas elegidas
Consistirá simplemente en asignar dos colores distintos a las celdas y
separarlos mediante un pequeño intervalo de tiempo:
if indi>0 then
for i=1 to indi
StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(col(i),fil(i)).cellbackcolor=rgb(255,100,200)
next i
end if
‘colorea de rojo apagado
for i=1 to 1e4:next i
‘mantiene ocupado el ordenador por un tiempo
if indi>0 then
for i=1 to indi
StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(col(i),fil(i)).cellbackcolor=rgb(255,255,255)
next i
‘Vuelve a colorear de blanco
Como ejercicio de programación es
divertido. Seguro que alguien lo puede mejorar.
Copiamos a continuación todo el código en Basic de OpenOffice.org
Sub parpadeo
dim pausa,valor,fila,columna,celda
dim t0,t1,i,j,k,indi
dim fil(50),col(50)
pausa=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(9,5).value
valor=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(4,5).value
fila=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(6,5).value
columna=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(7,5).value
t0=timer
t1=t0
indi=0
for i=0 to 50:fil(i)=0:col(i)=0:next i
for i=8 to fila
for j=1 to columna
celda=StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(j,i).value
if celda=valor then
indi=indi+1
fil(indi)=i:col(indi)=j
end if
next j
next i
while t1
if indi>0 then
for i=1 to indi
StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(col(i),fil(i)).cellbackcolor=rgb(255,100,200)
next i
end if
for i=1 to 1e4:next i
if indi>0 then
for i=1 to indi
StarDesktop.CurrentComponent.sheets(0).GetCellByPosition(col(i),fil(i)).cellbackcolor=rgb(255,255,255)
next i
end if
t1=timer
wend
End Sub
