lunes, 17 de diciembre de 2012

VBA: Optimizar el doble loop FOR...NEXT.

Es de todos conocidos que muchas veces, aunque no queramos, no nos queda más remedio que emplear la instrucción FOR...NEXT para recorrer o 'barrer' listados o rangos de nuestras hojas de cálculo; y peor aún cuando tenemos que anidar un bucle dentro de otro, aparecen nuestras peores pesadillas: los dobles bucles (doble loop). Es aquí cuando nuestros procedimientos se ralentizan hasta términos insopechados, incluso llegando al bloqueo de nuestro equipo.
Lo que se busca con estos dobles bucles es verificar alguna condición anidada. Por ejemplo, en el ejercicio que planteo, trabajamos sobre un listado de 1.000 elementos, valores repetidos entre 1 y 50. Pretendemos obtener un primer listado de esos valores repetidos (en este caso es muy simple, son valores del 1 al 50), para luego recorrer nuevamente el listado de 1.000 elementos buscando coincidencias o cualquier otra cosa.

Es lógico, por tanto, que intentemos optimizar, en la medida de lo posible estas acciones dobles. La idea es lograr limitar el segundo recorrido únicamente a las celdas de nuestro listado a estudio, limitando por tanto el tiempo destinado a ese 'barrido' de datos.
En un doble bucle sin restricciones, lo que ocurre realmente es que recorremos todos y cada uno de los elementos del listado una y otra vez, siguiendo las indicaciones.

Vamos a ver a continuación dos procedimientos similares, un primer con un doble bucle al uso, mediante el cual recorremos una y otra vez la totalidad de los elementos, tantas veces como registros únicos hallamos contado.
Mientras que en el segundo procedimiento, restringiremos el recorrido únicamente por los elementos coincidentes (que son los que nos interesan), para lo cual emplearemos la función de VBA FIND.


Insertamos el siguiente código VBA en un módulo del Explorador de proyectos VBA para el doble bucle largo:

Sub LoopTotal()
Dim celda As Object, ID As Object
Dim i As Integer
Dim mirng As String

inicio = Timer
Set unicos = New Collection
'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Sheets("Pfr").Range("IDE")
    On Error Resume Next
    unicos.Add celda.Value, CStr(celda.Value)
    On Error GoTo 0
Next celda

Application.ScreenUpdating = False
'escribir los datos en la Hoja de cálculo
For i = 1 To unicos.Count
    'con un loop general (requiere mucho tiempo...)
    For Each ID In Sheets("Pfr").Range("IDE")
        'incrementamos el contador de operaciones realizadas
        x = x + 1
        'acción de búsqueda e identificación (en este ejemplo no hace nada).
        If ID.Value = unicos(i) Then
        mirng = ID.Offset(0, 2).Address
        End If
    Next ID
Next i
Application.ScreenUpdating = True

Final = Timer
Sheets("Pfr").Range("D2").Value = Final - inicio
Sheets("Pfr").Range("D3").Value = x
End Sub



Insertamos el siguiente código VBA en un módulo del Explorador de proyectos VBA para el doble bucle restringido y más óptimo:

Sub LoopControlado()
Dim celda As Object
Dim i As Integer

inicio = Timer
Set unicos = New Collection
'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Sheets("Pfr").Range("IDE")
    On Error Resume Next
    unicos.Add celda.Value, CStr(celda.Value)
    On Error GoTo 0
Next celda

Application.ScreenUpdating = False
'escribir los datos en la Hoja de cálculo
For i = 1 To unicos.Count
    'con un loop controlado sobre un rango más reducido
    Dim iLoop As Integer
    Dim j As Integer
    
    'definimos variables, para iniciar la búsqeuda FIND
    Set rNa = Sheets("Pfr").Range("A2")
    'y contamos los elementos coincidentes para el valor buscado
    iLoop = WorksheetFunction.CountIf(Sheets("Pfr").Columns(1), CStr(unicos(i)))
    'iniciamos la búsqueda restringida únicamente a los valores coincidentes
    For j = 1 To iLoop
        'acción de búsqueda e identificación (en este ejemplo no hace nada).
        Set rNa = Sheets("Pfr").Columns(1).Find(What:=CStr(unicos(i)), After:=Sheets("Pfr").Range(rNa.Address), _
         LookIn:=xlValues, LookAt:=xlWhole, _
         SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=True)
        'incrementamos el contador de operaciones realizadas
        x = x + 1
    Next j
Next i

Application.ScreenUpdating = True
Final = Timer
Sheets("Pfr").Range("E2").Value = Final - inicio
Sheets("Pfr").Range("E3").Value = x
End Sub



La diferencia fundamental entre uno y otro radica en que el segundo Loop, en el caso óptimo recorre únicamente los valores repetidos, miestras que en el caso Total, pasa por todos los registros una y otra vez.

Vemos en la imagen el listado de 1.000 elementos repetidos (de 1 a 50), y cómo hemos asociado cada procedimiento a un Botón. En las celdas D2:E3 mediante los procedimientos reflejaremos los tiempos empleados así como el número de operaciones realizadas:

VBA: Optimizar el doble loop FOR...NEXT.


Podemos observar el ahorro de tiempo y sobre todo operaciones realizadas de uno frente a otro procedimiento... y esto es sólo para mil registros, imaginemos si trabajaramos sobre 10.000.

15 comentarios:

  1. Hola... Disculpa si el planteamiento no corresponde con esta entrada: suelo seleccionar (grabándolo en macro) los datos de una columna desde una celda, que siempre es la misma, hasta donde lleguen los datos (Ctlr + Ship + flecha hacia abajo). El problema es que en esta columna antes había fórmulas que copié como valores, de forma que a veces (con la función SI de por medio), si no se cumplía la condición, pedía que no se copiara nada (las típicas dobles comillas). El caso es que las celdas que contienen esas dobles comillas, cuando se pasan a valores, no se me queda como una celda vacía (aunque lo esté en apariencia), de forma que no puedo llevar a cabo la acción descrita al principio. En fir, perdón por el tostón. Gracias.

    ResponderEliminar
    Respuestas
    1. Hola!!
      nunca me había topado con algo así (o al menos no lo recuerdo)... si es un problema, desde luego. Le he estado danto vueltas y la única solución que se me ocurre es 'trabajar' previamente el rango.
      Con una macro, seleccionamos todo el rango (incluyendo las 'vacías'), y con algún métdo de filtro (por ejemplo) filtrar las 'vacías' (he probado y un filtro (autofilter) las detecta, seleccionamos el rango filtrado y lo borramos.
      En el siguiente paso quitamos el filtro y ya podemos aplicar el método de selección end(xlup).

      Espero te sirva la idea.
      Slds

      Eliminar
  2. ¡HA FUNCIONADO! Te lo agradezco especialmente, ya que era una consulta un poquito rara. Gracias, amigo.

    ResponderEliminar
    Respuestas
    1. Me alegro!!
      yo también lo veía complicado...
      Un cordial saludo

      Eliminar
  3. Hola buenas tardes, queria consultarte por la funcion find de vba excel.
    No se si estoy bien planteando esto aca si no es asi decime por donde puedo comunicarme con vos.
    La cosa es asi, quisiera saber si la funcion find puede buscar solo en una columna determinada o comienza buscando despues de la celda que se le indica y sigue por todas las columnas hasta el final de todas las columnas.

    ResponderEliminar
    Respuestas
    1. Hola Pablo,
      el método .Find de VBA funciona exactamente igual que cuando usas la herramienta de Búsqueda en la hoja de cálculo (Ctrl+B), así que depende de qué tengas seleccionado.
      Por ejemplo si tuvieras un rango seleccionado (B2:H13) sólo buscaría dentro de ese rango, para búsquedas generales en toda la hoja sólo marcaremos una celda...

      En tu macro funciona igual...

      Espero haber respondido tu cuestión.
      un saludo

      Eliminar
    2. Hola, como estas?, te agradezco la respuesta. entonces yo al usar find(whta:=x,after:=activecell), buscara a partir de la celda seleccionada en toda la hoja de calculo.
      en el caso de querer seleccionar solo la columna b para buscar como se podria redactar en vba.

      desde ya muchas gracias

      Saludos

      Eliminar
    3. ahhh, me olvide de preguntarte otra cosa. cuando se selecciona la busqueda con find por filas la manera de buscar es a1,a2,a3,a4 o a1,b1,c1,d1, etc.
      la misma pregunta seria para columnas.

      Eliminar
    4. Hola,
      por ejemplo, para seleccionar la columna B y buscar sólo en ella:
      Range("B:B").Find(What:=x, .....

      Respecto a la segunda pregunta, tu mismo te has contestado, hay dos opciones de búsqueda 'Por Filas' o 'Por Columnas', si eliges POr filas busca primero en cada fila, primero fila 1 , luego 2, etc... y lo mismo Por columnas, primero columna A, luego B, etc...

      Como te decía, este método .Find es la misma herramienta que la hoja de cálculo, con todas sus opciones.. igual que funcione el Ctrl+B funciona .Find.

      Slds

      Eliminar
    5. hola ismael, excelente tu ayuda me ha sido de mucha utilidad.
      Por ultimo y si no es mucha molestia quisiera saber si utilizando la funcion find se puede realizar un busqueda para un rango de fechas, es decir, me gustaria buscar registros que se encuentren entre dos fechas determinadas. por ejemplo del 01-12-13 al 01-01-14. espero que se entienda la pregunta.

      Nuevamente agradezco tu ayuda.

      Saludos

      Pablo

      Eliminar
    6. si por rango de fechas hablas de un rango de celdas A1:A10, estando limitado en A1: 1-12-13 y en A10: 1-1-14 y quieres buscar algo entre esas dos celdas, si es posible, Como te indiqué anteriormente
      Range("A1:A10").Find(What:=x, .....

      Con esto puedes limitar el rango de celdas donde buscar.
      Slds

      Eliminar
  4. Hola nuevamente ismael, pido disculpas de antemano por tantas preguntas. voy a tratar de ser bien preciso en el problema que tengo.
    con respecto a lo de buscar en la columna b lo probe y no se por que sigue buscando en toda la hoja el tema es asi:
    Tengo un negocio de electronica y actualmente tengo un programa basado en vba excel funcionando y lo que hace es que cuando se realiza una venta en una hoja se guardan los datos de la venta como fecha, n° de comprobante, costo de la venta, etc.
    ademas de eso hay un form que busca en esa hoja las ventas realizadas determinado dia por ejemplo 2-3-14 o buscar en un rango por ejemplo 1-2-14 al 1-3-14 como para ver las ventas realizadas en el mes. Como yo no se la celda en la que se encuentran las fechas a buscar creo que no me serviria la forma que me propusite.

    cambiando a lo de la columna b, yo deseo buscar en una hoja donde se encuentran los articulos a vender y la descripcion de los mismos se encuentra en la columna b. programe de la manera que me dijiste y ademas de encontrar datos en la columna b encuentra datos en otras columnas y no se por que.

    si tenes algun correo para darme podria pasarte el codigo o parte de el para darte una idea.

    Desde ya muchas gracias nuevamente.

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola ismael nuevamente, ya solucione lo de la columna b, era un error mio. me olvide de aplicar lo que me habias dicho al findnext y entonces segui buscando en otra columna.
      lo que me quedaria pendiente seria lo del rango de fechas.

      Gracias nuevamente

      Eliminar
    2. Hola Pablo,
      entiendo que el userForm realiza un tipo de filtro sobre el campo Fecha, para luego mostrarlo??
      Me preguntabas es posible hacerlo con .Find.. ??, si, si tienes un principio y un fin, puedes hacer un bucle de búsqueda desde un inicio hasta un fin,
      es decir, empieza la búsqueda de la fecha Inicial (con .Find) y vas aumentando en tu bucle y mostrando resultados, hasta que la fecha llega al límite, en cuyo momento sales del bucle...

      Slds
      P.D.: creo ha llegado el momento leas las Normas de uso del blog (pto 2 a 4)

      Eliminar
    3. Excelente Ismael, te agradezco tu ayuda fue de mucha utilidad.
      Pido disculpas si trasgredí alguna norma.
      Doy por resueltas las dudas.

      Muchas gracias

      Saludos

      Eliminar