PDF de programación - Funciones y Macros

Hojamat.es


Macros

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.

Contenido

Funciones y macros ............................................................ 1

Define tus propias funciones ......................................... 1

Macros de apertura ........................................................ 6

Cómo sumar datos dispersos etiquetados .................... 9

Parpadeo de un dato elegido ....................................... 12

Tus funciones, disponibles en todas las hojas de cálculo

..................................................................................... 18

Funciones recursivas en las hojas de cálculo ............. 28



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



1

Hojamat.es


Macros

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 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.



2

Macros

Hojamat.es



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:



3

Hojamat.es



Macros

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.



4

Hojamat.es


Macros

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



5

Hojamat.es



Macros

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.

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()



6

Hojamat.es


Macros

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

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.



7

Hojamat.es


Macros



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.



8

Hojamat.es


Macros

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



9

Hojamat.es


Macros

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



Alfredo Gómez

8,3



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

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



10

Hojamat.es


Macros

que existe entre etiqueta y dato. En el ejemplo de los vinos

serían 0 y +1 respectivamente.



Public Function sumar_etiq(a$,filas, columnas)

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).GetCellByPo

sition(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=

S