Hi,

I try to improve my VBA macros in the following fields :
- execution speed
- reduce the CPU usage
- reduce the memory usage

i have several sheets including around 21.000 records per sheet.
My first idea was to implement a simple cells comparison among sheets
but even if application.screenupdate is set to false, it tooks around 3
hours to perform all needed tasks.

Therefore, i worked around a collection solution, but it took let time
to perform the same tasks (around 1 hour) but it takes still a lot of
CPU and memory resources.

I tried to improve code by creating a Class module in couple with
collection, but it really increased memory usage by 200 % :-( now i need
around 350 Mb of RAM to execute it.

So i would like to know what is the best method to :
- compare very large "tables" (sheets) e.g : around 21.000 records each
the main tasks are :
- comparison between 3 sheets data
- scanning 1 sheet based on criteria from another sheet
- to limit CPU and Memory usage
- to increase execution code.

thanks a lot,

Al.

Maybe doing all in SQL might be your answer.
You can do that either in Excel sheets or maybe it is better to move the
data to a database.
If you opt for the last then have a look at SQLite.

RBS


"--== Alain ==--" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I try to improve my VBA macros in the following fields :
> - execution speed
> - reduce the CPU usage
> - reduce the memory usage
>
> i have several sheets including around 21.000 records per sheet.
> My first idea was to implement a simple cells comparison among sheets but
> even if application.screenupdate is set to false, it tooks around 3 hours
> to perform all needed tasks.
>
> Therefore, i worked around a collection solution, but it took let time to
> perform the same tasks (around 1 hour) but it takes still a lot of CPU and
> memory resources.
>
> I tried to improve code by creating a Class module in couple with
> collection, but it really increased memory usage by 200 % :-( now i need
> around 350 Mb of RAM to execute it.
>
> So i would like to know what is the best method to :
> - compare very large "tables" (sheets) e.g : around 21.000 records each
> the main tasks are :
> - comparison between 3 sheets data
> - scanning 1 sheet based on criteria from another sheet
> - to limit CPU and Memory usage
> - to increase execution code.
>
> thanks a lot,
>
> Al.