Excel. Macros:Bucle For… Each

Aunque nuestra imagen de una macro está muy ligada a una secuencia de teclas, como hemos podido ir viendo en estos artículos , una macro es mucho más. Realmente, una macro de excel es un programa de Visual Basic, con todas sus consecuencias, lo único que, orientado a moverse en el entorno de hojas de cálculo, y dotado de herramientas especiales para hacer esa interrelación más sencilla.

Y como todo lenguaje que se precie, dispone de un conjunto de instrucciones que nos permiten alterar el normal “devenir” del programa.

La instrucción que vamos a ver, es el “For each”, y para hablar sobre ella, debemos presentar las colecciones.

Las colecciones son objetos compuestos de multitud de objetos de un mismo tipo y que pueden ser accedidos por índice, o secuencialmente; ejemplo de colecciones son las hojas de un libro de excel

Set hojas = ActiveWorkbook.Worksheets
MsgBox hojas.Count

estas instrucciones, crean un objeto hojas que contiene todas las hojas del libro activo, como una colección, es por eso, que después podemos presentar el número de hojas, por medio de la propiedad Count del objeto Collection.

Cuando debemos recorrer todos los elementos de una colección, podemos usar “For each”, los escribiríamos de esta forma

For Each sh In hojas
Debug.Print sh.Name
Next sh

Este bucle, va situando cada elemento de la colección hojas, en la variable sh; en el interior del bucle sh es un objeto worksheet, por lo que, por ejemplo, podemos presentar el nombre.

El bucle termina cuando se acaben los elementos de la colección.

Vamos a presentar un ejemplo de cómo podríamos escribir una cabecera común en todas las hojas del libro.

Vamos a escribir en la celda A1 de todas las hojas, el letrero “Hoja de pruebas de Excel” en negrita, y vamos a ajustar el ancho de la columna al del letrero, para ello, partimos del primer ejemplo, un poco modificado

Set hojas = ActiveWorkbook.Worksheets
For Each sh In hojas
    sh.Activate
    Set rg = sh.Range("A1")
    rg.Value = "Hoja de pruebas de Excel"
    rg.Font.Bold = True
    Set Rng = sh.Range("A1", sh.Range("A1").End(xlDown)).Columns(1)
    Rng.AutoFit
Next sh

Empezamos igual que antes, obteniendo la colección Worksheets y dejándola en la variable hojas

Iniciamos el bucle, recorriendo toda la colección, y para cada hoja que se va situando en sh, cargamos el rango de la casilla A1 en la variable rg, para introducir el texto, y a continuación, activar el fuente a negrita.

Para el ajuste , vamos a utilizar el método autofit, pero… Este método se ha de aplicar a una columna, no a una celda, por lo que necesitamos obtener la columna entera en donde se encuentra A1, para ello, creamos un rango que cubre desde A1 hasta el final inferior (xlDown) de la hoja, y de ese rango extraemos la columna 1, única que necesitamos. A ésta columna, que aparece en la variable Rng, podemos aplicar AutoFit y se nos ajustará la anchura de columna, al de la celda de mayor longitud de la columna.

Aunque realmente, aunque lo anterior sea lo mas correcto desde el punto de vista documental, no es en absoluto necesario realizar tantos pasos, necesitamos obtener la columna en la que se encuentra A1, y A1 es un rango, aunque sea de una celda, eso significa que puedo decir:

Set Rng = rg.Columns(1)

y obtendré la misma columna que tenía antes, por lo que ya puedo hacer AutoFit. Y si quiero ahorrarme instrucciones, (ojo con esto, por la legibilidad del código), puedo hacer:

rg.Columns(1).AutoFit

 

Acerca de Miguel Garcia

Programador, Desarrollador web, Formador en distintas areas de informatica y director de equipos multidisciplinares.
Esta entrada fue publicada en Excel, Formacion y etiquetada , , . Guarda el enlace permanente.

4 respuestas a Excel. Macros:Bucle For… Each

  1. Saludos:
    Me ha llamado la atención el efecto de las burbujas de agua subiendo a través de esta página (se ve muy original). Si es posible, me gustaría saber cómo puedo hacer efectos como ese en mi blog, aunque no sea burbujas.
    Gracias

    • Miguel dijo:

      Se trata de utilizar 2 o 3 imagenes, con distintos tamaños, y fondo trasparente, y moverlas con javascript; realmente no es muy complicado; ahora tengo una punta de trabajo y me es imposible dedicar tiempo, pero el mes que viene puedo publicar la rutina explicando paso a paso su funcionalidad

  2. Buen dia.
    Quisera saber como puedo hacer un bucle para que haga deetrminadas acciones colo en todas las hojas que tengan el nombre de CL1, CL2, CL3, etc, podrias ayudarme?
    Porque veo en todos los ejemplos que los bucles pasan por todas las hojas del libro sin ninguna condicionante, espero tu ayuda.

Deja un comentario