En esta lección continuaremos profundizando en el estudio de las macros y crearemos nuevas para nuestra hoja de Pedidos.xls.

         En tu capacidad de contable y analista de la empresa cuya hoja utilizamos en la pasada lección, te habrás sentido admirado de cómo se distribuyen en las diferentes líneas de diseño de camisetas en las diferentes áreas geográficas de América y por los diferentes canales de ventas.

         Vamos a crear una tabla dinámica que muestre las unidades de los pedidos por categorías, resaltando celdas que contengan ventas excepcionales. Más adelante crearemos otra tabla para producir gráficos.

 

 

1.      Sin nada en pantalla, abre la hoja Pedidos.dbf para abrir nuestra base de datos histórica de pedidos que realizamos en la lección anterior.

2.      Accede a Datos - Asistente para tablas dinámicas.

3.      En el paso 1, pulsa en Siguiente.

4.      En el paso 2, selecciona todo el rango de datos y pulsa en Siguiente.

5.     
En el paso 3, coloca los campos de la siguiente forma:

 

6.      Pulsa en Siguiente.

7.      En el último paso, acepta de forma que la tabla se cree en una nueva hoja.

8.      Baja el zoom al 75%

 

9.      Cámbiale el nombre a la hoja por el de: Tabla dinámica.

10.  Desde la opción Archivo - Guardar como... guarda el libro con el nombre: Categorías.xls (asegúrate de que guardas con formato XLS).

 

La tabla muestra una información global de los productos, pero vamos a ver la relación que existe entre las distintas categorías de diseño. Para ello, convertiremos la tabla para que produzca en porcentajes y así poder comparar mejor la relación existente.

 

11.  Ve a la celda A1.

12.  Pulsa sobre el botón Campo de tabla dinámica de la barra de herramientas:

 

 

Aparece el cuadro de diálogo del campo de la tabla con información sobre el campo Suma de unidades.

 

13.  Pulsa sobre el botón Opciones para expandir el cuadro de diálogo.

14.  Selecciona de la lista la opción Mostrar datos como... - % de la fila.

15. 
Selecciona la palabra Suma del nombre del cuadro y sustitúyelo por Porcentajes:

 

16.  Sal del cuadro aceptando los cambios.

 

Observa cómo los datos se han convertido a porcentajes. La columna de la derecha visualiza los porcentajes al 100%. Vamos a hacer que no se visualicen:

 

17.  Selecciona cualquier celda de la columna K.

18.  Accede a Formato - Columna - Ocultar.

 

Ahora nadie podrá ver que el total es el porcentaje 100% del total de la fila.

 

 

         Imaginemos que queremos marcar en color amarillo todas aquellas celdas cuya cantidad sea superior al número 30. Manualmente, si la hoja es muy grande, puede ser un trabajo mortal.

 

1.      Selecciona la celda D3.

2.      Abre la paleta portátil de colores y selecciona el color amarillo. (El sexto color). El fondo se convertirá en amarillo.

3.      Busca hacia abajo en la columna D para la siguiente columna con valor superior al 30%, es decir, la celda D7, y cambia su fondo a amarillo igual que la celda anterior.

 

Dar formato a una celda para que disponga de color y un aspecto especial puede ser divertido las dos o tres primeras veces. Pero cuando se repite la misma acción una y otra vez, puede ser bastante aburrido.

Vamos a crear una macro que mirará si la celda es superior a un valor. Si lo es, le dará el color amarillo de fondo.

 

1.      Crea una nueva macro y la llamas: FormatoCelda.

2.      En Opciones, asígnale la combinación Ctrl + K

3.      Coloca el fondo amarillo.

4.      Finaliza la grabación de la macro.

5.      Sitúa el cursor en cualquier celda con valor superior a 30%

6.      Pulsa Ctrl + K

Evidentemente, esto es como hacerlo manualmente, pero con una combinación de teclas que llame a una macro. Veamos cómo modificarla:

 

7.      Accede a Herramientas - Macro - Macros, selecciona la macro y pulsa en Modificar.

8.      Observa el código. Siempre hará lo mismo.

9.      Modifícalo añadiendo estas líneas:

La rutina If...Then - End If comprueba si la condición que sigue a If es cierta. Si lo es, se ejecutan las sentencias del interior. Si no lo es, no se ejecutan. Esta orden debe acabar con la sentencia End If.

 

10.  Cierra la ventana del editor y sitúa el cursor sobre alguna celda cuyo valor no pase del 30%. Ejecuta la macro pulsando Ctrl + K y observa que no aparece el color de fondo.

11.  Haz lo mismo con cualquier celda que sí pase del 30%.

La macro va tomando cuerpo, pero todavía tenemos que desplazar el cursor manualmente y mirar si el contenido de la celda es superior a la condición establecida.

Vamos a hacer que el cursor se desplace automáticamente una celda hacia abajo. Para ello, utilizaremos la orden offset(fila,columna)

 

 

12.  Agrega estas líneas:

 

 

 

 

 

 

 

 

     Con esto, conseguiríamos que el cursor se desplazase una fila hacia abajo, pero luego se pararía. Tendríamos que ir pulsando Ctrl + K constantemente. Debemos crear un bucle controlado de forma que la macro se ejecute una y otra vez hasta que nosotros lo decidamos.

Para ello, crearemos un procedimiento personalizado en el que se creará un bucle que contendrá la macro:

 

 

Ahora bien, ¿cómo sabe él cuando tiene que parar el bucle? Evidentemente no continuará hasta la fila 65.536. ¿Cuándo debe parar? Cuando encuentre la primera celda vacía. En ese momento parará.

 

 

Su equivalente en lenguaje basic sería:

 

 

El bucle Do Until...Loop (repetir hasta que se cumpla la condición) verifica que cada vuelta se vaya comprobando que la condición no se cumple. En el momento en que se cumple, es decir, en que la celda activa no contiene nada (""), se detiene el bucle.

 

13.  Modifica el código de la macro como este último ejemplo, sitúate en la celda D3 y ejecuta la macro.

 

¿A que ya va pareciendo otra cosa? No obstante continúan los inconvenientes. La macro se detiene. Tendríamos que volver a situar el cursor en la primera celda a comprobar de la segunda columna. Vamos a desplazar la celda activa para que se sitúe automáticamente en la siguiente columna.

 

Podríamos, al finalizar el bucle, añadir la siguiente línea:

 

Loop

   Range("E3").Select

End Sub

 

Y Excel situaría el cursor automáticamente en la siguiente columna. A continuación sólo quedará volver a ejecutar la macro. El problema viene cuando haya que volver a ejecutarla en la siguiente columna; el cursor volverá a la celda E3.

Vamos a añadir líneas de código que desplacen el cursor hacia arriba y lo sitúen en la siguiente celda con un valor numérico. Corresponde a las pulsaciones Flecha derecha, Flecha arriba, Fin, Flecha arriba, Flecha abajo que serían las encargadas de situar el cursor en la siguiente columna.

 

 

         De esta forma, controlamos la posición del cursor de forma que se sitúe en la primera celda numérica de la siguiente columna.

 

1.      Modifica la macro de esta forma.

2.      Prueba la macro.

3.      En la siguiente columna, vuelve a ejecutar la macro.

 

La macro debería pasar siempre de una columna a otra.

 

 

A continuación te ofrecemos una ayuda sobre distintos tipos de bucles que se pueden utilizar en programación.

 

·         Do While...Loop: Seguir en el bucle mientras o hasta una condición se cumpla

 

·         For...Next: Utilizar un contador para ejecutar las instrucciones un número determinado de veces.

        

·         For Each...Next: Repetición del grupo de instrucciones para cada uno de los objetos de una colección

 

·         While... Wend: ejecuta una serie de instrucciones mientras una condición sea verdadera.