• 👏 Bienvenido a nuestra comunidad Excel

    ¿Todavía no estás registrado? 😲

    Registrate gratis aquí y podrás:

    💪 Hacer preguntas a los expertos
    ⬇️ Descargar ejemplos y plantillas
    🏅 
    Acceder a contenidos premium

Problema con la carga de un listbox y el metodo Application.index

protostecnologia

Active member
Te cuento, mi estimado, que respecto de la medición de tiempos, habría que tener en cuenta varios detalles:
  • Es posible (de hecho me pasó en este caso) que en el mismo momento que estaba haciendo las pruebas, mi antivirus se estaba actualizando: eso distorsiona los resultados. Así como otras "distracciones" que pueden ocupar los recursos de tu máquina.
  • El primero de los códigos mostrados es IGUAL al tuyo (estudialo mejor y te vas a dar cuenta).
  • Pero el tuyo no muestra los títulos sino integrándolos al listBox: ¿Por qué?... Porque cargas los datos mediante "List" y no "RowSource" y de allí las milésimas de segundo de diferencia.
  • En ese sentido quiero que no pierdas de vista "un detalle" no menor y es que tu código termina dando formato-fecha a la columna de fecha: ¿Y si hubiese más columnas con fechas?... ¿Y si algunas columnas tuviesen números enteros y otras números con decimales?...
  • Todos estos detalles que tu código no tiene en cuenta terminarían por aumentar los tiempos de proceso y -ni hablar- aumentar la cantidad de código. Con "RowSource" todo eso ya está contemplado.
  • De hecho, tu código realiza muchísimos "ReDim" y el que mostré hace sólo uno.
  • ¿Conclusión?... Estás comparando cosas diferentes.
  • Y como te dije antes (aunque sería un cambio menor en tu código) si escribes "//19" no encuentras nada mientras que los 4 mostrados encuentran datos del año 2019.
  • Otro detalle a tener en cuenta en la medición de tiempos es la cantidad de casos: para medir tiempos yo hice -no menos- de 20 filtrados en cada propuesta, y de allí tomamos el valor promediado.
  • Si lo haces así, verás que quien se tarda más es el método basado en Recordset mientras que el basado en AdvancedFilter termina segundo.
  • ¿Y sabes qué?... Los valores promedio de tiempo terminan siendo -casi- similares y en el orden de los "milisegundos".
  • Por lo tanto -y desde mi punto de vista- son métodos equivalentes.
  • Sólo falta evaluar algo más de 400 filas con datos. Por ejemplo: 2 o 3 mil filas.
  • ¡Te dejo la inquietud!
Saludos, Cacho R.
Estimado Cacho R. Una noche mas en casa con listboxs jaja.

Puse 2800 registros (filas) y probe mi codigo ya con el tema de la perdida de titulos solucionada.

Lei que es imposible cargar la columna de titulos (columns header) sin el metodo RowSource, y la verdad que es cierto.

Por lo que opte por un artificio con labels automaticos.

Paso codigo y adjunto.


En cuanto a la eficiencia, mi codigo propuesto continuò "tirando buenos tiempos", realizando varias pruebas y obteniendo de promedio un mismo tiempo de respuesta.

Para el caso de la busqueda ingresando el numero 5, con solo 400 registros tiraba un tiempo de 21.48ms y ahora con 2800 registros este tiempo sube a 80ms; pero de todas maneras con una perfomance bastante buena teniendo en cuenta la gran cantidad de datos analizados.

eficiencia3.PNG

Codigo propuesto final:

Código:
Private Sub UserForm_Initialize()

'Parametros del Listbox
With Me.Listbox1

     .Clear
     .ColumnCount = 15

  
End With


For i = 1 To 15
Me.Controls("Label" & i).Caption = Cells(1, i)
Next i

End Sub


Private Sub fechabusqueda_Change()

Worksheets("hoja1").Activate
ultimafila = Cells(Rows.Count, "A").End(xlUp).Row

Dim iniTime!
Static Tiempos(1 To 2) As Double

iniTime = Timer

Dim x, Arr As Variant
Dim b() As Variant
Dim j As Long


'Busqueda inteligente


For fila = 2 To ultimafila

     fechaingreso = Format(Worksheets("hoja1").Range("A" & fila).Value, "d/m/yyyy")

            If fechaingreso Like "*" & Me.fechabusqueda.Value & "*" Or fila = 1 Then


            ReDim Preserve b(j)
            b(j) = fila
            j = j + 1

            End If

Next

If j = 0 Then
  MsgBox "No records"
  Exit Sub


Else

  ReDim Preserve b(j)
  b(j) = Worksheets("hoja1").Range("A" & Rows.Count).End(xlUp).Row + 1


End If


   Arr = Application.Index(Cells, Application.Transpose(b), Application.Transpose([row(1:15)])) 'Generando el Array "Arr"

  'Formatea la columna 1 con formato de fecha "d/m/yyyy"
  For x = 1 To UBound(Arr)
    Arr(x, 1) = Format(Arr(x, 1), "d/m/yyyy")
  Next

Listbox1.List = Arr

Tiempos(1) = Tiempos(1) + Timer - iniTime: Tiempos(2) = 1 + Tiempos(2)
Label32 = Format(1000 * Tiempos(1) / Tiempos(2), "0.00")


End Sub


PD: Con respecto a la busqueda con "//19", el codigo que propongo no encuentra nada, porque efectivamente no hay nada en la columna de fechas con esos caracteres. Incluso con la funcion de busqueda de excel (ctrl + find) tampoco encuentra nada. Si ingreso /20 si encuentra, porque si hay registros que comiencen con esa posibilidad. Pero si ingreso /19 mi codigo no encuentra nada, porque yo estoy formateando las fechas con el formato d/m/yyyy, y al tener ese formato, luego de la barra nunca encontraria un 19 para el caso del año, ya que todas las fechas son del año 2000 en adelante en este libro. Si hubiera alguna fecha con el año 1985 por ejemplo, ahi si encontraria la coincidencia ingresando /19 en la caja de texto. (En el adjunto, agregue una fecha con ese año, y funciona.)

Sin mas y un gusto poder compartir experiencias, mientras uno sigue aprendiendo. Gracias.
 

Adjuntos

Cacho R

Well-known member
... con "//19", el codigo que propongo no encuentra nada, porque efectivamente no hay nada en la columna de fechas con esos caracteres.
Bueno: debería encontrar todas las fechas del año 2019 (fijate que es eso lo que hacen las cuatro alternativas propuestas).

Del mismo modo, si escribes: /1 tu filtro no encuentra nada pero en las cuatro alternativas se encuentran fechas de los meses: enero, octubre, noviembre y diciembre así como con "/3" se encuentran las fechas del mes de marzo y con /12 se encuentra: diciembre.

Nuevamente vemos que "las comparaciones válidas" implican "obtener resultado iguales" y parece que no es del caso, ¿No?
____________________

Es muy interesante el resultado que comentas con 2800 registros.
Por ello, la última pruebita que habría que hacer con tu método (puesto que con el RowSource ya sabemos que se conservan los formatos de la hoja) es tener: 3 columnas con fechas y 3 columnas con decimales.
Voy a preparar esa prueba con los 5 métodos y los subo para analizarlo juntos...
 
Última edición:

protostecnologia

Active member
Me olvidé re-preguntarte: ¿Cuántas veces escribiste 5?... ¿Una vez sola?...

Lo interesante es que escribas "5" unas 10 veces para que el resultado -en tiempo- NO sea el de una sola experiencia sino -y por ejemplo- el de 10 búsquedas.
Hola buen dia Cacho R.

Hice una prueba de 5 veces aprox, y asi obtener un resultado promedio, para el caso de 2800 registros, dio un aproximado de 80ms.

Bueno: debería encontrar todas las fechas del año 2019 (fijate que es eso lo que hacen las cuatro alternativas propuestas).

Del mismo modo, si escribes: /1 tu filtro no encuentra nada pero en las cuatro alternativas se encuentran fechas de los meses: enero, octubre, noviembre y diciembre así como con "/3" se encuentran las fechas del mes de marzo y con /12 se encuentra: diciembre.

Nuevamente vemos que "las comparaciones válidas" implican "obtener resultado iguales" y parece que no es del caso, ¿No?
____________________

Es muy interesante el resultado que comentas con 2800 registros.
Por ello, la última pruebita que habría que hacer con tu método (puesto que con el RowSource ya sabemos que se conservan los formatos de la hoja) es tener: 3 columnas con fechas y 3 columnas con decimales.
Voy a preparar esa prueba con los 5 métodos y los subo para analizarlo juntos...
Ahi solucione el tema de la busqueda con mi metodo. Ya encuentra los registros cuando se ingresa "/19" o cualquier otra combinacion.

Paso codigo y adjunto. (Le agregue ademas un contador de los registros que encuentra).

Saludos y aguardo la propuesta del codigo con 3 columnas de fechas y decimales.



eficiencia4.PNG

Código:
Private Sub Label34_Click()

End Sub

Private Sub UserForm_Initialize()

'Parametros del Listbox
With Me.Listbox1

     .Clear
     .ColumnCount = 15

     
End With


For i = 1 To 15
Me.Controls("Label" & i).Caption = Cells(1, i)
Next i

End Sub


Private Sub fechabusqueda_Change()

Worksheets("hoja1").Activate
ultimafila = Cells(Rows.Count, "A").End(xlUp).Row

Dim iniTime!
Static Tiempos(1 To 2) As Double

iniTime = Timer

Dim x, Arr As Variant
Dim b() As Variant
Dim j As Long


'Busqueda inteligente


For fila = 2 To ultimafila

    fechaingreso = Format(Worksheets("hoja1").Range("A" & fila).Value, "d/m/yyyy")
    fechaingreso2 = Format(Worksheets("hoja1").Range("A" & fila).Value, "d/m/yy")

            If fechaingreso Like "*" & Me.fechabusqueda.Value & "*" Or fechaingreso2 Like "*" & Me.fechabusqueda.Value & "*" Then


            ReDim Preserve b(j)
            b(j) = fila
            j = j + 1

            End If

Next

If j = 0 Then
  MsgBox "No records"
  Exit Sub


 Else

  ReDim Preserve b(j)
  b(j) = Worksheets("hoja1").Range("A" & Rows.Count).End(xlUp).Row + 1


End If


   Arr = Application.Index(Cells, Application.Transpose(b), Application.Transpose([row(1:15)])) 'Generando el Array "Arr"
 
  'Formatea la columna 1 con formato de fecha "d/m/yyyy"
  For x = 1 To UBound(Arr)
    Arr(x, 1) = Format(Arr(x, 1), "d/m/yyyy")
  Next
  
Listbox1.List = Arr

Tiempos(1) = Tiempos(1) + Timer - iniTime: Tiempos(2) = 1 + Tiempos(2)
Label32 = Format(1000 * Tiempos(1) / Tiempos(2), "0.00")
Label34 = j 'Registros encontrados


End Sub
 

Adjuntos

Cacho R

Well-known member
Hola! mi estimado. ¡Te vas a sorprender!... (como yo lo hice). O sea:
  • En el adjunto vemos los 5 métodos: Index (carga mediante List), Index (carga mediante RowSource), filtro Avanzado, Recordset y Copiar-Pegar.
  • A todos les hago filtrar lo mismo con los mismos criterios.
  • Incorporé al comboBox una nueva opción: Genera Modelo. Básicamente sirve para indicar la cantidad de registros con los que queremos trabajar.
  • Además puse en la columna S textos a buscar (como te dije: hay que hacer correr los modelos muchas veces para obtener un tiempo promedio).
  • Y aquí viene la "sorpresa":
    • Genera no más de 50 registros y compara tiempos: ¡No lo vas a creer!
    • Genera 2800 registros y compara tiempos: ¡Lo vas a creer menos!
En resumen:
No esperaba obtener estos resultados (ni mucho menos).

Saludos, Cacho R.
 

protostecnologia

Active member
Hola! mi estimado. ¡Te vas a sorprender!... (como yo lo hice). O sea:
  • En el adjunto vemos los 5 métodos: Index (carga mediante List), Index (carga mediante RowSource), filtro Avanzado, Recordset y Copiar-Pegar.
  • A todos les hago filtrar lo mismo con los mismos criterios.
  • Incorporé al comboBox una nueva opción: Genera Modelo. Básicamente sirve para indicar la cantidad de registros con los que queremos trabajar.
  • Además puse en la columna S textos a buscar (como te dije: hay que hacer correr los modelos muchas veces para obtener un tiempo promedio).
  • Y aquí viene la "sorpresa":
    • Genera no más de 50 registros y compara tiempos: ¡No lo vas a creer!
    • Genera 2800 registros y compara tiempos: ¡Lo vas a creer menos!
En resumen:
No esperaba obtener estos resultados (ni mucho menos).

Saludos, Cacho R.
Viendo tus metodos. tenor.gif
 

protostecnologia

Active member
Hola! mi estimado. ¡Te vas a sorprender!... (como yo lo hice). O sea:
  • En el adjunto vemos los 5 métodos: Index (carga mediante List), Index (carga mediante RowSource), filtro Avanzado, Recordset y Copiar-Pegar.
  • A todos les hago filtrar lo mismo con los mismos criterios.
  • Incorporé al comboBox una nueva opción: Genera Modelo. Básicamente sirve para indicar la cantidad de registros con los que queremos trabajar.
  • Además puse en la columna S textos a buscar (como te dije: hay que hacer correr los modelos muchas veces para obtener un tiempo promedio).
  • Y aquí viene la "sorpresa":
    • Genera no más de 50 registros y compara tiempos: ¡No lo vas a creer!
    • Genera 2800 registros y compara tiempos: ¡Lo vas a creer menos!
En resumen:
No esperaba obtener estos resultados (ni mucho menos).

Saludos, Cacho R.
Estimado, me sorprendio bastante.

Resultados obtenidos por ranking de eficiencia en tiempo de ejecucion en ms.

1)Recordset
2)Index2
3)Copiarypegar
4)Indice1
5)FiltroAvanzado (The worst!!) jaja.

Es claro que el metodo Recordset les gano a todos. Quede sorprendido. Voy a ver si puedo agregar un sexto metodo (mi codigo) en tu ultimo adjunto y veo como se comporta en cuanto a eficiencia.
 

Cacho R

Well-known member
¡Ahhh!... ¡Me olvidaba!...
Lo que me sorpredió es que tu método (Index/List) es el menos eficiente con 50 registros pero el más eficiente con 2800 registros: ¡Todo al revés de lo que esperaba!
 

protostecnologia

Active member
Amigo: tu método es el primer método cambiando el Like para que los resultados sean iguales.
Inclusive está mejorado porque sólo se hace un ReDim: ¡Estúdialo bien y me comentas!
Estimado , si lo estudie y lo incorpore al mio original en tu codigo, osea ahora hay un sexto modelo para analizar, y me siguen sorprendiendo los resultados.
Si podes correlo en tu dispositivo. A mi me da como ganador en tiempo de respuesta mi metodo con Application.index.

Es claro que se podria mejorar algo mas en las coincidencias con la busqueda, pero creo que asi como esta, esta bien pulido y optimo.
Quisiera que me confirmes si en tu PC te dan los mismos resultados en cuanto a tiempo de respuesta.

Analisis y estudio de datos de los 3 mejores:

Metodo: indice2: Tiempo de respuesta: 107.5 ms
Ingreso de busqueda: "/3"
Cantidad de registros: 1000
e1.PNG

Metodo: Recordset: Tiempo de respuesta: 104.17 ms
Ingreso de busqueda: "/3"
Cantidad de registros: 1000

e2.PNG


Metodo: Application.index: Tiempo de respuesta: 82.03 ms
Ingreso de busqueda: "/3"
Cantidad de registros: 1000

e3.PNG


Eficiencia en cuanto a tiempo de respuesta:

Criterios:
Ingreso de busqueda: "/3"
Cantidad de registros: 1000

1) Application.index (82.03ms)
2) Recordset (104.17ms)
3) Indice2 (107.5ms)

Linda comparacion y analisis. Quede sorprendido nuevamente con el recordset, aunque no pudo con el Application.index jaja.
Quedo a la espera de resultados desde tu dispositivo. (Te paso el adjunto ya con mi codigo incorporado al tuyo en el combobox)
Saludos.
 

Adjuntos

Cacho R

Well-known member
¡Dios!... ¡Hay dos cosas que no te puedo hacer entender!...
  • El método que llamas Application.index lo programaste mal y con ineficiencias, y
  • Que el método al que llamo Index 1 es el mismo que el tuyo pero no omite nada y es lo más eficiente que puede.
  • Te muestro imágentes:
1596045769436.png


1596045784553.png

¿Ves que el Application.index se está "comiendo" 7 registros en esta comparación?...
Y lo mismo ocurre con "//2": debería entregarte TODOS los registros pero no lo hace.

Además seguís "haciendo trampas": sólo le ponés formato a la primera columna y no a las 4 columnas.
_________

Vuelvo a subir los 5 métodos (6 en realidad pero recuerda que el último que pusiste es la versión "pobre" del primero que vengo mostrando). Además a ese 6to. le agregué el tema del formateo completo para que veas que no te encuentras comparando lo mismo.
 

Adjuntos

Última edición:

protostecnologia

Active member
¡Dios!... ¡Hay dos cosas que no te puedo hacer entender!...
  • El método que llamas Application.index lo programaste mal y con ineficiencias, y
  • Que el método al que llamo Index 1 es el mismo que el tuyo pero no omite nada y es lo más eficiente que puede.
  • Te muestro imágentes:
Ver el archivo adjunto 59342


Ver el archivo adjunto 59343

¿Ves que el Application.index se está "comiendo" 7 registros en esta comparación?...
Y lo mismo ocurre con "//2": debería entregarte TODOS los registros pero no lo hace.

Además seguís "haciendo trampas": sólo le ponés formato a la primera columna y no a las 4 columnas.
_________

Vuelvo a subir los 5 métodos (6 en realidad pero recuerda que el último que pusiste es la versión "pobre" del primero que vengo mostrando). Además a ese 6to. le agregué el tema del formateo completo para que veas que no te encuentras comparando lo mismo.
Entiendo los detalles. En el caso del formato era porque solo me interesaba la primera columna que tenia fechas.
La doble //2 mi codigo no la encuentra obviamente porque no esta configurado para que encuentre ese tipo de combinacion, ya que no me interesaria encontrar dos barras vacias.

No entiendo porque en mi PC los tiempos dan mejor que tus modelos, y ahora cuando probe tu ultimo adjunto da peor. (Seguro debe ser por el tema que le agregaste el formato a las 4 columnas).

De todas formas me gusto todo el analisis y las comparaciones. Muy enriquecedor todo!. Un gran saludo Cacho R.

Dentro de poco te consultare dos temas que estoy por atacar y abordar, medio complicados. Gracias.
 

Cacho R

Well-known member
Por eso es que te decía antes que te fallaba el filtro al poner: 3/3
  • Días que contienen "3" (3, 13, 23, 30 y 31)
  • Mes que contiene "3" (sólo marzo)
  • Cualquier año
  • Como encontrabas menos registros tardaba menos.
  • Y lo que tarda en poner los 3 formatos que te faltaban -obviamente- aporta lo suyo.
Espero que entiendas que el primero de los procedimientos mostrados es el "tuyo" pero mejorado:
  • Mejorado el filtro
  • Un sólo ReDim (y no uno por registro hallado)
  • Y Cálculo del tiempo de proceso para 26 ejemplos (los de la columna S).
 

protostecnologia

Active member
Por eso es que te decía antes que te fallaba el filtro al poner: 3/3
  • Días que contienen "3" (3, 13, 23, 30 y 31)
  • Mes que contiene "3" (sólo marzo)
  • Cualquier año
  • Como encontrabas menos registros tardaba menos.
  • Y lo que tarda en poner los 3 formatos que te faltaban -obviamente- aporta lo suyo.
Espero que entiendas que el primero de los procedimientos mostrados es el "tuyo" pero mejorado:
  • Mejorado el filtro
  • Un sólo ReDim (y no uno por registro hallado)
  • Y Cálculo del tiempo de proceso para 26 ejemplos (los de la columna S).
Ahora termine de comprender bien todo con el tema de la busqueda de las fechas.
Lindo aporte y aprendizaje.

Gracias Cachor R.
 

Cacho R

Well-known member
Ahora termine de comprender bien todo con el tema de la busqueda de las fechas.
Lindo aporte y aprendizaje.
Está claro que los métodos 1 y 2 son los más eficientes.

¿Qué seguiría estudiar ahora?... Por ejemplo: mostrar los datos ordenados por columnas 12 y 8...
Sospecho que el Método 1 (es decir: el tuyo pero mejorado) ya deja de ser operativo y hay que utilizar el Método 2 (es decir: el tuyo mejorado pero con los datos en las celdas auxiliares) para utilizar el Sort del Excel.
 

protostecnologia

Active member
Está claro que los métodos 1 y 2 son los más eficientes.

¿Qué seguiría estudiar ahora?... Por ejemplo: mostrar los datos ordenados por columnas 12 y 8...
Sospecho que el Método 1 (es decir: el tuyo pero mejorado) ya deja de ser operativo y hay que utilizar el Método 2 (es decir: el tuyo mejorado pero con los datos en las celdas auxiliares) para utilizar el Sort del Excel.
Si, sin lugar a dudas el mejor metodo de ordenamiento en cuestion de eficiencia es el Sort, aunque no estoy seguro si solo el que este integrado al excel como funcion, sino el metodo sort universal basicamente.
 
Arriba