programación de macros y funciones en excel

5/11/2018 Programación De Macros Y Funciones En Excel - slidepdf.com

http://slidepdf.com/reader/full/programacion-de-macros-y-funciones-en-excel 1/7

Excel incluye un intérprete de Visual Basic con el que se puede aumentar

el poder del programa definiendo nuevos comandos y funciones que pro- porcionan una enorme potencia a las macros. Solamente se requieren

mínimos conocimientos de lógica y programación.

PCW 250



na macro puede definirse como un “súpercomando”, que permite ejecutar varias operacionessucesivas con una única orden. Por ejemplo, supon-

gamos una hoja de cálculo en la que tengamos queordenar una lista, luego copiarla ordenada en otrahoja y, finalmente, imprimir esta segunda hoja. Setrata de tres operaciones: ordenar, copiar, imprimir.Una macro nos permitiría ejecutar las tres operacio-nes de una sola vez: tomando una sola opción delmenú, usando una única combinación de teclas ohaciendo clic sobre un botón adecuado. Para obte-ner una macro así, tenemos que escribir un pro-grama, una lista de instrucciones que, traducidas aun lenguaje que Excel pueda entender, equivalgan alas operaciones anteriores. Así, por ejemplo, la

acción de imprimir la hoja actual se representa enuna macro mediante la instrucción ActiveWindow.SelectedS-

heets.PrintOut Copies:=1

Esto podría traducirse como “imprimir una copiade la hoja seleccionada en la ventana activa”. ¿Enqué idioma? En VBA (Visual Basic para Aplicacio-nes), una variante del lenguaje Visual Basic que seusa en la programación de Excel. Con ellas podemosampliar las opciones del programa, dotándolo denuevos comandos y también nuevas funciones.Por supuesto que para escribir una macro hay que

conocer las reglas del lenguaje VBA: cuál es la ins-

trucción correspondiente a cada operación de Excel.Pero estas reglas son bastante sencillas y fáciles de

Excel, Visual Basicy Master Mind

U

Programación de macros y funciones en Excel

Claudio H. Sánchez [email protected]

TUTOR

PCW 251



aprender. Por si esto no alcan-zara, Excel incluye un modo deprogramación (el grabador demacros) que simplifica las cosasaún más (ver el recuadro El gra-

bador como asistente ).Este artículo explica cómo usar

la programación de Excel para

crear una hoja de cálculo contrala cual jugar al Master Mind.Crear un juego de ordenador enun programa como Excel es depor sí un desafío interesante.Pero, además, el desarrollo deesta hoja de cálculo constituyeuna forma muy atractiva decomenzar a familiarizarse con lasherramientas de programación.

Master Mind

¿Conoce usted este juego?, con-siste en adivinar un número pen-sado por el adversario. El nosguiará hacia ese objetivo dicién-donos cuánto nos aproximamosal número que él ha pensado.Por ejemplo, supongamos que el

piensa en el número 4587 (cosaque, por supuesto, no nos dice).Nosotros arriesgamos: “¿Es el

7482?” Entonces él nos dirá:“Dos regular y uno bien” . Con estonos quiere decir que dos dígitos

de nuestro número se encuentrantambién en el que él ha pensado,

pero en distinta posición (el 7 y el4). Y que otro (el 8) se encuentraen la misma posición. Pero no nosdice cuáles son esos dígitos. Sola-mente evalúa nuestro intento entérminos de “bien” y “regular”.Con esta información, y usando lalógica, podemos llegar a determi-nar cuál es el número.Una hoja de cálculo capaz de

jugar al Master Mind debe hacer

dos cosas: generar el número decuatro cifras que tendremos que

adivinar y evaluar nuestros inten-tos de adivinarlo en términos de“bien” y “regular”, de acuerdo conlas reglas del juego. Veamosentonces cada problema.

Generando el número

Excel incluye un par de funcionespara generar números aleatoriosde determinadas características.Pero ninguna de ellas nos ase-

gura que el número generadotenga todos sus dígitos diferen-tes, condición necesaria en elMaster Mind. Entonces recurri-mos a la hoja de cálculo de laFigura 1.El rango E3:E12 contiene copias

de la función =ALEATORIO(). Estafunción devuelve un númeroaleatorio cualquiera compren-dido entre 0 y 1. El númerodevuelto cambia cada vez que se

recalcula la hoja, tanto porquehacemos algún cambio en ella,como porque apretamos la teclaF9.El rango F3:F12 contiene simple-

mente los números del cero alnueve. Si ordenamos esta tablasegún el contenido de lacolumna E, el resultado será,realmente, desordenar los dígi-tos de la columna F. Leyendo dearriba hacia abajo las cuatro

primeras celdas de estacolumna, obtendremos el

TUTOR

Cómo se ejecuta una macroEn el texto se indica la forma más incómoda de ejecutar una macro: a través de lasopciones Herramientas∑Macro∑Macros. Hay muchas otras formas, pero las más simples y ele-gantes son las siguientes:

Asignando una combinación de teclas a la macro Tomamos las opciones Herramientas∑Macro∑Macros. Aparecerá un cuadro con la lista demacros disponibles, en la cual seleccionamos (con un clic) la macro a la cual le que-ramos asignar la combinación de teclas. Hacemos un clic en el botón Opciones y apare-cerá el cuadro de la Figura 6. Donde dice Tecla de método abreviado escribimos una M

mayúscula y hacemos un clic en Aceptar. Cerramos el cuadro donde aparece la macro.De acuerdo con la combinación de teclas usada, ahora podemos ejecutar la macro(generando el número a adivinar) mediante la combinación Control+Shift+M. Es conve-niente elegir combinaciones de este tipo porque las combinaciones más sencillas(de la forma Control+Letra) suelen tener alguna otra función asignada por el programa.

Insertando un botón en la hoja de cálculo Los que son más aficionados al ratón que al teclado, preferirán ejecutar la macrohaciendo clic en un botón insertado a tal efecto. Los botones de macros se obtienenen la barra de herramientas Formularios (Figura 7).Hacemos un clic en el botón Botón, dentro de la barra de herramientas Formularios y defi-nimos, arrastrando el ratón, un rectángulo del tamaño que tendrá el botón. Al soltarel botón del ratón aparecerá el cuadro de la Figura 8, con la lista de macros defini-das en la hoja de cálculo. Hacemos un clic en la macro a la cual le queremos asignarel botón y hacemos un clic en Aceptar. De ahora en adelante, la macro puede ejecu-

tarse con un clic en el botón creado.

Figura 1: la

hoja de cál-

culo con los

elementos

necesarios

para gene-

rar el núme-

ro de cuatro

dígitos dife-

rentes que

habremos

de adivinar.

Figura 2: la

macro que

genera el

número a adi-

vinar.

PCW 252



número de cuatro dígitos dife-

rentes que estamos necesitando.Para construir este númeroescribimos en la celda E1 la fór-mula =F3*1000+F4*100+F5*10+F6 quereúne los cuatro primeros dígi-tos en uno solo número. Esteserá el número a descubrir.

Automatizando el proceso

En resumen, para obtener elnúmero a adivinar, tenemos quehacer lo siguiente:- Borrar el rango A2:A11, para eli-

minar los números usados enuna partida anterior.- Colocar el cursor en la celda E3.- Ordenar el rango E3:F12.- Asegurarnos que, al ordenar,

se obtiene en E1 un número cuatrocifras, es decir, que no comienzacon cero.- Colocar el cursor en la celda A2

para comenzar la tarea de deduc-ción.Son demasiados pasos. Y, aun-

que se tarde más en decirlos queen hacerlos, pueden simplificarsecon una macro. Ésta es la queaparece en la Figura 2. Lo quevemos en la figura es el “código dela macro”. Es la traducción allenguaje VBA de la lista de cincooperaciones que conducen a laobtención del número. Paraentender las instrucciones queaparecen en la macro de la Figura2 no se necesita un gran conoci-

miento de VBA. Apenas si hacefalta saber un poco de inglés.

Entendiendo el código

Encabeza la macro la instrucciónSub Ordenar(). Toda macro debecomenzar con la palabra clave Sub,seguida del nombre elegido parala macro. En principio, este nom-bre puede ser cualquiera. Luegosiguen dos instrucciones de lamisma forma: Objeto.Acción. Es decir,se menciona un “objeto” (que

puede ser un rango, un gráfico,una línea, etc.) y una “acción” quese efectuará sobre ese objeto.La primera instrucción es

Range(“A2:A11”).ClearContents. La primeraparte (el objeto) indica que se va ahacer una operación sobre elrango A2:A11. La segunda parteindica la acción que se hace sobreese objeto: borrar el contenido. Esdecir que esta instrucción borra elcontenido del rango donde se

encontrarían los números escritosen una partida anterior.

En la siguiente instrucción(Range(“E3”).Select) el objeto es lacelda E3, y lo que se hace es selec-cionarla. En otras palabras, lainstrucción ubica el cursor en lacelda E3. La estructuraObjeto.Acción es típica del len-guaje Visual Basic en general y

de VBA en particular.Luego sigue un bucle: un con-

junto de instrucciones que pue-den ejecutarse una o más veces.Hay dos tipos de ciclos: aquellosen que las instrucciones se eje-cutan un número determinadode veces y aquellos en que seejecutan según se cumpla o nocierta condición. El que apareceen el listado de la Figura 2 perte-

nece a este segundo

tipo.Este ciclo comienza conla palabra clave Do y secierra con Loop. En estaúltima instrucción seindica que el ciclo serepetirá hasta (eninglés, until ) que el valorde la celda F3 sea dis-tinta de cero. Aquí apa-rece una estructurasimilar a Objeto.Acción:

Objeto.Propiedad. El objeto es

la celda F3 y la propiedades su valor.

La instrucción comprendidaentre el Do y el Loop es Selection.Sort

Key1:=Range(“E3”), Order1:=xlAscending.Aquí aparece de nuevo la estruc-tura objeto.acción: el objeto es laselección actual y la acción es suordenamiento (en inglés, sort ).La instrucción indica tambiénque la clave de ordenamiento(Key1) es la celda E3 y que el orden

es ascendente. Debido a la con-dición expresada en la instruc-ción Loop, el ordenamiento sehará tantas veces como seanecesario hasta que el valor dela celda F3 sea distinto de cero, loque asegura generar un númerode cuatro dígitos. Una vez gene-rado el número a adivinar, lainstrucción Range(“A2”).Select ubicael cursor en la celda A2 paracomenzar el juego. Finalmente

aparece la instrucciónEnd Sub

queseñala el fin de la macro.

TUTOR

Figura 3:

la función

BIEN.

Figura 4:

la función

REGULAR.

Figura 5: la hoja de cálculo terminada. En la celda E1

está (oculto) el número a adivinar. A partir de la celda

A2 escribimos nuestros pronósticos y en las columnas B

y C la hoja de cálculo nos mostrará la calificación obte-

nida por cada intento.

PCW 254



Cómo se escribe la macro

El texto de la Figura 2 debe escri-birse en una hoja de módulosdentro del editor de Visual Basic.Elejimos la opcionesHerramientas∑Macro∑Editor de Visual Basic yaparecerá el editor (que es un pro-grama aparte). Entonces seleccio-namos Insertar∑Módulo, dentro del

menú del editor. El último pasoabre una ventana de texto dondepodemos escribir el código de lamacro tal como aparece en la Figura

2.Antes de seguir con la progra-

mación del juego es el momentode probar si la macro anteriorfunciona correctamente. Desde lahoja de cálculo Excel (no desde eleditor de VBA) seleccionamosHerramientas∑Macro∑Macros. Aparecerá uncuadro con la lista de macros dis-

ponibles. Seleccionamos la macroOrdenar y hacemos un clic en elbotón Ejecutar. En ese momento lacelda E1 deberá mostrar unnúmero de cuatro cifras distintas,apto para ser adivinado por el

jugador.La ejecución de la macro puede

simplificarse de varias formas,para ello vea el recuadro Cómo se

ejecuta una macro . Tal como fue contada hasta

aquí, la hoja de cálculo presentaun problema: el número a adivi-nar queda a la vista. Basta mirarla celda E1 o los dígitos de lacolumna F para saber qué númerogeneró el programa. La formamás simple de resolver esto esocultar las columnas E y F, selec-cionándolas y pinchando enFormato∑Columna∑Ocultar.

Calculando las puntuaciones

Habiendo obtenido (y ocultado) elnúmero a adivinar, debemos pro-

poner un número (que llama-remos el “pronóstico”) y obte-ner su puntuación respectodel número pensado por lahoja de cálculo (el “objetivo”)de acuerdo con las reglas delMaster Mind.Lo ideal sería disponer de dos

funciones, BIEN() y REGULAR(),capaces de hacer la evaluación

en forma inmediata. Estas funcio-nes no existen pero pueden tam-bién ser creadas en VBA, tal comohicimos con la macro.

La función BIEN

Un dígito se califica como “bien”cuando ocupa la misma posiciónen el número objetivo y en el pro-

nóstico . La función BIEN deberá,entonces, ser capaz de:1- Descomponer el objetivo y el

pronóstico en cada uno de sus

dígitos.

2- Comparar cada dígito del pro-nóstico con el correspondientedígito del objetivo.3- Si la comparación es satisfac-

toria, contar ese dígito como“bien”.Cada una de estas operaciones se

hace con funciones y herramientas

específicas del lenguaje VBA.Para descomponer un número

en sus dígitos dis-ponemos de la fun-ción Mid. Su sintaxises: Mid(número, comienzo,

cantidad), donde: número

es el número delcual queremosextraer sus dígitos,comienzo es la posicióna partir de la cual

hacemos la extracción ycantidad

esla cantidad de dígitos que quere-mos extraer. En nuestro caso, undígito por vez.Por ejemplo, queremos saber si

el tercer dígito del pronósticocoincide con el tercer dígito delobjetivo. Si coincide, una variableque lleva la cuenta del puntua-ción incrementa su valor en unaunidad. Las instrucciones de VBAque hacen todo esto son:If Mid(pronóstico, 3, 1) = Mid(objetivo, 3, 1) Then

BIEN = BIEN + 1End If

TUTOR

Figura 6: en este cuadro podemos asignar una

combinación de teclas para ejecutar la macro.

Figura 7: esta es la barra de herramientas Formularios. El botón

que aparece en la figura permite definir un botón al cual

asignarle luego una macro.

El grabador como asistenteEn la macro que genera el número a adivinar aparecen instrucciones en VBA corres-pondientes a diversas operaciones de Excel: seleccionar una celda, borrar unrango, ordenar una lista. ¿Cómo podemos saber cuáles son esas instrucciones sindisponer de un libro de VBA?Para eso podemos recurrir al grabador de macros, que traduce a VBA las operacio-nes que hacemos con Excel. Su manejo es muy sencillo. Por ejemplo, queremossaber cuál es la instrucción en VBA correspondiente a la acción de seleccionar unrango y borrarlo. Para ello seleccionamos Herramientas∑Macro∑Grabar nueva macro, lo que haceaparecer un cuadro como el de la Figura 9. Hacemos un clic en Aceptar y aparece labarra de botones de la Figura 10. Todo lo que hagamos de ahora en adelante será

registrado por el grabador y traducido a VBA. El primero de estos botones servirápara apagar el grabador cuando hayamos completado la operación.Realizamos la operación cuya traducción a VBA queremos conocer, en este caso,seleccionamos un rango y borramos su contenido apretando la tecla Suprimir. Hace-mos un clic en el botón de la Figura 10 para apagar el grabador y ahora podemos ver la macro obtenida abriendo el editor de VBA. Para ello vamos aHerramientas∑Macro∑Macros. Aparece un cuadro parecido al de la Figura 8 con la lista demacros disponibles. En la lista deberá aparecer la macro que acabamos de crear.La seleccionamos y hacemos un clic en Modificar.Se abrirá el editor de VBA (Figura 11) mostrando el código correspondiente a la ope-ración realizada mientras el grabador estaba funcionando. Asi nos enteramos de laforma que tiene la instrucción.El grabador funciona entonces como un asistente que nos dice cuál es la instrucción VBA correspondiente a cualquier comando de Excel. Sin embargo, si queremosdepurar nuestras macros y hacerlas más eficientes, debemos profundizar el conoci-

miento del lenguaje. Abundan los libros sobre este tema.

PCW 255



Esto se lee “si el dígito queocupa la tercera posición en elpronóstico es igual al dígito queocupa la tercera posición en el

objetivo, entonces la variableBIEN

aumenta en una unidad”.La comparación debe hacerse

con cada uno de los cuatro dígi-tos que componen los números yno solamente con el tercero.Para eso no hace falta escribirlas instrucciones anteriores cua-tro veces. Podemos recurrir aotro tipo de ciclos:

For i = 1 To 4

If Mid(pronóstico, i, 1) = Mid(objetivo, i, 1) Then

BIEN = BIEN + 1End If

Next

La instrucción For señala elcomienzo del ciclo e indica quetodo lo que sigue hasta la apari-ción del Next (fin del ciclo) se eje-cutará cuatro veces. La variablei controla el avance del ciclo y seusa, al mismo tiempo, paradesignar la posición del dígito aanalizar. Es decir, que el ciclo

comenzará comparando los pri-meros dígitos, luego compararálos segundos y así sucesiva-mente. Cada vez que se detectaque los dígitos comparados soniguales, la variable donde seguarda el puntuación incre-menta su valor en una unidad.En forma similar a lo que ocu-

rre con las macros, el código delas funciones comienza con lainstrucción especial Function,

seguido del nombre de la fun-ción. Este nombre es el de la

variable donde se guarda elvalor que la funcióndevuelve. La última instruc-ción, que señala el fin de lafunción es End Function.

En la Figura 3 aparece el códigocompleto de la función, que seescribe en una hoja de módulostal como se explicó en el caso de

la macro.

La función REGULAR

Un dígito se califica como “regu-lar” cuando está presente tantoen el número objetivo como ennuestro pronóstico, pero en dis-

tinta posición. Es decir que, eneste caso, cada dígito del pro-

nóstico debe ser com-parado con todos losdemás dígitos delobjetivo. Esto se lograusando dos ciclos For...

Next:For i = 1 To 4

For j = 1 To 4If Mid(pronóstico, i, 1) =

Mid(objetivo, j, 1) ThenREGULAR = REGULAR + 1End If

Next

Next

Este juego de instruccionesrealiza dieciséis comparaciones.Comienza con la variable i iguala 1. Es decir, tomando el primerdígito del pronóstico. Sin cam-biar este valor, el segundo ciclose ejecuta cuatro veces compa-rando este primer dígito con

cada uno de los cuatro del obje-tivo.Luego el valor de i se incre-menta a 2. Es decir que setoma el segundo dígito delpronóstico y, otra vez, elsegundo ciclo recorre loscuatro dígitos del objetivo.Como antes, cada vez que lacomparación detecta dosdígitos iguales, se incre-menta el valor del puntua-ción.

Pero hay algo mal: las ins-trucciones anteriores califi-can un dígito como “regular”cuando aparece en ambosnúmeros, sin verificar que

ocupen distintasposiciones. Pararestringir la com-paración a posi-ciones diferentesse agrega unasegunda condi-

ción en la ins-trucción If: If

Mid(pronóstico, i, 1) =

Mid(objetivo, j, 1) And

i<>j Then

El signo <> repre-senta la desi-

gualdad. Esta modificación ase-g u r a q ue s o l a m en t e s ecomparan dígitos que ocupanposi ci ones d i f e r e n t e s . Lafunción terminada aparece

en laFigura 4

.

TUTOR

Figura 8: en este cuadro debemos indicar la macro

a la cual le asignaremos el botón.

Figura 9: este cuadro aparece al comenzar la

creación de una macro usando el grabador. Macro1

es el nombre de la macro.

Figura 10: esta barra de botones indica que está

encendido el grabador de macros. El botón seña-

lado servirá para apagarlo y dar por terminada la

grabación.

Figura 11: el editor de VBA y la macro correspondiente a la

selección y borrado de un rango.

PCW 256



Usando las funciones

Habiendo creado las funciones en el editor deVBA, ya las podemos usar. Considerando lahoja de cálculo de la Figura 1, supondremos quenuestros pronósticos serán escritos en lacolumna A, debajo del número objetivo (quepermanecerá oculto al oscurecer su celda). Enlas columnas B y C debemos obtener las respec-

tivas puntuaciones. En la celda B2 escribimos=BIEN(E$1;A2), en la celda C2 escribimos=REGULAR(E$1;A2). Estas dos fórmulas comparan elobjetivo que está en lacelda E1 con los pronósti-cos que iremos escri-biendo a partir de la celdaA2. Ambas deben exten-derse hacia abajo parapoder conservar las pun-tuaciones de todos nues-tros intentos. Diez inten-

tos es una cantidad razo-nable.La hoja de cálculo termi-

nada aparece en la Figura5, con algunas opcionesde formato para hacerlamás elegante. Del botónque se ve a la derechahablamos en el apartadoCómo se ejecuta una

macro .

Comienza el juego

Está todo listo paracomenzar a jugar. Paraello ejecutamos la macro,escribimos nuestro pro-nóstico en A2. Las celdasB2 y C2 mostrarán la pun-tuación de ese pronóstico.Con la información sumi-nistrada por estas pun-tuaciones, escribimos otronúmero en A3. Cuando la función escrita en lacolumna B muestre el valor 4, es que hemos

acertado. Los cuatro dígitos de nuestro últimointento están “bien”.Como dijimos antes, deberíamos adivinar el

número generado por la computadora en nomás de diez intentos.Puede encontrar la hoja de cálculo terminada

y funcionando (con ligeras modificacionesestéticas) en el área de descarga de listados denuestra páginas web: www.idg.es/pcworld

Cl

au

di

o Hor

a

cio Sá

n

c

h

ez es ingeniero industrial

y profesor de física y de informática en la Uni-

versidad de Flores (Argentina). Ha escrito varios libros sobre Excel.

PCW

TUTOR

Excel incluye

un par de

funciones para

generar

números

aleatorios dedeterminadas

características.

Pero ninguna de

ellas nos

asegura que el

número

generado tenga

todos susdígitos

diferentes,

condición

necesaria en el

Master Mind

programación de macros y funciones en excel

Documents