Depurar rutinas con arrays y/o diccionarios?

dogwall · el 4 de agosto

Muy buenas tardes,

Normalmente mis macros son relativamente sencillas y luego, poco a poco voy puliendo detalles para hacerlas más rápidas y más cómodas. En el trabajo manejo un fichero con muchas rutinas: desde seleccionar un fichero txt y pasarlo a una hoja hasta seleccionar varios ficheros, recorrer uno a uno y realizar diferentes operaciones con ellos.

Poco a poco la cantidad de cosas y subrutinas que he ido añadiendo (por necesidades de mi trabajo) han hecho que el tiempo que tardo en ejecutar todas las macros vaya incrementándose. No es excesivo aún, ronda el minuto y medio, pero se que trabajando con arrays o creando diccionarios (un compi que está de vacaciones me mencionó algo como Scripting Dictionary) para determinadas macros puedo acelerar todo bastante. Sobre todo a la hora de realizar operaciones en determinadas hojas.

Pongo un ejemplo:

Hoja1: Precios de productos (codigo, descripcion, precio)

Hoja2: Pedidos (pedido, codigo, cantidad)

Lo que hago es mediante código cómo este que pongo (que no tiene nada que ver:

With Sheets("REPOSICIONES").Range("G4:G" & UltimaFila)
    .Formula = "=RC[-1]/DiasInforme"
    .Formula = .Value
    .NumberFormat = "0.00"
End With

Es anotar en la hoja2 el precio del producto (con un buscarv y en otra columna multiplicarlo por la cantidad).

Este proceso, recorriendo todas las filas de toda la hoja lleva un determinado tiempo que multiplicado por varias fórmulas más y varias hojas más se convierten en un minuto.

Y TODO el rollo éste que he contado es para pedir opiniones, ejemplos, etc. sobre las mejores prácticas en estos casos. ¿Se pueden almacenar esos datos en un array y recuperarlos haciendo un buscarv, una operación aritmética o similar? Apenas he utilizado arrays y me vendrían de perlas ejemplos para aprender.

Muchísimas gracias si os habéis leído este tocho

AlexanderS · el 4 de agosto

Efectivamente utilizar arrays o diccionarios acelera increíblemente la ejecución del código, el principal problema de tu código es que escribe sobre las celdas según va avanzando el bucle que utilizas, ademas de que si utilizas formulas excel debe re calcular por cada vez que escribes en una celda.

Un array o Diccionario almacena en la memoria los datos que necesitas de una manera mas rápida que escribir en cada celda estos datos, los cuales posteriormente puedes pasar a la hoja de una manera mas rápida o acceder a ellos sin necesidad de escribirlos en las celdas.

Este es un ejemplo muy simple que escribe números del 0 al 10000 celda por celda en una hoja de excel en la columna A:

Sub ejem_sin_array(): Application.ScreenUpdating = False
t = Timer
For i = 0 To 10000: Cells(i + 1, "A") = i: Next

MsgBox "Terminado en " & Format(Timer - t, "0.000") & " Segundos"

Application.ScreenUpdating = True
End Sub

Puedes ver lo simple y corto del código, toma nota de cuanto tarda en terminar.

Ahora lo mismo pero con un array, con la diferencia que no escribimos en las celdas de la columna B hasta el final del codigo:

Sub ejem_con_array(): Application.ScreenUpdating = False
Dim num()
t = Timer
ReDim num(0)

For i = 0 To 10000
    num(i) = i
    ReDim Preserve num(UBound(num) + 1)
Next
    Range("B1:B" & UBound(num)) = Application.WorksheetFunction.Transpose(num)

    MsgBox "Terminado en " & Format(Timer - t, "0.000") & " Segundos"
    
Application.ScreenUpdating = True
End Sub

Como ves en el ciclo "for" no indico que escriba sobre las celdas sino en un array que voy ampliando a medida que ingreso datos.

Viste la diferencia en el tiempo de ejecución?

Cabe destacar que la utilización de los arrays dependerá de como sea tu proyecto.

Por ultimo, si puedes realizar busquedas, filtros, sumas, entre otras operaciones en un array.

Salu2

Dr Hyde · el 4 de agosto

yo en vez de array convierte la informacion en un objeto con la orden

set area=range("a1").currentregion es un ejemplo, esto crea en la memoria una region de x por y columnnas, luego si quieres buscar algun dato solo usas

buscar=worksheetfunction.vlookup(dato,area,3,0)

o

with area.columns(x) o area.rows(y)

set busca=.find(dato)

end with

tambien puedes usar funciones coincidir(match) o indice(index), o si quieres mandar todo la informacion a una matriz de un solo paso, lo haces usando

matriz=area

esto es solo unos ejemplos realmente las ventajas son mas: colorear filas o datos, ordenar la informacion, formatear con lineas o decimales, usar funciones nativas de excel, crear graficos con los ultimos datos o poner formulas a toda una columna en 1 solo paso.

tambien te olvidas de usar los do while ....... loop (son lentisimos) y usas los for each o los for i=1 to que son mucho mas eficientes y rapidos ademas que usas mucho menos lineas para programar, y casi nada de variables.