Lección 11

         En ocasiones, tenemos que realizar acciones repetitivas y rutinarias una y otra vez. En vez de hacerlas manualmente, podemos crear una macro que trabaje por nosotros. Las macros son funciones que ejecutan instrucciones automáticamente y que nos permiten ahorrar tiempo y trabajo.

         Los pasos para crear una macro son:

1.      Acceder a Herramientas – Macro – Grabar macro

2.      Pulsar las teclas o tareas una tras otra teniendo cuidado en no equivocarnos.

3.      Detener la grabación de la macro.

4.      Depurar posibles errores o modificar la macro.

Las macros también pueden ejecutarse pulsando una combinación de teclas específica, por lo que ni siquiera debemos acceder a un menú para invocar a la macro, o bien asignársela a un botón.

Cuando creamos una macro, en realidad Excel está creando un pequeño programa utilizando el lenguaje común en aplicaciones Office: el Visual Basic.

1.     
Accede a Herramientas – Macro – Grabar nueva macro. Te aparecerá un menú:

2.      Acepta el nombre propuesto (Macro1) y acepta el cuadro de diálogo.

A continuación aparecerá un pequeño botón desde el que podrás detener la grabación de la macro.

A partir de estos momentos, todo lo que hagas (escribir, borrar, cambiar algo...) se irá grabando. Debemos tener cuidado, porque cualquier fallo también se grabaría.

3.      Pulsa Control + Inicio

4.      Escribe: Días transcurridos y pulsa Intro.

5.      En la celda A2 escribe: Fecha actual y pulsa Intro.

6.      En la celda A3 escribe: Fecha pasada y pulsa Intro.

7.      En la celda A4 escribe: Total días y pulsa Intro.

8.      Selecciona con un click la cabecera de la columna A (el nombre de la columna) de forma que se seleccione toda la columna.

9.      Accede a Formato – Columna – Autoajustar a la selección

10.  Pulsa click en la celda B2 y escribe: =HOY(). Pulsa Intro.

11.  Escribe: 29/09/98 y pulsa Intro.

12.  Accede a Formato – Celda elige el formato Número y acepta.

13.  Sitúa el cursor en la celda A1.

14.  Pulsa la combinación de teclas Control + * (se seleccionarán todo el rango no-vacío).

15.  Accede a Formato – Autoformato – Multicolor 2 y acepta.

16.  Finaliza la grabación desde el botón Detener grabación o bien desde el menú Herramientas – Macro – Detener grabación.

Perfecto. Ahora vamos a ver si la macro funciona:

1.      Colócate en la Hoja2

2.      Accede a Herramientas – Macro – Macros.

3.      Elige tu macro y pulsa el botón Ejecutar.

4.      Observa su comportamiento.

La macro ha ido realizando paso a paso todas las acciones que hemos preparado.

La creación de macros no se limita a pequeñas operaciones rutinarias como acabamos de ver en el último ejemplo; podemos crear macros más complejas que resuelvan situaciones complicadas de formateo y cálculo de celdas que nos ahorrarán mucho trabajo.

Excel crea sus macros utilizando el lenguaje común de programación de los componente de Office: el Visual Basic, por lo que si tenemos idea de dicho lenguaje, podremos modificar el código de la macro manualmente.

Pero vamos a crear una macro más completa. Supongamos que queremos conseguir un informe mensual de una tabla de datos de ventas, añadiendo columnas, clasificándolas, imprimirlas, clasificarlas con otros criterios, etc. Tenderás que abrir el fichero que se adjunta en esta lección y trabajar con él.

1.      Abre el fichero Pedidos.XLS que se adjunta con la presente lección.

2.      Observa sus dos hojas: Precios y Pedidos.

Imagina que se trata de una empresa textil que tiene que elaborar una macro que realice tareas de fin de mes. La hoja nos muestra una clasificación por estados, canales (minorista y mayorista), categorías, precios y cantidad. La macro automatizará el trabajo de forma que cada mes podremos recoger un informe de los pedidos de mes anterior extrayéndolo del sistema de proceso de pedidos.

         El secreto de una macro larga es dividirla en varias macros pequeñas y luego unirlas. Si intentamos crear toda una gran macro seguida, habrá que realizar cuatrocientos pasos, cruzar los dedos, desearse lo mejor, y que no hayan demasiados fallos.

         La hoja que hemos recuperado nos muestra las unidades y totales netos. Los pedidos del mes anterior, Marzo de 1994, se encuentran en la hoja 2. Como vamos a crear una macro, y estamos sometidos al riesgo de fallos, vamos a crear una copia de nuestra hoja. De todas formas, aunque la macro funcione perfectamente, tendremos una copia para practicar con ella.

1.     
Crea una copia de la hoja Pedidos (arrastrándola hacia la derecha con la tecla de control pulsada).

         Cuando el sistema de pedidos produce un informe, introduce una etiqueta en una columna la primera vez que aparece la etiqueta. Vamos a crear la macro. Te pedimos que prestes atención a las acciones que vamos creando y su resultado en pantalla.

1.      Crea una nueva macro con el nombre: RellenarEtiquetas y acepta.

Pasos de la macro:

1.      Pulsa Ctrl + Inicio para situar el cursor en la primera celda.

2.      Pulsa Ctrl + * para seleccionar el rango completo.

3.      Pulsa F5 (Ir a...)

4.      Pulsa el botón Especial de ese mismo cuadro de diálogo.

5.      Activa la casilla Celdas en blanco y acepta.

6.      Escribe: =C2 y pulsa Ctrl + Intro.

7.      Pulsa Ctrl + Inicio

8.      Pulsa Ctrl + *.

9.      Elige Edición - Copiar (o el botón Copiar).

10.  Elige Edición -  Pegado especial....

11.  Activa Valores y acepta.

12.  Finaliza la grabación.

Hemos utilizado combinaciones de teclas y métodos rápidos de seleccionar y rellenar celdas para agilizar el trabajo.

Observa que hemos finalizado la macro sin desactivar la última selección de celdas. Con una simple pulsación de la tecla Esc y después mover el cursor, habría bastado, pero lo hemos hecho así para que puedas ver cómo se modifica una macro.

1.      Elimina la hoja copia de Pedidos.

2.      Vuelve a crear otra copia de Pedidos.

3.      Ejecuta la macro en la hoja copia.

Si todos los pasos se han efectuado correctamente, la macro debería funcionar sin problemas.

4.      Vuelve a borrar y crear otra copia de Pedidos.

         Hemos dicho que Excel trabaja sus macros básicamente en el lenguaje común Visual Basic. Veamos qué ha sucedido al crear la macro a base de pulsaciones de teclas y teclear texto:

1.      Accede a Herramientas - Macros - Editor de Visual Basic

Te aparecerá una pantalla especial dividida en tres partes:

·         Pantalla de proyecto: es donde se almacenan los nombres de las hojas y las macros que hay creadas.

·         Pantalla de módulos: un módulo es una rutina escrita en Visual Basic que se almacena en forma de archivo y que puede ser utilizada en cualquier programa.

·         Pantalla de código: aquí es donde podemos escribir y modificar el código de la macro actual.

1.     
En la pantalla de Proyecto, pulsa doble click en Módulos y luego en Módulo 1. Aparecerá el código Visual Basic en la parte derecha.

Si ya has programado con Visual Basic verás que el sistema para Excel es prácticamente idéntico. No tendrás demasiados problemas en comprender las sentencias de programación.

         Aunque este curso no trata de programación, puede servirte como iniciación a la misma aunque no hayas hecho nunca. De esta forma te pones en contacto con Visual Basic, uno de los más extendidos lenguajes mundialmente.

         Normalmente, una rutina en lenguaje Visual Basic de macros, se lee de derecha a izquierda. Fíjate que comienza con la sentencia Sub RellenarEtiquetas(), esto es, la orden Sub y el nombre de la macro. Fíjate también que la rutina finaliza con la orden End Sub. Todas las órdenes contenidas entre ellas son las secuencias de pulsaciones que has ido ejecutando en la creación de la macro.

         Recuerda que la primera pulsación fue ir a la primera celda con la combinación Ctrl + Inicio. Observa la traducción en Visual Basic:

Range("A1").Select

Selection.CurrentRegion.Select

         Selecciona la región actual de la selección original.

Selection.SpecialCells(xlCellTypeBlanks).Select

         Selecciona las celdas en blanco de la selección actual.

Selection.FormulaR1C1 = "=R[-1]C"

         Significa: "La fórmula para todo lo seleccionado es...". La fórmula =L(-1) significa: "leer el valor de la celda que se encuentra justo encima de mí".

         Cuando utilizamos Ctrl + Intro para rellenar celdas, la macro tendrá la palabra Selection delante de la palabra Formula. Cuando se introduce Intro para rellenar una celda, la macro tendrá la palabra ActiveCell delante de la palabra Formula.

         El resto de sentencias de la macro, convierten las fórmulas en valores. Observa el resto de sentencias y relaciónalos con las pulsaciones que has ido realizando en la creación de la macro. Recuerda leerlas de derecha a izquierda.

1.      Cierra la ventana del editor de Visual Basic.

2.      Accede a Herramientas - Macro - Macros.

3.      Selecciona la macro y pulsa en el botón Opciones.

4.      Asigna la letra r como combinación de teclas de la macro y acepta.

5.      Cierra el último cuadro de diálogo.

6.      Accede a Herramientas - Macro - Editor de Visual Basic

7.      Añade al final del código y antes del fin de la rutina End Sub las siguientes líneas:

Application.CutCopyMode = False

Range("A1").Select

8.      Cierra y ejecuta de nuevo la macro.

Observa que las últimas líneas hacen que el modo de Copiar se cancele y el cursor vuelva a la celda A1. Es lo mismo que si hubiésemos pulsado la tecla Esc y Ctrl + Inicio cuando grabábamos la macro.

         La ejecución de una macro es muy rápida. A veces nos puede interesar ver paso a paso lo que hace una macro, sobre todo cuando hay algún fallo, para localizarlo y corregirlo.

1.      Elimina y vuelve a hacer otra copia de la macro.

2.      Accede a Herramientas - Macro - Macros

3.      Selecciona la macro y pulsa en el botón Paso a paso.

Observa cómo la macro se ha detenido en la primera línea y la ha marcado en color amarillo.

4.      Ve pulsando la tecla F8 y observa cómo la macro se va deteniendo en las diferentes líneas de la rutina.

5.      Finalmente, cierra la ventana de código.

         Nuestro informe no incluye la fecha en cada fila, por lo que vamos a añadir una nueva columna para añadir el mes de cada registro.

1.      Crea una nueva macro con el nombre: AñadirFecha y acepta.

Pasos de la macro:

1.      Sitúate en la celda A1 (de la hoja con la macro anterior ejecutada)

2.      Accede a Insertar - Columnas.

3.      Escribe: Fecha y pulsa Intro.

4.      Vuelve a la celda y conviértela en formato negrita.

5.      Selecciona el rango A2:A179

6.      Escribe: Mar-98 y pulsa Ctrl + Intro.

7.      Pulsa Ctrl + Inicio y finaliza la grabación.

8.      Borra la hoja.

9.      De la hoja original, haz una copia.

10.  Ejecuta las dos macros en el orden que las hemos creado.

Evidentemente, cada vez que ejecutemos la macro, Excel rellenará las celdas recién creadas con la palabra mar-98. Una solución sería cambiar la macro cada mes con la nueva fecha, pero no parece la solución más adecuada. Vamos a hacer que el programa nos pida el mes y posteriormente lo rellene él.

1.      Accede al código Visual Basic de la última macro creada.

2.      Selecciona el texto mar-98 (comillas incluidas)

 

3.      Pulsa la tecla Supr para borrarlo.

4.     Escribe en su lugar: InputBox("Introduce la fecha en formato MM-AA: ")

5.      Sal del cuadro de diálogo y ejecuta la macro de nuevo en alguna hoja copia de la original, o bien borra la columna A de la última hoja y ejecuta la macro.

6.      Cuando te pida la fecha, escribe por ejemplo: 4-11

La orden InputBox es una función de Visual Basic que visualiza un cuadro con un mensaje personalizado para la entrada de datos cuando se está ejecutando la macro.

         Observa que en la hoja tenemos tres precios por diseño: Bajo, Medio y Alto. Si queremos comparar el valor de los pedidos sin descuento con el de los mismos con descuento, precisaremos añadir en cada fila la lista de precios. Una vez hayamos observado la lista de precios de cada fila, podremos calcular el importe total de los pedidos, multiplicando las unidades por los precios.

         Finalmente, convertiremos las fórmulas en valores como preparación para añadir los pedidos al archivo histórico permanente.

1.      Crea una nueva macro llamada: AñadirColumnas.

2.      Pulsa F5, ve a la celda H1 utilizando este cuadro y escribe en esa celda: Tarifa.

3.      Ve a la celda I1 y escribe: Bruto.

4.      Ve a la celda H2 y escribe la siguiente fórmula (puedes ayudarte con el teclado o escribirla "a saco"):

=BUSCARV(E2;Precios!$A$2:$C$4;SI('Pedidos'!C2="Minorista";2;3))

5.      Ve a la celda I2 e introduce: =F2*H2. Pulsa Intro.

6.      Selecciona el rango de celdas H2:I179

7.      Accede a Edición - Rellenar - Hacia abajo

8.      Pulsa Ctrl + Inicio

9.      Finaliza la grabación de la macro.

En la celda H2 aparece el valor 4.5. Esta fórmula busca el precio Medio (E2) de la primera columna del rango A2:C4 de la hoja Precios. A continuación devuelve el valor de la columna número 2 de la lista por ser Minorista la celda C2.  El precio para la venta Minorista de un diseño con un precio Medio es de 4.50 dólares.

Para comprobar su funcionamiento:

10.  Borra las dos columnas H e I y ejecuta la macro.

Las fórmulas de BUSCARV son aún fórmulas. En nuestro archivo histórico de pedidos, no debemos añadir fórmulas, sino resultados. Vamos a transformar las fórmulas en valores.

11.  Crea una nueva macro llamada: ConvertirValores.

12.  Selecciona el rango H2:I179.

13.  Cópialo al portapapeles.

14.  Ve a Edición - Pegado especial.

15.  Selecciona Valores y acepta.

16.  Finaliza la grabación de la macro.

         Finalmente, queremos añadir los nuevos pedidos del mes al archivo histórico acumulativo de pedidos. Necesitamos asegurarnos de que las columnas de los nuevos pedidos del mes se ajustan adecuadamente a las columnas del archivo de pedidos.

         El archivo histórico de pedidos es un archivo en formato del programa dBASE (dbf) que creó nuestro compañero Pepito del departamento de Facturación. Vamos a abrirlo desde Excel para manipularlo.

1.     
Abre el archivo Pedidos.dbf. Deberás elegir el tipo de archivo dbf:

2.     
Observa las cabeceras de las columnas del archivo histórico; son diferentes. Puedes organizarte las dos ventanas para compararlas. Observa que el orden de las columnas Categoría y Precio no coincide una hoja con otra. Además las etiquetas de Unidades y Bruto son diferentes.

3.      Crea una nueva macro llamada: FijarColumnas.

4.      Selecciona con un click la cabecera de la columna E del libro Pedidos.xls y elige Edición - Cortar.

5.      Pulsa una vez sobre la cabecera de la columna D para seleccionarla y elige Insertar - Cortar celdas

6.      Ve a la celda F1 (contiene la palabra Cantidad), escribe en su lugar: Neto y pulsas Intro.

7.      Finaliza la grabación.

8.      Comprueba el funcionamiento de la macro. Quizá debas hacer una copia de la hoja anterior.

         La última hoja con la macro ejecutada, posee un diseño de columnas igual que el archivo histórico. Vamos a añadir la hoja a partir de la primera línea en blanco de la parte inferior del archivo.

1.      Activa el libro Pedidos.dbf

2.      Ve a la celda A1 y pulsa Ctrl + *

3.      Comprueba el nombre del rango en la casilla de nombres:

4.      Crea una nueva macro llamada AmpliarBaseDatos.

5.      Pulsa Ctrl + Inicio.

6.      Sitúate en la primera celda en blanco del rango pulsando las teclas Fin, Flecha abajo y de nuevo la Flecha abajo.

7.      Pulsa Ctrl + Tabulador para volver a la hoja Pedidos.xls.

8.      Selecciona la celda A2.

9.      Dejando pulsada la tecla Shift, pulsa las teclas: Fin, Flecha abajo, Fin, Flecha derecha.

10.  Pulsa Ctrl + C para copiar las celdas al portapapeles.

11.  Pulsa Ctrl + Shift + Tab para volver al libro Pedidos.dbf.

12.  Pulsa Ctrl + V para pegar el contenido del portapapeles.

13.  Pulsa Esc para cancelar el estado de copia.

14.  Pulsa Ctrl + * para seleccionar todo el rango de datos.

15.  Accede a Insertar - Nombre - Definir para volver a definir el nombre del rango nuevo.

16.  Escribe Base_de_datos

NOTA fíjate que no hemos elegido el mismo nombre que tenía antes pulsando sobre el nombre que aparece en la ventana, sino que hemos definido un nuevo nombre para el rango. Si hubiéramos elegido el mismo nombre que tenía, Excel guardaría la antigua definición.

17.  Accede a Cerrar del menú Archivo.

NOTA en un caso real, ahora podríamos elegir la orden de Guardar, pero en este caso, al ser una macro de prueba, no grabaremos ningún cambio.

18.  Pulsa en No para cancelar el guardado.

19.   Finaliza la grabación de la macro.

         Llega el momento de la verdad. Vamos a crear una macro que ejecute una a una las demás macros que hemos preparado. Si te has asegurado de que cada macro por separado funciona, no debe haber ningún problema.

1.      Deberás dejar sólo el libro Pedidos.xls a la vista.

2.      Deja también una copia de la hoja Pedidos para probar las macros.

3.      Crea una nueva macro llamada: HacerTodo.

Pasos de la macro:

1.      Accede a Herramientas - Macros - Macro

2.      Elige de la lista de macros RellenarEtiquetas y acepta.

3.      Haz exactamente lo mismo para las demás macros en este orden:

AñadirFecha (cuando te pida la fecha, introduce: 05-11)

AñadirColumnas

FijarColumnas

AmpliarBaseDatos

4.      Finaliza la grabación de la macro.

Como ya hemos dicho, en un caso real, la última pregunta de si queremos guardar el libro Pedidos.dbf contestaríamos que sí.