¿Cuál es el más eficiente y más rápida forma de bucle a través de las filas en VBA (excel)?

Sé VBA en Excel no es la más rápida de las cosas – pero necesito el más eficiente (es decir, más rápida) forma un bucle a través de una amplia muestra de las filas.

Actualmente tengo:

For Each c In Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).row
    ' do stuff
Next c

El ‘hacer cosas’ incluye insertar una fila aquí y allá (lo necesito para mantener la dinámica de búsqueda de la gama.)

Las ideas (mirando 10.000 filas+)?

EDITAR
Yo ya estoy usando

Application.ScreenUpdating = False
Application.Calculation = xlManual
  • Ha transcurrido casi una década desde entonces he trabajado con VBA en Excel, así que estoy un poco nebuloso. Pero, pensé que había una forma de desactivar la actualización de la pantalla mientras se hace grande procesos como este que tiende a acelerar las cosas considerablemente. No puede ser de recordar correctamente, sin embargo.
  • 100% correcto, estoy usando la Aplicación.ScreenUpdating = False y Aplicación.Cálculo = xlManual (agregará a la pregunta)
  • Usted puede tratar de deshacerse de la gama por el seguimiento de las inserciones que hacer usted mismo, pero tiendo a pensar que no hay mucho de optimización que se puede hacer en el bucle for construcción de sí mismo. Ciertamente podría estar equivocado acerca de eso, pero usted debe de referencia de cuánto tiempo se tarda sólo para hacer el bucle con nada dentro de ella sólo para ver si usted podría ahorrar una cantidad significativa de tiempo, o si usted está buscando en el lugar equivocado.
  • Creo que quiso decir xlManual en su edición.
  • Application.EnableEvents = False también puede ser un verdadero rendimiento de refuerzo si el código no depende de la Hoja de trabajo/Libro de los eventos que se acciona.
  • se le olvidó un ) justo antes de la .row al final . Fijo For Each c In Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp)).row

InformationsquelleAutor Chris | 2011-11-18

3 Kommentare

  1. 37

    Si se acaba el bucle a través de 10k filas en la columna a, luego volcar la fila en una variante de la matriz y, a continuación, el bucle a través de eso.

    Puede, a continuación, añadir los elementos a una nueva matriz (mientras que la adición de filas cuando sea necesario) y el uso de Transpose() para colocar la matriz en su rango en un solo movimiento, o usted puede utilizar su variable de iteración para el seguimiento de la fila que está en y agregar las filas de esa manera.

    Dim i As Long
    Dim varray As Variant
    
    varray = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    
    For i = 1 To UBound(varray, 1)
        ' do stuff to varray(i, 1)
    Next

    Aquí es un ejemplo de cómo se podría agregar filas después de la evaluación de cada celda. Este ejemplo sólo se inserta una fila después de cada fila que tiene la palabra «foo» en la columna A. No es que el «+2» se agrega a la variable i durante la inserción, ya que estamos empezando en A2. Sería +1 si estábamos empezando nuestra matriz con A1.

    Sub test()
    
    Dim varray As Variant
    Dim i As Long
    
    varray = Range("A2:A10").Value
    
    'must step back or it'll be infinite loop
    For i = UBound(varray, 1) To LBound(varray, 1) Step -1
        'do your logic and evaluation here
        If varray(i, 1) = "foo" Then
           'not how to offset the i variable 
           Range("A" & i + 2).EntireRow.Insert
        End If
    Next
    
    End Sub
    • +1 bucles son necesarios a veces. Volcado de la gama en una matriz, hacer lo que usted necesita a la matriz, a continuación, volcado de nuevo. Una dinámica de segunda matriz (usar Redim) puede ayudar cuando se necesita para «agregar filas» a los datos originales.
    • Absolutamente. Soy un gran fan de la redim en una segunda matriz mismo 🙂
    • +1 en la matriz variant, y +1 en JPs comentario para manejar las nuevas filas
    • Me gusta mucho la idea de la nueva matriz, pero estoy golpeando con un obstáculo. Ejemplo De Rango(…).valor devuelve un array como un(10,10). Decir que tengo una segunda matriz, Redim a2 como variante, si quiero insertar una ‘fila’, me gustaría ReDim a2 10+1, pero ¿cómo puedo copiar un(1) a2(1) cuando ‘a’ como una fila completa – en este momento tengo un bucle a través de todos los valores a través de la a2(1, 1) = a(1, 1), a2(1, 2) = a(1, 2), etc, porque no puedo extraer sólo la fila de la matriz
    • Chris, es la razón por la que me sugirió que acaba de insertar filas utilizando la variable i (si se inicia desde la A2 y necesita agregar una fila, usted sabe que la nueva fila debe ser i +1). Agregar filas a una nueva matriz puede ser la pena, pero el comlexity es un trade-off por seguro. La idea es que por lo general la transferencia de elementos de una matriz a otra, agregar filas a medida que avanza.
  2. 19

    EDITAR Resumen y siguientes cuestiones

    El uso de un for each cell in range construcción no es en sí mismo lento. Lo es lento se repite el acceso a Excel en el bucle (ya sea de lectura o escritura de los valores de las celdas, formato, etc, insertar o eliminar filas, etc).

    Lo que es demasiado lento depende entierly en sus necesidades. Un Sub que toma unos minutos para que se ejecute podría estar bien, si sólo se utiliza rara vez, pero otra que lleva 10s podría ser demasiado lento si se ejecuta con frecuencia.

    Así, algunos consejos generales:

    1. mantenerlo simple a primera vista. Si el resultado es demasiado lento para sus necesidades, entonces de optimizar
    2. se centran en la optimización del contenido del bucle
    3. no acaba de asumir un bucle que se necesita. Hay algún momento de alternativas
    4. si necesita utilizar los valores de las celdas (mucho) en el interior del circuito, la carga en una variante de la matriz fuera del bucle.
    5. una buena manera de evitar la complejidad con insertos es el bucle de la gama de abajo hacia arriba

      (for index = max to min step -1)
    6. si usted no puede hacer eso y su ‘insertar una fila de aquí y de allí’ no es demasiado, considere la posibilidad de recarga de la matriz después de cada inserción
    7. Si necesita acceder a las propiedades de las células de otros que value, usted está atascado con referencias de celda
    8. Para eliminar un número de filas considerar la construcción de un rango de referencia para un multi rango de área en el bucle, a continuación, elimine ese rango de una vez después de que el bucle

    por ejemplo (no probado!)

    Dim rngToDelete as range
    for each rw in rng.rows
        if need to delete rw then
    
            if rngToDelete is nothing then
                set rngToDelete = rw
            else
                set rngToDelete = Union(rngToDelete, rw)
            end if
    
        endif
    next
    rngToDelete.EntireRow.Delete

    Post Original

    La sabiduría convencional dice que el bucle a través de las células es mal y bucle a través de una matriz variant es buena. Yo también he sido un defensor de esto por algún tiempo. Su pregunta me puso a pensar, así que hice algunas pruebas cortas con sorprendente (para mí) resultados:

    conjunto de datos de prueba: una simple lista en las células A1 .. A1000000 (eso es 1.000.000 de filas)

    Caso de prueba 1: el bucle de una matriz

    Dim v As Variant
    Dim n As Long
    
    T1 = GetTickCount
    Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
    v = r
    For n = LBound(v, 1) To UBound(v, 1)
        'i = i + 1
        'i = r.Cells(n, 1).Value 'i + 1
    Next
    Debug.Print "Array Time = " & (GetTickCount - T1) / 1000#
    Debug.Print "Array Count = " & Format(n, "#,###")

    Resultado:

    Array Time = 0.249 sec
    Array Count = 1,000,001

    Caso de prueba 2: lazo de la gama

    T1 = GetTickCount
    Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
    For Each c In r
    Next c
    Debug.Print "Range Time = " & (GetTickCount - T1) / 1000#
    Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

    Resultado:

    Range Time = 0.296 sec
    Range Count = 1,000,000

    Así,en bucle una matriz es más rápido, pero sólo el 19%, mucho menos de lo que esperaba.

    Prueba 3: bucle de una matriz con una referencia de celda

    T1 = GetTickCount
    Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
    v = r
    For n = LBound(v, 1) To UBound(v, 1)
        i = r.Cells(n, 1).Value
    Next
    Debug.Print "Array Time = " & (GetTickCount - T1) / 1000# & " sec"
    Debug.Print "Array Count = " & Format(i, "#,###")

    Resultado:

    Array Time = 5.897 sec
    Array Count = 1,000,000

    Caso de prueba 4: el circuito con una referencia de celda

    T1 = GetTickCount
    Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
    For Each c In r
        i = c.Value
    Next c
    Debug.Print "Range Time = " & (GetTickCount - T1) / 1000# & " sec"
    Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

    Resultado:

    Range Time = 2.356 sec
    Range Count = 1,000,000

    Así con una simple referencia de celda, el bucle es de un orden de magnitud más lento, y es más, en el rango de bucle es el doble de rápido!

    Así, la conclusión es lo que más importa es lo que haces dentro del bucle, y si la velocidad es realmente importante, probar todas las opciones

    Por lo que vale, probado en Excel 2010 de 32 bits, Win7 64 bits
    Todas las pruebas con

    • ScreenUpdating off,
    • Calulation manual,
    • Events movilidad.
    • Estás perdiendo la parte más importante de la prueba de bucle de la varray con varray de referencia. No hay ningún punto en la prueba 3 – ¿por qué bucle de una matriz, pero hacer una llamada a excel para cada elemento y se ignora el contenido de la matriz? Creo que usted encontrará que el traspaso de la matriz y hacer referencia a sus elementos es substancially más rápido que la reproducción en bucle de una gama y hacer referencia a las celdas (asegúrese de probar con los datos dentro de las células que se está probando (no las celdas vacías)).
    • lo siento, pero se olvida el punto: el OP dice claramente El ‘hacer cosas’ incluye insertar una fila de aquí y de allí, por lo tanto una referencia a la hoja dentro del bucle. La elección de la instrucción fue deliberadamente trivial y no ment a ser significativo. Si no hay necesidad de hacer referencia a la hoja dentro del bucle, a continuación, utilizando un zarray de referencia es la respuesta obvia y puede (y lo hizo), sin ir diciendo. Lo realmente interesante aquí es el uso de un bucle a través de las celdas es mismo no es un costoso método. Lo que importa más es lo que puede hacer dentro del bucle.
    • Simplemente la asignación de una variable tmp para igualar el valor de la celda, 1M anhela demasiado 2.234 sec para el rango y 0.3428 sec para la matriz en mis pruebas. Resultados similares para las cadenas (alrededor de un tercio de un segundo más para ambos métodos).
    • Lo siento, estoy asumiendo que si el código se inserta filas de aquí y de allí, se tiene que hacer referencia a los valores de celda para determain donde debe insertar las filas (de lo contrario no hay razón en absoluto para el bucle de la serie!). La inserción real de las filas que tendrá la misma cantidad de tiempo, no importa el método, así que no estoy considerando que.
    • +1 por CIERTO, quiero darte un +1 para bien investigada respuesta!
    • ¿qué acerca de un vacío de bucle (es decir, prueba 1, 2) en su ensayo muestran resultados diferentes a la mía? Y ¿qué versión de Excel que están utilizando
    • Sólo quería añadir que la prueba 3, lo que quise decir es que son no el traspaso de la matriz en todo lo que son simplemente hacer referencia a sus límites inferior y superior para obtener 1 y 1000000 números para configurar el bucle.
    • Yo estoy usando el 2007 y puedo mostrar los mismos resultados para vaciar los bucles. La razón es que Excel no volver atrás y mirar en el Rango indicado en la línea «para» del código, por lo tanto no hay llamadas a Excel se realizan durante el ciclo. Usted puede ver este comportamiento en un bucle en un varray y el aviso de que el intercambio de 1 y 100000 para ubound(varray, 1) y ubound(varray, 2) no tendrá ningún efecto sobre la velocidad de bucle (porque sólo se obtiene que el número de una vez y no hacer ubound llamadas cada vez).
    • Hace sence y explica por qué el tiempo para los dos métodos es similar. Pero al hacer estas pruebas una cosa que probé fue la inserción de una fila en el bucle. Provocó un bucle infinito (como el rango siguió expandiéndose) por lo que el for debe comprobación de la gama de pienso?
    • Creo que almacena el rango de temp de memoria (en una variable que no podemos ver o de acceso) que se remonta al. No hay otra forma de explicar por qué una llamada ubound un millón de veces no es más lento que la codificación duro 1000000. O eso, o el costo de llamar a excel para obtener la ‘celda siguiente’ es extreamlly rápido y bien implementado (mientras hay una llamada para obtener un valor es lento). 🙂
    • La razón de que el «rango de bucle es el doble de rápido» en comparación con el ciclismo a través de n en r.Cells(n, 1), es que el For Each construcción es intrínsecamente más rápido para marchar a través de las colecciones, en comparación a buscar los miembros de la colección a través de su índice, el cual es más lento. Por supuesto, si usted decide llamar a la última «en bucle a través de una variante», luego en «bucle a través de una variante» es malo, pero que no lo es; se trata de «bucle a través de [una colección de las células» a través de su índice, que la sabiduría convencional describe con precisión como lento.
    • Vacío de un bucle es una muy mala manera a la velocidad de la prueba en general, ya que el bucle no se que hacer nada. La hora para que los/bucle foreach es prácticamente nada cuando cada elemento en el rango de/colección/matriz no se hace referencia. En el momento de hacer, para cada uno de los bucles de DESTRUIR los bucles for, y varrays DESTRUIR las llamadas a celdas de excel 8so es así, que el hecho de que usted está utilizando en lugar de la de cada uno es irrelevent)
    • Este es uno de los más épica de las respuestas que he visto desde que he sido ASÍ. Voy a probar un poco de cada de Issun y a Chris y a ver qué eventuates. Voy a juicio hoy y marca un aceptó responder a partir de lo que funciona mejor
    • Consejo General #7 es incompleta. Cada una de las siguientes propiedades del rango de realizar en paralelo la asignación de una matriz: Value, Value2, Formula, FormulaLocal, FormulaR1C1, FormulaR1C1Local
    • VBA es como VB6 en este sentido. Es inteligente a la hora de bucles. La mayoría de estas pruebas son de sentido, y no hay nada sorprendente aquí en absoluto. El compilador está optimizado para los bucles que no hacen nada y no se ocupa de ellos, como se podría esperar. El 19% de la diferencia entre no hacer nada con la colección vs la matriz es porque la colección es un objeto, mientras que la matriz es de memoria contigua. Un hecho importante de que la izquierda es que la escritura a las células es de un orden de magnitud más lenta que la lectura de los mismos.
    • puede usted decirme qué estoy haciendo mal aquí trato de usar la lógica, pero no su trabajo para mí , aquí está mi código stackoverflow.com/questions/32686786/…
    • href=»http://stackoverflow.com/questions/32706967/how-do-i-loop-through-using-instr-value-in-vba» title=»¿cómo puedo hacer el bucle a través del uso de instr valor en vba»>stackoverflow.com/questions/32706967/…
    • por favor, puedes echar un vistazo a mi página

  3. 1

    De Cada uno es mucho más rápido que para I=1 a X, por alguna razón. Sólo trate de ir a través de el mismo diccionario,


    una vez con cada Dkey en dDict,


    y una vez con el para Dkey = lbound(dDict.teclas) to ubound(dDict.teclas)

    =>Usted notará una gran diferencia, incluso a pesar de que usted va a través de la misma construcción.

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea