Class module and a lot of records
 

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.

Class module and a lot of records
 

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 ==--" wrote in message
...
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.